Where We ve Been and Where We re Going

Where We've Been and Where We're Going

We begin this new section on analyzing cube content by taking a few moments to look backward before moving forward. We do this by using several Learn By Doing exercises to complete the design and population of one of the data marts, namely the Manufacturing data mart, which we began building in Chapter 6 and began populating in Chapter 7. In the section "A Slowly Changing Dimension in Action," we populate the other data mart, the Sales data mart, which we began in Chapter 6. In addition to providing a review of previous topics, this process enables us to create two OLAP cubes that we can build on throughout this chapter and the remainder of the book.

Completing the Manufacturing Data Mart

In Chapter 6, we created two data mart structures for our sample company, Maximum Miniatures, Inc. These data marts serve as repositories for business information that is not available from a single database. The Manufacturing data mart holds information coming from comma-separated value (CSV) text files created by the manufacturing automation system.

The information needed to populate the dimensional tables in this data marl comes from the AccountingSystem database. In Chapter 7, we created and executed an Integration Services package to copy information from the AccountingSystem database into most of the dimensional tables in the data mart. We are still left with a data mart that leaves a bit to be desired, because we have dimension members, but no records in our fact table. Now is the time to do something about this situation.

Before taking care of this, however, we need to deal with a change request from the vice president at Maximum Miniatures. I know this probably does not happen in your organization, but in some places, users change their minds on or come up with new ideas for the use of Business Intelligence (BI). We deal with that situation now at Maximum Miniatures.

Learn by Doing—Adding a Second Fact Table to the Manufacturing Data Mart

Features Highlighted

• Modifying the structure of a data mart database

• Multiple fact tables in a single data mart

Business Need The vice president of production at Maximum Miniatures, Incorporated would like to be able to view the current inventory amount and the number of backorders pending for products while analyzing manufacturing production. The inventory and backorder amounts are available in the order processing system.

The inventory and backorder information is added as a second fact table in the data mart. This is done because the inventory information does not relate to all of the same dimensions as the production information. In addition, because the inventory information is populated from a different data source, loading the data mart is easier with two separate fact tables.

Steps

1. Open the SQL Server Management Studio.

2. Connect to the SQL Server where you created the MaxMinManufacturingDM.

3. Expand the Databases folder and expand the MaxMinManufacturingDM database folder. Right-click the Tables folder and select New Table from the Context menu. A Table Designer tab appears.

4. If the Properties window is not visible, select Properties Window from the View menu.

5. In the Properties window, enter InventoryFact for Name and Inventory Fact populated from the Order Processing System database for Description.

6. Add the following columns to the table:

   Column Name	Data Type	Allow Nulls
   InventoryLevel	int	No
   NumberOnBackorder	int	No
   DateOflnventory	datetime	No
   ProductCode	int	No
   Material	Varchar(30)	No

7. Select the DateOflnventory, Product Code, and Material entries in the Table Designer. Click the Set Primary Key button in the toolbar.

8. Click the Relationships button in the toolbar. The Foreign Key Relationships dialog box appears.

9. Click Add.

10. Click the Tables and Columns Specifications entry, and then click the ellipsis (…) button. This displays the Tables and Columns dialog box.

11. Select DimProduct for Primary Key Table.

12. Click the first row below Primary Key Table and select ProductCode from this drop-down list.

13. Select <None> from the drop-down list in the first and third rows below Foreign Key Table. Make sure ProductCode is selected in the drop-down list in the second row.

14. Click OK to exit the Tables and Columns dialog box. Click Add.

15. Again, click the ellipsis (…) button next to the Tables and Columns Specifications to display the Tables and Columns dialog box.

16. Select DimMaterial for Primary Key Table.

17. Click the first row below Primary Key Table and select Material from this drop-down list.

18. Select <None> from the drop-down list in the first and second rows below Foreign Key Table. Make sure Material is selected in the drop-down list in the third row.

19. Click OK to exit the Tables and Columns dialog box. Click Close to exit the Foreign Key Relationships dialog box.

