Exforsys

Analysis Services Training

  1. MSAS - Browsing the Dependency Network
  2. MSAS - Building a Relational Decision Tree Model
  3. MSAS - Introduction to Data Mining
  4. MSAS - Applying security to a Dimension
  5. Tutorial 65: MSAS - Managing Cube Roles
  6. MSAS - Understanding Database Roles
  7. MSAS - Securing User Authentication
  8. MSAS - Introducing Analysis Services Security
  9. MSAS - Writebacks
  10. MSAS - Defining and Creating Drillthrough
  11. MSAS - Defining and Creating Auctions
  12. MSAS - Creating and Maintaining Calculated Members in Virtual Cubes
  13. MSAS - Building a Virtual Cube
  14. MSAS - Understanding Virtual Cubes
  15. MSAS - Introducing Solve Order
  16. MSAS - Implementing Calculations Using MDX Part 2
  17. MSAS - Implementing Calculations Using MDX Part 1
  18. MSAS - Merging Partitions
  19. MSAS - Introduction and Managing Partitions
  20. MSAS - Troubleshooting Cube Processing
  21. MSAS - Optimizing Cube Processing
  22. MSAS - Processing Dimensions and Cubes
  23. MSAS - Introducing Dimension and Cube Processing
  24. MSAS: Optimization Tuning Part 2
  25. MSAS: Optimization Tuning Part 1
  26. MSAS: Usage-Based Optimization
  27. MSAS: Analysis Services Aggregations
  28. MSAS: The Storage Design Wizard
  29. MSAS: Analysis Server Cube Storage
  30. MSAS: Defining Cube Properties
  31. MSAS: Introduction and Working with Measures
  32. MSAS: Introduction and Working with Cubes
  33. MSAS: Virtual Dimensions
  34. MSAS: Introducing Member Properties
  35. MSAS: Creating Custom Rollups
  36. MSAS: Creating a Time Dimension
  37. MSAS: Understanding Hierarchies
  38. MSAS: Dimension Storage Modes and Levels
  39. MSAS: Working with Levels and Hierarchies
  40. MSAS: Working with Parent-Child Dimensions
  41. MSAS : Basics of Levels
  42. MSAS : Working with Standard Dimensions
  43. MSAS : Shared vs Private Dimensions
  44. Understanding Dimension Basics
  45. MSAS : Office 2000 OLAP Components
  46. MSAS : Client Architecture
  47. MSAS : Cube Storage options
  48. MSAS : Meta data Repository
  49. MSAS : Analysis services Tools for Extended Functionality
  50. MSAS : The Wizards
  51. MSAS : The Analysis Manager and Analysis Server
  52. MSAS : The Data warehousing framework of SQL Server 2000 - Part 2
  53. MSAS : The Data warehousing framework of SQL Server 2000 - Part 1
  54. MSAS : Microsoft Data Warehousing Overview
  55. MSAS : Browsing the Cube
  56. MSAS : Designing Storage and Processing the Cube
  57. MSAS : Building the Cube Part #3
  58. MSAS : Building the Cube Part #2
  59. MSAS : Building the Cube Part #1
  60. MSAS : Setting up the Database in Analysis Server
  61. MSAS : Preparing to Create the Cube
  62. MSAS : Introducing Analysis Manager Wizards
  63. Microsoft Analysis Services Installation
  64. MSAS - Applying OLAP Cubes
  65. Understanding OLAP Models
  66. Designing the Dimensional Model and Preparing the data for OLAP
  67. Design of the data warehouse: Kimball Vs Inmon
  68. Defining OLAP Solutions and Data Warehouse design
  69. Microsoft Analysis Services Training
  70. Data Warehouse database and OLTP database
  71. Introduction to Data Warehousing

Ads


Home arrow Technical Training arrow Analysis Services Training

Designing the Dimensional Model and Preparing the data for OLAP

