My biografia

Data Warehousing Basics for SQL Server Analysis Services
Sponsored by FlowerFire

Speaker: Baya Dewald

Baya Dewald: Hi, my name is Baya Dewald. I am a SQL Server and Analysis Services Consultant. Today, I will tell you a little bit about data warehousing concepts, primarily focusing on Analysis Services. This presentation is intended for people who are new to data warehousing or have builtdata warehouses, but are new to Microsoft Analysis Services platform. In this presentation, I will introduce you to data warehousing components as well relational and multi-dimensional data warehousing concepts. I will also discuss the steps involved with building a data warehousing solution and I will finish up by looking at the various methods for ensuring high availability. Many companies havealready built data warehouses and many more are thinking about building them, but what exactly is involved in building a data warehousing project with a business intelligence solution? Each business intelligence solution will contain multiple complex components, including data sources, Extraction, Transformation, and Loading (ETL) routines, a relational data warehouse, a dimensional datawarehouse, and graphical user interface tools. Before you build a warehouse, you need to identify where your data resides currently. Your data could currently reside in relational databases such as SQL server, Oracle, DB2, and so forth, or it could be in spreadsheets or e-mail systems or any number of definite data sources. Once you identify where your data is, then you need to load it to a commonrepository, and since each data source could have data in different format, you may need to transform the data and give it a common shape. The process of extracting, transforming and loading is commonly abbreviated as ETL. An ETL in a SQL server environment would typically be developed using either integration services or data transformation services packages. A relational data warehouse is made up offact tables and dimension tables, whereas a dimensional data warehouse consists of dimensions, cubes, measure groups, partitions and so forth, which we will talk about in great detail on the following slides. A graphical user interface tools can be purchased from third party vendors or you can develop them on your own. The graphical user interface tools would use SQL, Structured Query Language, toquery a relational warehouse or they would use MDX, Multi-Dimensional Expressions, to query the cubes. So, let’s talk about the relational data warehousing concept, many of which also apply to dimensional data warehouses. Dimensional tables allow slicing and dicing of data or examining it from different perspectives, for example, the time dimension would tell you when the purchase was made, thecustomer dimension would tell you who made the purchase, and the employee dimension would show you who took the order. Relational data warehouses are built using either a Star schema or a Snowflake schema, and we will talk about Star and Snowflake schemas in greater detail a little bit later. Dimension tables are normally very small. They could contain a handful of records, a few 100 or a few 1000records at most. Occasionally, you have some exceptions, for example, if you want to expose invoice numbers in your dimension, then you would have to have as many rows in a dimension table as you would in a fact table and you

should also know that the fact and dimension tables typically would reside in a single database, but that is not always necessarily going to be the case. You could havedimensions on a different database, or in a different server or in a whole separate data source. So as I mentioned, the relational data warehouses consist of fact and dimension tables. The fact tables contain dimension table keys and measures. Measures are metrics used to analyze business performance. For example, sales dollars, sales units, salary and commission and so forth. Fact tables can be...