20. Click the Save toolbar button to save this table design. Click Yes in the dialog box listing the tables to be affected. The InventoryFact table should appear as shown in Figure 8-1.

21. Close the SQL Server Management Studio.

Figure 8-1: The InventoryFact table

Learn by Doing—Populating the Fact Tables in the Manufacturing Data Mart

Feature Highlighted

• Populating a fact table with Integration Services

Business Need Obviously, these data marts don't do Maximum Miniatures any good unless they contain facts! Therefore, we need to populate the fact tables. The information for the Manufacturing fact table is in the BatchInfo.CSV file. In an actual production environment, this file would probably have the production results for one day or perhaps one week at the most. The Integration Services package that processes this file would be run daily or weekly. However, because we want to have a good deal of sample data to work from, our copy of Batchlnfo.CSV has three years' worth of data. The design of the Integration Services package used to import this information remains unchanged.

We also have the little issue of one dimension table that has not been populated. This is the DimBatch table. The only source we have for the batch number information is the Batchlnfo.CSV file. Therefore, we include a data flow in our Integration Services package to populate the dimension table right before the fact table is populated.

We now have a second fact table in this data mart as well, the Inventory fact table. The information for this fact table comes from the order processing database. Our Integration Services package also includes a data flow to handle this.

Note 

To complete this Learn By Doing activity, you need the Batchlnfo.csv file and the Maximum Miniatures Order Processing Database. If you have not done so already, go to http://www.osborne.com, locate the book's page using the ISBN 0072260904, and follow the instructions to download the supporting files and to install the sample databases.

Steps

1. Open the Business Intelligence Development Studio.

2. Click the New Project button in the toolbar.

3. Make sure Business Intelligence Projects is selected from the Project Types, and then select Integration Services Project from the templates.

4. Enter MaxMinManufacturingDMFactLoad for the Name and set the Location to the appropriate folder.

5. Click OK to create the project.

6. Once the project is open, right-click on the Package.dtsx entry in the Solution Explorer window and select Rename from the Context menu. Enter FactLoad.dtsx for the package name and press enter.

7. Click Yes when asked if you want to rename the package object as well.

8. Drag a Data Flow Task item from the toolbox and drop it on the Control Flow tab.

9. Right-click the Data Flow Task item you just created and select Rename from the Context menu. Enter Load DimBatch and press enter. Double-click the Load DimBatch item. This takes you to the Data Flow tab.

10. Right-click in the Connections tray and select New Flat File Connection from the Context menu. The Flat File Connection Manager Editor dialog box appears.

11. Enter BatchInfo.CSV File for Connection Manager Name. Click Browse. The Open dialog box appears.

12. Browse to the BatchInfo.CSV file that you downloaded from the book's web page. Select this file and click Open to exit the Open dialog box.

13. Check Column Names in the first data row.

14. Select Columns in the page selector on the left side of the dialog box. Note the content of the columns being read from the CSV text file.

15. Select Advanced in the page selector.

16. Change the data type for the BatchNumber, MachineNumber to four-byte signed integer [DT_14], ProductCode, NumberProduced, and NumberRejected columns. Change the data type for the TimeStarted and TimeStopped columns to database timestamp [DT_DBTimeStamp].

17. Click OK to exit the Flat File Connection Manager Editor dialog box.

18. Expand the Data Flow Sources section of the toolbox, if it is not already expanded, drag a Flat File Source item from the toolbox, and drop it on the Data Flow tab.

19. Double-click the Flat File Source item you just created. The Flat File Source Editor dialog box appears.

20. The BatchInfo.CSV File data connection should be selected in the Flat File Connection Manager drop-down list.

21. Select Columns in the page selector on the left side of the dialog box.

22. Uncheck all of the columns in the Available External Columns list except the BatchNumber column.

23. Click OK to exit the Flat File Source Editor dialog box.

24. Expand the Data Flow Transformations section of the toolbox, if it is not already expanded, drag a Sort item from the toolbox, and drop it on the Data Flow tab.

