5
Exercise 10
Creating a new DB source for the Cube
1. Open SQL Server Management Studio. Connect to server.
2. Rt-click Databases folder | New Database. Name it EquipmentListEg2DB. OK.
Creating the Cube
3. Start SQL Server BIDS. File | New project. Select BI Projects Type, and Analysis Services Project Template. Name the project EquipmentListEg2. Choose the Workspace folder as location. Make sure the checkbox Create directory for solution is chosen. OK.
4. On the Solution Explorer, rt-click, Cubes | New Cube. On the Welcome to the Cube Wizard, click Next.
5. On the Select Build Method window, choose Build the cube without using a data source. Make sure the checkbox “Use a cube template” is unchecked. Next.
6. In the Define New Measures window, click under Measure name column, and type ‘TotalUnits’. In Measure Group column, type ‘PurchasesGroup’; change Data type to Integer; leave Aggregation at Sum.
7. Add another Measure Name, PurchaseDollars; Measure group: PurchasesGroup; Data Type: currency; Aggregation: Sum. Click Next.
8. In the Define New Dimensions, you will see Time is already checked. Focus on the bottom grid titled, Add new dimensions. On the first row below the Name column, type in, Vendor. Add two other dimensions with names, Equipment and Warranty. Leave the SCD check boxes unchecked. Click Next.
9. In the Define Time Periods window, set First calendar day to Jan 01, 2002; Last calendar day to Dec 31, 2005. In Time periods, check Year, Quarter, and Month (leave the Date checked at default). Next.
10. On the Specify Additional Calendars, leave the four check boxes unchecked. Next.
11. In the Define Dimension Usage, you will see Time, Vendor, Equipment and Warranty. Check all the four boxes under Purchases Group. Next.
12. In the Completing the Wizard screen, name the cube as EquipmentListEg2. Finish.
You should now see the cube you created under the Cubes folder in Solution Explorer. You should also see the four dimensions Time, Vendor, Equipment and Warranty under the Dimensions folder.
In the middle part of your screen, you will see a link to generate data source view. Ignore it for the time being.
On the left side of the screen, you will see the Measures pane and below it the Dimensions pane.
13. On the Dimensions pane, click on the Attributes tab. Expand the Vendor. Click on Edit Vendor link. You see a grid in the bottom left of the screen (if you don’t see this, click on the Dimension Structure Toolbar icon that says View Attributes as Grid). Click on Vendor in the first row under Name column and change it to read VendorID. Click the second row in Name column (where it says in gray, <new attribute> and type in VendorName. Press Tab. The remaining columns fill automatically (as Regular, Regular, WChar, Same as key). Leave them at the default values.
14. Click on the EquipmentListEg2.cube[Design] tab. In the Dimensions pane, this time expand the Equipment and click on Edit Equipment link. On the Attributes grid, change the name Equipment to EquipmentID. Add a new attribute called EquipmentType.
15. Click on the EquipmentListEg2.cube[Design] tab again. Expand the Warranty dimension and click the Edit Warranty link. Change Warranty to WarrantyID and add a new attribute called WarrantyType.
Setting DefaultMeasure
16. Click on the EquipmentListEg2.cube[Design] tab again. On the Cube Structure tab, look for the icon that says Show Measures Tree and click on it. Click on the cube name EquipmentListEg2 (you will see a cube icon left of the cube name). Look at the Properties window on bottom right. Scroll until you see the property, DefaultMeasure, and click on it. Click on the blank textbox to the right, and select TotalUnits from the combo box.
Creating a new Data Source
17. Rt click Data Sources folder on the Solution Explorer | New Data Source.
18. On the Welcome window, click Next.
19. Select how to define the connection screen shows. Click New button.
20. Connection Manager window pops up. Provider should be Native OLE DB\ SQL Native Client. For Server name, choose the local server from the pull down menu. Choose your authentication method (we have been using SQL Server authentication, with User name: sa and Password: SOM485;. On the Select or enter a database name textbox, click on the pull down menu and choose EquipmentListEg2DB that we created at the very beginning of this exercise. Click Test Connection. Click OK to close the Connection Succeeded message window. Click OK again on the Connection Manager window. Now you will be back on the ‘Data Source Wizard-Select how to define the connection’ screen once again. Click Next.
21. On the Impersonation Information screen choose Use the service account. Next.
22. Completing the Wizard screen comes up. Leave the Data source name at EquipmentListEg2DB. Click Finish.
Generating Data Source View
23. The mid part of the cube design window shows a link titled, ‘Click here to generate data source view’. Click on the link.
24. Click Next on the Welcome to the Schema Generation Wizard.
25. On the Specify Target window, you will see the default name for the data source view as EquipmentListEg2, and for data source as EquipmentListEg2DB. Leave these as they are and click Next.
26. Subject area database schema option windows screen comes up. Leave everything on the screen at default and click Next.
27. On the Specify Naming Conventions, click Next.
28. On the Completing the Wizard screen, click Finish. And, wait a few seconds...
29. When the Schema generation is complete, click Close.
You will see a schema diagram as shown below.
30. Click on the Save All button. File | Close project (but, don’t close BIDS start page!).
Verifying Data Source
31. Open the SQL Server Management Studio. Expand the Databases folder. Expand the EquipmentListEg2DB.
32. Click on the Database Diagrams folder. Click Yes to create the required support objects.
33. Rt-click Database Diagrams folder | New Database Diagram. In the Add table window, select all the tables. Click Add button. Click Close. You see the Relationships diagram. Close the diagram. Save.
Midpoint Review
Based on the data analysis we wanted to perform, we identified the measures and dimensions in the business first. We then created a multi-dimensional data structure (cube) specifically suited for them. The system then automatically created the database schema of tables and relationships called the Data Source View for the cube. The system also physically created the corresponding database itself in a MSSQL database.
Our next step is to fill in the data into the tables that system created in the MSSQL database.
Entering data
34. Expand the Tables folder. Add the following data to the Vendor table.
1 HP HP
2 IBM IBM
3 CISCO CISCO
Add the following data to the Equipment table.
1 Laptop Laptop
2 MiniTower MiniTower
3 Monitor Monitor
4 Scanner Scanner
5 Server Server
6 ServerMonitor ServerMonitor
7 WorkStation WorkStation
Add the data to Warranty table.
1 Full Warranty Full Warranty
2 Parts & Labor Parts & Labor
3 Parts only Parts only
4 Non-Warranty Non-Warranty
Open the Time table and examine the data system created automatically. Understand how it works.
Data Integration Services
We saw in theory that creating a Data warehouse involves importing and integrating data from multiple sources (See Fig 5.1, Turban). SQL Server allows importing and integrating data from Excel, Access, Oracle, flat files, XML, etc. The ETL steps are performed by an Integration Services tool in SQL Server. We shall use this feature to fill data into the PurchasesGroup fact table we created when building the cube.
Our source data is in an Excel file (factPurchases.xls). It can be accessed from the class Web site.
35. Download into a folder in C: drive. The structure of the file is as follows.
Col A: PurchaseDate Col B: VendorID Col C: EquipmentID
Col D: WarrantyID Col E: TotalUnits Col F: PurchaseDollars
We will first use the Integration Services to load the Excel file data into the EquipmentListEg2DB and store it under a table called factPurchasesBkUp. We will then transfer the data from this table to the real factPurchases table that we created for the cube, matching column by column in these two tables.
Creating an Integration Package
36. On the BIDS start page, click File | New | Project. In the dialog window, select Business Intelligence Projects type, and the Integration Services Project template. Name the project, IntegrationServicesEg2 and click OK.
37. Rt-click the SSIS Packages folder and select SSIS Import and Export Wizard.
38. On the Welcome page of the wizard, click Next.
39. On the Choose a Data Source window, click on the combo box for Data Source. Select Microsoft Excel. Click Browse for Excel file path and locate the file you saved in Step 35. Leave the Excel version box at default value. Uncheck the First row has column names box. Click Next.
40. Choose a Destination window shows. Leave the Destination box at SQL Native Client and Server name at Local. Choose the appropriate authentication (see Step 20). For the Database, from combo box, choose EquipmentListEg2DB. Click Next.
41. Specify Table Copy or Query window comes up. Choose Copy data from one or more tables or views option. Next.
42. On the Select Source Tables and Views, check the first checkbox for ‘Sheet1$’. Click Preview to make sure the data is correctly displayed (do not worry about the column name changes). Next.
43. On the Complete the Wizard screen, click Finish. And, wait...
44. In a few moments, The execution was successful window shows. Click Close.
45. Your screen will now show the Package1.dtsx[Design]. You will see two rectangles connected by an arrow. Press the F5 key. The package will now execute. Both rectangles will turn yellow and then green.
46. Go back to the SQL Server Management Studio. Rt-click EquipmentListEg2DB | Refresh. Expand Tables. You will now see a table named dbo.Sheet1$. Rt-click on it | Open table. You will see the imported data from Excel. Note the columns are named F1 thru F6. Close the table.
47. Now, we need to transfer the data from the primary import table (Sheet1$) to the PurchasesGroup (this is the fact table we created when developing the cube structure). For this, click on the New Query icon/button in the toolbar. A blank query window opens. Change the source table from ‘master’ to ‘EquipmentListEg2DB’. Enter the following query.
Insert into EquipmentListEg2DB.PurchasesGroup
(FK_Time, FK_Vendor, FK_Equipment, FK_Warranty, TotalUnits, PurchaseDollars)
Select F1, F2, F3, F4, F5, F6
from dbo.Sheet1$
Click on the !Execute key. You will get the message (201 rows affected). To verify, rt-click on the PurchasesGroup | Open Table. You will see the copied data.
48. Go back to the IntegrationServicesEg2 project. Click File | Close project. Say Yes to stop debugging.
49. On the BIDS start page, click open the EquipmentListEg2 project we created earlier when we developed the cube structure. Rt-click on the project name in the Solution Explorer | Deploy. And, wait... until you see the message Deployment Completed Successfully.
50. Click the Browser tab on the cube design window. Click and drag Measures from the left column into the Drop Totals or Detail Fields Here area. Click and drag Vendor to the area Drop Row Fields Here. Click and drag Time to Drop Column Fields Here area. Click and drop Equipment to the Row area to the right of Vendor column. Perform slicing and dicing to get a feel for the analysis you can perform.