1 / 27

Building the cube – Chapter 9

Building the cube – Chapter 9. Let’s be over with it . Two. SSMS (SQL Server Management Studio) Important for any tasks deals with databases Use this to make sure the MaxMinMangufactureingDM database is in working order, follow the instructions I posted It is a Data Mart

colin
Download Presentation

Building the cube – Chapter 9

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Building the cube – Chapter 9 Let’s be over with it 

  2. Two • SSMS (SQL Server Management Studio) • Important for any tasks deals with databases • Use this to make sure the MaxMinMangufactureingDM database is in working order, follow the instructions I posted • It is a Data Mart • SSDT (SQL Server Data Tool) • Essentially Visual Studio 2010 • Creating a Multidimensional BI Semantic Model (OLAP) and DM projects use the same tool

  3. Key Steps • 1 – need to know where to find this tool, you may have to start a new project

  4. Key steps • 1 – may want to find the right directory to store your projects • 8 – you may have to create a new connection

  5. Key Steps • 8 – Impersonation, pick the first one • You can have multiple data sources for the same project

  6. Key Steps • 10 and 17, make sure not have space in the name • 18, you have to “right click” when the cursor is on the title bar of the table • 20, 22, and 23 test the expression with SSMS • 22, use datepart(quarter, dateOfManugacture) instead of the book’s approach, test it first • 23, understand what the author is try to do • 25, make sure to save your project here!!!

  7. Measures and related • Measure Group • The table where the measure comes from • The data in the table is the source for the measure • For the cube we have at step 35, we have one measure group – Manufacturing Fact • ??? Not sure the point for discussion on page 335

  8. Other factors to consider • Granularity • How detailed view do we need • Day, month, quarter, year, etc. • Professor, department, division, college, university • Calculated measures • New measures generated through calculations with existing ones • For example, the total goods produced = goods passed QA + goods failed QA • Step 24 of page 345 is another one.

  9. Measure Aggregates beside SUM • Look into AggregateFunction property, you will see a list of selections because not all aggregates are just sum • For example, Figure 9-8 inventory level is not additive along the time dimension, but additive along the product dimension

  10. Aggregate Functions

  11. Adding new measure group • True we can add new measure groups, but generally believe is to plan ahead and add all measure groups at the very beginning. • What is a measure group? • It is basically a fact table

  12. Things about dimensions • Dimension Design Tab --

  13. Page 347 • Step 7 and 8 • If you don’t get them right, you cannot got the steps in 352 done, then cannot deploy the project

  14. Types of dimensions • Fact dimensions • Dimensions come from the fact table • Parent Child dimensions • Two columns in the same table • Self reference • For example, employees and managers both come from the employee table

  15. Types of dimensions • Role playing dimensions • The same dimension can related to multiple columns multiple times • For example, a time dimension can related to a sales measure group several times, order date, shipment date, received date, payment received date

  16. Types of dimensions • Reference dimensions • It related to the measure group through another dimension • In the case below, Geography dimension is related to InternetSales through Customer, therefor is a reference dimension

  17. DM dimensions, M:N dimension, and Slowly changing dimension • The values of the dimension come from data mining algorithms • Many-to-Many dimension • Not to use • Slowly changing dimension • Type 1 • Type 2 • Type 3

  18. Slowly Changing Dimension • As the name suggest • An employee got promoted in Dec of 2012, she is not the GM, but was a vendor manager before, how to reflect that? • There are many ways to deal with this. We introduce three common approaches names Type I, II, and III. • The discussions here are based on Wikipedia

  19. Slowly Changing Dimension – type I • Before • After • Then, the “After” info is all you going to see

  20. Slowly Changing Dimension – type II • Before • After • Then, add additional info

  21. Slowly Changing Dimension – type III • Before • After • Then, add additional info

  22. Slowly Changing Dimension – type VI • Type VI • Type II

  23. SCD– another example • Per http://www.learndatamodeling.com • First price • Second price

  24. SCD– another example Type I • Use the second price to replace all the first one, actually the first will not be in the DM

  25. SCD– another example Type II • Approach I – use product ID and Year as key • Approach II, convert year to Effective DT

  26. SCD– another example Type III • Add, previous price and year

  27. The difference Between Type III and Type II • When we add more product price change, • Type II can be unlimited in handling the changes by just adding records • Type III can only handle a limit changes, let it be the first and last, the last two, or some others

More Related