25. Click the Flat File Source item. Click the green data flow arrow, drag it on top of the Sort item, and drop it on this item. This connects the source to the transformation.

26. Double-click the Sort item you just created. The Sort Transformation Editor dialog box appears.

27. Check the box to the left of BatchNumber in the Available Input Columns list.

28. Check Remove Rows with Duplicate Sort Values. This gives us a distinct list of batch numbers that we can use to populate our dimension table.

29. Click OK to exit the Sort Transformation Editor dialog box.

30. Drag a Derived Column item from the toolbox and drop it on the Data Flow tab.

31. Click the Sort item. Click the green data flow arrow, drag it on top of the Derived Column item, and drop it on this item. This connects the two transformations.

32. Double-click the Derived Column item you just created. The Derived Column Transformation Editor dialog box appears.

33. Enter BatchName in the first row under Derived Column Name.

34. Enter [BatchNumber] in the first row under Expression.

35. Select string [DT_STR] in the first row under Data Type.

36. Click OK to exit the Derived Column Transformation Editor dialog box.

37. Expand the Data Flow Destinations section of the toolbox, if it is not already expanded, drag a SQL Server Destination item from the toolbox, and drop it on the Data Flow tab.

38. Click the Derived Column item. Click the green data flow arrow, drag it on top of the SQL Server Destination item, and drop it on this item. This connects the transformation to the destination.

39. Double-click the SQL Server Destination item. The SQL Destination Editor dialog box appears.

40. Click New to create an OLE DB connection manager for use with this SQL Server destination. The Configure OLE DB Connection Manager dialog box appears.

41. Select the connection to the MaxMinManufacturingDM database that you created previously. Click OK to exit the Configure OLE DB Connection Manager dialog box.

42. Select DimBatch from the Use a Table or View drop-down list.

43. Click Mappings to view the Mappings page. The columns from the data source (that are Available Input Columns to this data destination item) should be automatically mapped to the columns in the destination (the Available Destination Columns). The following mappings should be in place:

    Available Input Columns		Available Destination Columns
    BatchNumber	to	BotchNmnber
    BatchName	to	BatchName

44. Click OK to exit the SQL Destination Editor dialog box. The Data Flow tab should appear as shown in Figure 8-2.

45. Click the Control Flow tab. Drag another Data Flow Task item onto the Control Flow tab. Rename this new Data Flow Task item Load ManufacturingFact. Drag the Data Flow Task item wider, so all of the new name can be seen.

46. Click the Load DimBatch item. Drag the precedence arrow onto the Load ManufacturingFact item and drop it there. (The DimBatch table must be loaded successfully before the ManufacturingFact table can be loaded.)

47. Double-click the Load ManufacturingFact item. This takes you to the Data Flow tab with Load ManufacturingFact selected in the Data Flow Task drop-down list.

48. Drag a Flat File Source item from the toolbox and drop it on the Data Flow tab.

49. Double-click the Flat File Source item you just created. The Flat File Source Editor dialog box appears.

50. The BatchInfo.CSV File data connection should be selected in the Flat File Connection Manager drop-down list. Click OK to exit the Flat File Source Editor dialog box.

51. Drag a Derived Column item from the toolbox and drop it on the Data Flow tab.

52. Click the Flat File Source item. Click the green data flow arrow, drag it on top of the Derived Column item, and drop it on this item. This connects the source to the transformation.

53. Double-click the Derived Column item you just created. The Derived Column Transformation Editor dialog box appears.

54. Our flat file does not explicitly provide us with the number of products accepted and the elapsed time of production. Instead, we need to calculate this information from the data that is provided in the flat file. The flat file also includes a time portion on TimeStarted. This causes problems when we use this field to build our time dimension. We can solve these issues by adding three derived columns to the data flow. Enter AcceptedProducts in the first row under Derived Column Name. Enter the following in the first row under Expression:

     [NumberProduced]- [NumberRejected] 

    (You can expand the Columns folder in the upper-left corner of the dialog box, and then drag-and-drop the fields onto the expression, if you like.) Select four-byte signed integer [DT_14] from the drop-down list in the first row under Data Type.

