Overview of DataWarehouse

DataWarehouse:
A data warehouse is a relational database that centrally stores a consistent view of the data from transactional systems in a denormalised format. This makes the retrieval of the data more efficient than it would be from a transactional system.
A data warehouse will consist of one or more data marts. A data mart typically represents an area of the business such as sales or finance departments. Within each data mart, there are two types of tables created—fact tables and dimension tables.

Dimension tables contain columns that hold information that represents a business entity. You can define many dimension tables. An example of a business entity that is represented as a dimension table could be customers. The columns will contain information that is specific to that entity. In the case of the customers dimension table, columns can include Firstname and Lastname. Dimension tables will also contain a primary key column that uniquely identifies the records in the dimension table. The primary key also defines a one-to-many relationship with an associated column or columns within a fact table.

The fact table contains information about historical transactions and typically represents this information as integer data. For example, you can have three dimension tables named Customers, Region and Time. Each has a primary key column that relates to the fact table. In this example, Customer 6231 in the fact table is equal to Francois Ajenstat in the dimension table. Region 4 is equal to the value of West and the Time value of 3423 is equal to the 18th of August, 1975 in the Time dimension table. The additional columns will contain measures about the transaction such as order quantity and sales amount. Using integer data to represent historical transactions allow SQL Server Analysis Services to process the data much more efficiently.
It consists of three groups of columns. The first is a primary key column to maintain the integrity of the fact table itself. The second group of columns are known as foreign keys. Each of these columns relate to a dimension table primary key to provide a relationship between the fact table and the contextual information provided by the dimension table. Imagine that the image represents a Sales data mart, and that the three smaller tables represent dimension tables called CustomerDim, TimeDim and RegionDim. The fact table, which would be the big table in the image, would contain three foreign key columns that would relate directly to each dimension table.
The fact table also contains a third group of columns known as measures. This group of columns hold business metrics such as sales units, sales amounts and cost amounts. As a result, the fact table ideally consists of business information represented by integer values.

Also note that dimension tables can be shared between different data marts. A common example of this is a Time dimension table. This could be used by different data marts to provide consistent information about time across multiple data marts. These types of dimension tables are known as conformed dimensions. In order to manage the movement of data from source systems to the data warehouse, staging tables may also be created in the data warehouse. This allows tables to act as intermediary tables for cleansing data before loading the cleansed data into the respective dimension tables.

DATA-WAREHOUSE STRUCTURES
Star Schema:
A star schema describes the layout of the fact tables and the dimension tables within a data mart. In a star schema, the fact table resides at the center of the data mart. There are dimension tables that have a direct join with the fact table.

SnowFlake Schema:
Within a snowflake schema, the fact table resides at the center of a data mart. Some dimension table have a direct relationship to the fact table. However, there are dimension tables that have a direct relationship with the fact table, but have another relationship to another table. These relationships can also extend to further tables as well.
An example of this can be related to a data mart about a products business entity. In this example, you have a one-dimension table that lists columns about the product including color, size, category and subcategory. If you have 10,000 products spread across 4 categories and 25 subcategories, it is more efficient to store the subcategory information within a separate table with one record for each subcategory and relate this to a separate category table. Rather than repeating the same information many times within a single dimension table to unnecessarily increase the size of the dimension table, it is more efficient to store this information in separate tables.

USING A DATAWAREHOUSE IN A BI SOLUTION:
SQL Server 2008 provides Business Intelligence tools that can be used to populate and utilize a data warehouse. Once the data warehouse has been created in the database engine, SSIS can be used to extract data from source systems.
1.)This data may then be loaded into a staging table where the data is then transformed, and then extracted as cleansed data from the staging table and loaded into the dimension tables within the data warehouse. Once the dimension tables are loaded, SSIS will then populate the associated fact tables with the correct records.
2.)You also have the option to extract data from source systems and load data directly into the data warehouse without the need for staging tables.
When the data warehouse has been fully populated, SSAS will then use the data warehouse as a basis for creating the cube. The dimension tables in the data warehouse acts as the axis of the cube to provide contextual information. These are known as dimensions. The data within the fact table is used as the basis for information that is intersected at each axis within the cube. Furthermore, SQL Server Analysis Services will generate preaggregated information of the data found within the fact table. These are known as measures. You can build cubes without the use of a data warehouse; however, this approach is not common.

SQL Server Reporting Services can then be used to create reports by querying data within the cube, within the data warehouse or from a wide range of data sources.

Introducing Business Intelligence in Microsoft SQL Server 2008

Course 10056:
Introducing Business Intelligence in Microsoft SQL Server 2008

BI is to deliver the targeted info to right people at right time.Companies use BI to remain competitive within their respective market.
MS uses SQL Server 2008 BI uses SQL server,integration,analysis and reporting services.
SSIS transforms the data and moves it to a central data warehouse. It is responsible for turning the raw data into meaningful information that will eventually be consumed by the user via business reports.
SQL Server Analysis Services or SSAS can provide a single point of access for the organization’s Business Intelligence information.It provides OLAP (Online Analytical Processing Services) databases and data mining capabilities. In the context of an OLAP database, SSAS will typically take the data that is stored in the data warehouse and create a multidimensional cube. The cube not only stores the data from the data warehouse, it can also preaggregate this data and store it permanently within the cube. This can substantially improve the query performance of both complex business questions and reports that require a large amount of aggregated data.
SQL Server Reporting Services or SSRS is the reporting component of the SQL Server BI stack that provides the front-end reports to business users on which they can make decisions. You can configure SSRS to query data within an SSAS cube and present the data in a range of reporting formats such as tables, charts and gauges. SSRS also has the capability to create reports from other data sources such as SQL Server databases or third-party database systems.