Page 1 of 3
Author : Exforsys Inc.     Published on: 1st Mar 2005    |   Last Updated on: 13th Apr 2011
This tutorial covers Designing the Dimensional Model, Dimensional Model schemas like Star Schema, Snowflake Schema, Optimizing star schema and Design of the Relational Database, OLAP Cubes and Data mining tools, Security considerations, metadata and backup and recovery plans.

Ads

Preparing the data for OLAP

The global issues out of the way, the enterprise must begin to focus on the granular design issues. The data in the data warehouse must be prepared for the application of Online Analytical Processing (OLAP) solutions and such preparations will be driven by the business needs of the enterprise.

Designing the Dimensional Model

User requirements and data realities drive the design of the dimensional model. The grain of detail and the type of facts to be included are decided by the business needs and the type of analytics and reports the end user wishes to generate. Maintenance issues and scalability issues determine the type of model created.


Central to the dimensional model are the Dimension tables which are linked to Fact tables. Dimension tables are tables which encapsulate the attributes associated with a particular subject into a table. A dimension table may relate to a customer, a product or a geographical region. Dimension tables have three kinds of fields—a primary key field, hierarchy level field and attribute fields. Dimension tables are linked to fact tables using the primary key of the dimension table and the foreign key of the fact table. Dimension tables are relatively small tables compared to fact tables. A dimension table may be used in multiple places if the data warehouse contains multiple fact tables that link to the dimension table. Such tables are known as Conforming dimensions. Use of conforming dimensions is critical to the design of the data warehouse.

Dimension tables define a dimension. A dimension is hierarchical and the nature of the hierarchy is determined by the needs of the group requiring the dimension. For example the time dimension may contain the day, week, month, year and quarter as attributes. A two dimension hierarchy may have customer and area as a dimension.

A dimension may also contain multiple hierarchies. For instance a time dimension may contain a calendar year and a fiscal year as hierarchies. A multiple hierarchy dimension in the customer dimension table would be area dimension, customer dimension and product dimension. This will be represented as a three dimensional model of data. This figure represents a three dimensional hierarchy

As a safety measure most dimension tables provide for an omnibus attribute called “all” such as “all customers”, “all products”. This is an artificial

category used for grouping the first level category of the dimension and permits summarization of fact data to a single number for a dimension. A hierarchy may be balanced, unbalanced, ragged, or composed of parent child relationships. We will be dealing with these attributes later in the series.

Surrogate keys are keys defined and maintained in the data warehouse to uniquely identify records in the dimension. GUID(globally unique numbers) and Identity keys are sometimes used in data derived from distributed sources to identify them.

Fact Tables

A fact table contains business event details and addresses unique business problems, process and user needs. Fact tables are very large containing millions of rows and consuming hundreds of gigabytes of space. Since dimension tables contain descriptions of facts, the fact table can be reduced to columns for dimension foreign keys and numeric fact values.

Data warehouses may contain multiple fact tables. Each fact table may relate to one particular user requirement or business need. The fact tables are related to the dimension tables relating to the business function in schemas known as star or snowflake schemas. Such business specific schemas may be part of the central data warehouse or implemented in separate data marts.

Very large fact tables are partitioned physically for ease of implementation and design considerations. The partitions are usually on a single dimension-mostly time dimension as the data in the data warehouse is historical in nature. The OLAP cubes that are developed using the partitioned fact tables are also partitioned to match the partition in the fact table.

Measures are values that quantify facts and are numeric. Measures are usually additive along dimensions. For example Quantity by customer, product and time results in a meaningful value. However non-additive values also can exist along dimensions. The quantity on hand measure is a non-additive value. Calculated measures are measures that result from applying a function to one or more measures. An example of this measure is the result of a multiplying price with product quantity.

The logical model of a fact table contains a foreign key column for the primary keys of each dimension. The combination of these foreign keys defines the primary key for the fact table. The type of composite key defined will be determined by the type of partitions required, the load performance needed etc.

Ads

The fact table resolves many-to-many relationships between dimensions because the dimension tables join through the fact table.