55. Enter ElapsedTimeForManufacture in the second row under Derived Column Name. Enter the following in the second row under Expression:

     DATEDIFF("mi", [TimeStarted] , [TimeStoppedl) 

    (You can expand the Date/Time Functions folder in the upper-right corner of the dialog box, and then drag-and-drop the DATEDIFF function onto the expression. You can also drag-and-drop the fields onto the expression.) Select numeric [DT_NUMERIC] from the drop-down list in the second row under Data Type.

56. Enter DateOfManufacture in the third row under Derived Column Name. Enter the following in the third row under Expression:

     (DT_DBTIMESTAMP)SUBSTRING((DT_WSTR,25)[TimeStarted],1,10) 

    This expression converts TimeStarted into a string and selects the first ten characters of that string (the date portion, but not the time portion). This string is then converted back into a datetime, without the time portion, so time defaults to midnight. (You can expand the Type Casts and String Functions folders in the upper-right corner of the dialog box, and drag-and-drop the (DT_WSTR) type cast, SUBSTRING function, and (DT_DBTIMESTAMP) type cast onto the expression. You can also drag-and-drop the fields onto the expression.) The data type should be [DT_DBTIMESTAMPI" by default. Click OK to exit the Derived Column Transformation Editor dialog box.

57. Drag a SQL Server Destination item from the toolbox and drop it on the Data Flow tab.

58. Click the Derived Column item. Click the green data flow arrow, drag it on top of the SQL Server Destination item, and drop it on this item. This connects the transformation to the destination.

59. Double-click the SQL Server Destination item. The SQL Destination Editor dialog box appears.

60. Click New to create an OLE DB connection manager for use with this SQL Server destination. The Configure OLE DB Connection Manager dialog box appears.

61. Select the connection to the MaxMinManufacturingDM database that you created previously. Click OK to exit the Configure OLE DB Connection Manager dialog box.

62. Select ManufacturingFact from the Use a Table or View drop-down list.

63. Click Mappings to view the Mappings page. The columns from the data source (that are Available Input Columns to this data destination item) should be automatically mapped to the columns in the destination (the Available Destination Columns). The following mappings should be in place:

    Available Input Columns		Available Destination Columns
    BatchNumber	to	BatchNumber
    MachineNumber	to	MachineNumber
    ProductCode	to	ProductCode
    Accepted Products	to	Accepted Products
    ElapsedTimeForManufacture	to	ElapsedTimeForManufacture
    DateOfManufacture	to	DateOfManufacture

64. Use drag-and-drop to add the following mapping:

    Available Input Columns		Available Destination Columns
    NumberRejected	to	RejectedProducts

65. Click OK to exit the SQL Destination Editor dialog box. The Data Flow tab should appear as shown in Figure 8-3.

66. Click the Control Flow tab. Drag a third Data Flow Task item onto the Control Flow tab. Rename this new Data Flow Task item Load InventoryFact. (You do not need to set precedence between the Load InventoryFact item and the other Data Flow Task items. It does not matter when the InventoryFact table is filled relative to the operations being done on the other tables.)

67. Double-click the Load InventoryFact item. This takes you to the Data Flow tab.

68. Right-click in the Connections tray and select New OLE DB Connection from the Context menu. Click New in the Configure OLE DB Connection Manager dialog box to create a new data connection.

69. Enter the name of the server where the Maximum Miniatures Order Processing database is installed in Server Name. Select the appropriate method for accessing this server in the Log On to the Server section. Enter credentials if necessary. Select OrderProcessingSystem from the Select or Enter a Database Name drop-down list. (If the drop-down list is empty, either your server name or your logon credentials are incorrect.) Click OK to return to the Configure OLE DB Connection Manager dialog box. Click OK again to exit this dialog box.

70. Drag an OLE DB Source item from the toolbox and drop it on the Data Flow tab.

71. Double-click the OLE DB Source item you just created. The OLE DB Source Editor dialog box appears.

72. Select the OrderProcessingSystem data connection that you just created in the OLE DB Connection Manager drop-down list. Leave the Data Access Mode drop-down list set to Table or View. Select Inventory from the Name of the Table or the View drop-down list. Click OK.

73. Drag a SQL Server Destination item from the toolbox and drop it on the Data Flow tab.

74. Click the OLE DB Source item. Click the green data flow arrow, drag it on top of the SQL Server Destination item, and drop it on this item. This connects the source to the destination.

75. Double-click the SQL Server Destination item. The SQL Destination Editor dialog box appears.

76. Select the MaxMinManufacturingDM data connection in the OLE DB Connection Manager drop-down list. Select InventoryFact from the Use a Table or View drop-down list.

77. Click Mappings to view the Mappings page. Use drag-and-drop to add the following mappings:

    Available Input Columns		Available Destination Columns
    Code	to	ProductCode
    InventoryDate	to	DateOflnventory
    AmountOnHand	to	InventoryLevel
    AmountBackordered	to	NumberOnBackorder

78. Click OK to exit the SQL Destination Editor dialog box. The Data Flow tab should appear as shown in Figure 8-4.

79. Click the Control Flow tab. The Control Flow tab should appear as shown in Figure 8-5.

80. Click the Save All button on the toolbar to save the completed package.

81. Click the Start Debugging button in the toolbar to execute the completed package. When the execution is complete, click Stop Debugging or the Package Execution Completed link to return to Design mode.

82. Close the project.

Figure 8-2: The Data Flow tab for the Load DimBatch Data Flow task

Figure 8-3: The Data Flow tab for the Load ManufacturingFact Data Flow task

Figure 8-4: The Data Flow tab for the Load InventoryFact Data Flow task

Figure 8-5: The Control Flow tab for the FactLoad.dtsx package

Creating a Cube

Now that the Manufacturing data mart contains data, let's go ahead and define an OLAP cube on top of the data mart relational database. In Chapter 6, we defined one OLAP cube at the same time we created the Sales data mart relation database. This time around, we approach things from a different direction. We already have the Manufacturing data mart relational database. What we need to do is define our OLAP cube on top of that existing database.

Once the OLAP cube is defined, we use it to help us learn more about measures in the following section of this book.

Learn by Doing—Building an OLAP Cube for the Manufacturing Data Mart

Feature Highlighted

• Creating an OLAP cube on lop of an existing data mart

Business Need The vice president of production wants to perform multidimensional analysis on the information in the Manufacturing data mart. To let him do that, we need to define an OLAP cube on top of that database.

Steps

1. Open the Business Intelligence Development Studio.

2. Click the New Project button in the toolbar.

3. Make sure Business Intelligence Projects is selected from the Project Types, and then select Analysis Services Project from the templates.

4. Enter MaxMinManufacturingDM for the Name and set the Location to the appropriate folder.

5. Click OK to create the project.

6. Right-click the Data Sources folder in the Solution Explorer window and select New Data Source from the Context menu. The New Data Source Wizard appears.

7. Click Next. The Select How to Define the Connection page appears.

8. Select the data connection to the MaxMinManufacturingDM you previously created. Click Next. The Impersonation Information page appears.

   Note 

   Analysis Services needs to access the data mart relational database to populate and update the information in the OLAP cube. This is done using the information in the data connection selected in Step 8. The Analysis Services updates run as a background process. This can be an issue if the data connection specifies Windows Authentication or if the connection is to a file-based database, such as Access or FoxPro. In these situations, Analysis Services needs to know what Windows credentials to use when accessing the data source. In other words, Analysis Services needs to impersonate a Windows identity while accessing the data.

9. On the Impersonation Information page, if you are using Windows Authentication in your data connection, select Use a Specific User Name and Password, and then enter a valid Windows user name and password for database access. If you entered a specific SQL Server login in the data connection, you do not need to worry about impersonation. (If you cannot remember how the data connection was configured, select Use a Specific User Name and Password, and then enter valid Windows credentials, just to be on the safe side.) Click Next. The Completing the Wizard page appears.

10. Enter Max Min Manufacturing DM for Data Source Name, if it is not already there. Click Finish.

11. Right-click the Data Source Views folder in the Solution Explorer window and select New Data Source View from the Context menu. The Data Source View Wizard appears.

    Note 

    A data source view enables us to specify the subset of the tables and their fields from the data source that should be included in the OLAP cube. We can also define table relationships and calculated fields that do not exist in the underlying database. In this case, we are accessing a data mart that was architected with the cube in mind. Therefore, we use all of the tables and fields in the data mart database and we do not need to specify additional relationships. However, we do use the data source view to define three calculated fields that let us easily create a time dimension hierarchy from the single datetime field that exists in the fact table.

12. Click Next. The Select a Data Source page appears.

13. The Max Min Manufacturing DM data source that you just created should be selected. Click Next. The Select Tables and Views page appears.

14. Move all of the dimension and fact tables to the Included Objects list. (Remember, there are two fact tables now.) Click Next. The Completing the Wizard page appears.

15. Enter Max Min Manufacturing DM for Name, if it is not already there. Click Finish. The Data Source View Design tab appears.

    Note 

    The Data Source View Design tab shows a diagram of the data source view we just created. This looks similar to a database diagram. This is to be expected, because a data source view is simply a view into the underlying database. The data source view gives us the chance to add items that are required by or remove items that are irrelevant to the cube we are creating.

16. Find the ManufacturingFact table in the data source view diagram. Right-click the title bar of this table and select New Named Calculation from the Context menu. The Create Named Calculation dialog box appears.

17. Enter YearOfManufacture for Column Name.

18. Enter the following for Expression:

     CONVERT(char(4), YEAR(DateOfManufacture)) 

    This creates a character field containing the year corresponding to the date of manufacture.

    Note 

    The data source view is interacting with the underlying database to create the named calculation. Therefore, the expression language of that database must be used to define the expression. In this case, the underlying database is SQL Server, so the expression language is T-SQL. The expression is evaluated by sending the SELECT statement (shown on the next page) to the underlying database.

     SELECT {Table in Data Source).* ,   {Named Calculation Expression} AS {Named Calculation Name} FROM {Table in Data Source} AS {Table Name in Data Source View} 

    For the named calculation we are creating here, the SELECT statement would be:

     SELECT ManufacturingFact.* ,   CONVERT(char(4), YEAR(DateOfManufacture))   AS YearOfManufacture FROM ManufacturingFact AS ManufacturingFact 

    Any T-SQL expression that is valid in this context, including subqueries (when explicitly surrounded by parentheses), is valid as a named calculation.

19. Click OK to exit the Create Named Calculation dialog box.

20. Create a second named calculation called QuarterOfManufacture with the following expression to contain the year and quarter corresponding to the date of manufacture:

     CONVERT(char(4), YEAR(DateOfManufacture)) +   CASE    WHEN MONTH(DateOfManufacture) BETWEEN 1 AND 3 THEN 'Q1'    WHEN MONTH(DateOfManufacture) BETWEEN 4 AND 6 THEN 'Q2'    WHEN MONTH(DateOfManufacture) BETWEEN 7 AND 9 THEN 'Q3' ELSE 'Q4' END 

21. Create a third named calculation called MonthOfManufacture with the following expression to contain the year and month corresponding to the date of manufacture:

     CONVERT(char(4), YEAR(DateOfManufacture)) + RIGHT('0'+CONVERT(varchar(2), MONTH(DateOfManufacture)),2) 

22. Create YearOfInventory, QuarterOfInventory, and MonthOfInventory named calculations in the InventoryFact table. Use the same T-SQL expressions, substituting DateOfInventory for DateOfManufacture. When completed, the Data Source View Design tab should appear similar to Figure 8-6.

23. Right-click the Cubes folder in the Solution Explorer window and select New Cube from the Context menu. The New Cube Wizard appears.

24. Click Next. The Select Build Method page appears.

25. Build the Cube Using a Data Source should be selected by default along with Auto Build checked and Create Attributes and Hierarchies selected from the drop-down list. Click Next. The Select Data Source View page appears.

26. The Max Min Manufacturing DM data source view that you just created should be selected. Click Next. The Detecting Fact and Dimension Tables page appears.

27. The wizard analyzes the content of the data view to determine which tables are fact tables and which tables are dimension tables. When this is complete, click Next. The Identify Fact and Dimension Tables page appears.

    Note 

    The wizard should have identified the ManufacturingFact and InventoryFact as fact tables. (They are identified as fact tables because of their position in the foreign key relationships in the database, not because they have the word "Fact" in the table name.) The wizard should have also correctly identified all of the dimension tables. (Again, they are identified as dimension tables because of their position in the foreign key relationships, not because their tables' names begin with Dim.) We do not have a dimension table representing the time dimension. Instead, we are going to create one based on the values in the DateOfManufacture field in the ManufacturingFact table.

28. Select ManufacturingFact from the Time Dimension Table drop-down list. When you make this selection, the wizard places a check in the Dimension column for the ManufacturingFact table. This is correct. The Manufacturing Fact table is serving as the source for both a set of facts and a dimension.

29. Remove the check next to InventoryFact in the Fact column. We initially leave the measures in the InventoryFact table out of the cube and add them in at a later time.

30. Click Next. The Select Time Periods page appears.

31. We need to select the columns we are going to use to create the time hierarchy. Select YearOfManufacture under Time Table Columns in the row for Year. Select QuarterOfManufacture under Time Table Columns in the row for Quarter. Select MonthOfManufacture under Time Table Columns in the row for Month. Select DateOfManufacture under Time Table Columns in the row for Date.

32. Click Next. The Select Measures page appears.

33. The wizard should have found and checked all of the measure fields in the ManufacturingFact table. The wizard also added a field called Manufacturing Fact Count, which counts the number of records. This does not provide us with any useful information in this data mart, so you should uncheck the Manufacturing Fact Count field.

34. Click Next. The Detecting Hierarchies page appears.

35. The wizard analyzes the foreign key relationships among the dimension tables to determine any hierarchies that may be present. When this is complete, click Next. The Review New Dimensions page appears.

36. You can expand the dimensions to view the attributes and hierarchies that were identified by the wizard. The wizard should have identified everything properly. Leave all items checked. One dimension is called Manufacturing Fact. This is the time dimension, which is based on the ManufacturingFact table. Right-click the ManufacturingFact dimension entry and select Rename Dimension from the Context menu. Enter Dim Time for the name of the dimension.

    Note 

    If you expand the attributes item under our newly named Dim Time dimension, you can see the wizard created an attribute, which is the concatenation of all of the primary keys in the ManufacturingFact table. This attribute is designated the key attribute for the dimension. This is normal behavior for the wizard. To make this dimension function as we would like, the DateOfManufacture should be the key field. We cannot fix this problem here, so we will modify it after the cube is created.

37. Click Next. The Completing the Wizard page appears.

38. Enter Max Min Manufacturing DM for Cube Name, if it is not already there. Click Finish. The Cube Design tab appears. The Business Intelligence Development Studio windows should appear similar to Figure 8-7.

39. Click the Save All button in the toolbar.

Figure 8-6: The Data Source View Design tab for the Max Min Manufacturing DM data source view

Figure 8-7: The Business Intelligence Development Studio windows after the Cube Wizard completes

If you look at the Solution Explorer window after the Cube Wizard completes, you will notice that the wizard created four dimensions: Time, Batch, Machine, and Product. However, if you look at the Dimensions section of the Cube Design tab, you can see that only one of these dimensions, the Time dimension, is being used by the Max Min Manufacturing DM cube. In the section "Measure Groups and Dimensions" we relate the other dimensions to this cube.