ETL
ETL stands for Extract, Transform and Load. Data is extracted from source systems such as OLTP databases and loaded into a destination database such as a decision support system or a data warehouse. During the movement of this data, you can optionally transform the data. Data transformations allow you to change the format or structure of the data. SSIS is an ETL tool that is provided by SQL Server 2008.
In typical ETL solutions using SSIS, staging tables may be used to break down the movement of data from source systems into the data warehouse. Staging tables acts as useful intermediary storage locations in that data can be moved out of the source systems and straight into the staging tables to reduce the time and resources that are placed upon the source system. From the staging table, you can then transform the data as it is loaded into the data warehouse without impacting the resources on the source systems.


OLAP:
OLAP refers to the multidimensional analysis of data. SSAS consumes information from a data warehouse to store data within an OLAP database that is stored in cubes. Cubes can store detailed data, however, its power is to create preaggregated data that is persistent within the cube. You can arrange the aggregated data so that it can be intersected by dimensions that provide contextual information for the aggregated data. Dimension can include contextual information about customers, employees or orders. This allows analysis to be performed far more efficiently than could be performed against the same data in a transactional system.
OLAP databases can store one or more cubes within it.


Data Mining:
Data mining provides an exciting aspect to SSAS in that it can use mathematical algorithms to analyse the data in either a cube or a relational table. This analysis can involve trend analysis, data classification or clustering and sequence analysis. Data mining allows you to explore your data and find out patterns that may have not been immediately evident.

Dashboards and Scorecards:
Key performance indicators (KPIs) are a feature of SSAS and are stored in an OLAP database but can be exposed through client tools such as SSRS, Microsoft Excel and Microsoft Office SharePoint Services. The ability to provide key business metrics through these visual indicators is a very powerful feature that enables you to build scorecards and digital dashboards through the client tools. Storing the KPIs on SSAS helps you to manage the KPIs centrally.





A BI solution should provide an accurate and consolidated insight into the activities of all areas of a business. SQL Server 2008 provides the technologies that allows you to create a cost-effective BI solution that is available to all users of the business.

Steps to Get MS BI Certification

MCITP: Business Intelligence Developer 2008:
Set of 2 exams:
1.)Exam 70-448- SQL Server 2008, Business Intelligence Development and Maintenance
2.)Exam 70-452- PRO: Designing a Business Intelligence Infrastructure Using Microsoft SQL Server 2008

Difference between MCTS and MCITP
http://www.vast-talent.com/blog/difference-between-MCTS-MCITP
MCTS certifications allow you to target specific technologies in the Microsoft set. Once you pick your speciality, you will have to show that you can successfully implement, build on, troubleshoot, and debug a particular Microsoft technology in order to get certified.
The MCITP credential lets you highlight your field of professional expertise, and take it up a notch.

You can also build on the MCTS to earn a MCITP certification.


REFERENCE MATERIAL FOR Exam 70-452:
Collection 6233: Implementing and Maintaining Business Intelligence in Microsoft SQL Server 2008: Integration Services, Reporting Services and Analysis Services.
Overview
This 24-hour collection provides you with the skills and knowledge required to implement and maintain business intelligence solutions on SQL Server 2008. You will learn about SQL Server technologies, such as Integration Services, Analysis Services, and Reporting Services. This collection should help students to prepare for Exam 70-448.
Topics covered in this collection include:
Introducing BI in SQL Server 2008
Configuring, deploying, and maintaining SSIS, SSAS, and SSRS
Implementing Packages, Data Flow and Control Flow in SSIS
Customizing Cube Functionality, creating a multidimensional solution, and implementing data mining in SSAS
Creating reports in SSRS

This course includes the following:
Course 10056: Introducing Business Intelligence in Microsoft SQL Server 2008
Course 10057: Implementing Packages and Control Flow in SQL Server Integration Services 2008
Course 10058: Implementing Data Flow in SQL Server Integration Services 2008
Course 10059: Managing Integration Services Packages in SQL Server 2008
Course 10060: Securing and Deploying SQL Server Integration Services 2008 Packages
Course 10061: Creating a Multidimensional Solution in SQL Server Analysis Services 2008
Course 10062: Customizing Cube Functionality in SQL Server Analysis Services 2008
Course 10063: Deploying, Managing and Maintaining SQL Server 2008 Analysis Services
Course 10064: Implementing Data Mining in SQL Server Analysis Services 2008
Course 10065: Creating Basic Reports in SQL Server Reporting Services 2008
Course 10066: Creating Interactive Reports in SQL Server Reporting Services 2008
Course 10067: Configuring, Deploying and Maintaining SQL Server Reporting Services 2008