The granularity of the fact table is determined by the fact content columns that have been identified. Granularity is the measure of the level of detail addressed by the individual entries in the fact table.



 
This tutorial is part of a Analysis Services Training tutorial series. Read it from the beginning and learn yourself.

Analysis Services Training

  1. MSAS - Browsing the Dependency Network
  2. MSAS - Building a Relational Decision Tree Model
  3. MSAS - Introduction to Data Mining
  4. MSAS - Applying security to a Dimension
  5. Tutorial 65: MSAS - Managing Cube Roles
  6. MSAS - Understanding Database Roles
  7. MSAS - Securing User Authentication
  8. MSAS - Introducing Analysis Services Security
  9. MSAS - Writebacks
  10. MSAS - Defining and Creating Drillthrough
  11. MSAS - Defining and Creating Auctions
  12. MSAS - Creating and Maintaining Calculated Members in Virtual Cubes
  13. MSAS - Building a Virtual Cube
  14. MSAS - Understanding Virtual Cubes
  15. MSAS - Introducing Solve Order
  16. MSAS - Implementing Calculations Using MDX Part 2
  17. MSAS - Implementing Calculations Using MDX Part 1
  18. MSAS - Merging Partitions
  19. MSAS - Introduction and Managing Partitions
  20. MSAS - Troubleshooting Cube Processing
  21. MSAS - Optimizing Cube Processing
  22. MSAS - Processing Dimensions and Cubes
  23. MSAS - Introducing Dimension and Cube Processing
  24. MSAS: Optimization Tuning Part 2
  25. MSAS: Optimization Tuning Part 1
  26. MSAS: Usage-Based Optimization
  27. MSAS: Analysis Services Aggregations
  28. MSAS: The Storage Design Wizard
  29. MSAS: Analysis Server Cube Storage
  30. MSAS: Defining Cube Properties
  31. MSAS: Introduction and Working with Measures
  32. MSAS: Introduction and Working with Cubes
  33. MSAS: Virtual Dimensions
  34. MSAS: Introducing Member Properties
  35. MSAS: Creating Custom Rollups
  36. MSAS: Creating a Time Dimension
  37. MSAS: Understanding Hierarchies
  38. MSAS: Dimension Storage Modes and Levels
  39. MSAS: Working with Levels and Hierarchies
  40. MSAS: Working with Parent-Child Dimensions
  41. MSAS : Basics of Levels
  42. MSAS : Working with Standard Dimensions
  43. MSAS : Shared vs Private Dimensions
  44. Understanding Dimension Basics
  45. MSAS : Office 2000 OLAP Components
  46. MSAS : Client Architecture
  47. MSAS : Cube Storage options
  48. MSAS : Meta data Repository
  49. MSAS : Analysis services Tools for Extended Functionality
  50. MSAS : The Wizards
  51. MSAS : The Analysis Manager and Analysis Server
  52. MSAS : The Data warehousing framework of SQL Server 2000 - Part 2
  53. MSAS : The Data warehousing framework of SQL Server 2000 - Part 1
  54. MSAS : Microsoft Data Warehousing Overview
  55. MSAS : Browsing the Cube
  56. MSAS : Designing Storage and Processing the Cube
  57. MSAS : Building the Cube Part #3
  58. MSAS : Building the Cube Part #2
  59. MSAS : Building the Cube Part #1
  60. MSAS : Setting up the Database in Analysis Server
  61. MSAS : Preparing to Create the Cube
  62. MSAS : Introducing Analysis Manager Wizards
  63. Microsoft Analysis Services Installation
  64. MSAS - Applying OLAP Cubes
  65. Understanding OLAP Models
  66. Designing the Dimensional Model and Preparing the data for OLAP
  67. Design of the data warehouse: Kimball Vs Inmon
  68. Defining OLAP Solutions and Data Warehouse design
  69. Microsoft Analysis Services Training
  70. Data Warehouse database and OLTP database
  71. Introduction to Data Warehousing
 

Comments