One of the most important steps to take before designing a dimension in Analysis Services is to thoroughly understand the source data . View the data to become familiar with it and try to understand how the data relates to the functioning of your organization . Find the
Table of Contents
ous or missing data—don’t believe those who tell you that their data is clean . Some data may be less dirty than other data, but you will always find data issues . Discover the hierarchical relationships that exist in the data . You should learn how the data in one table may be related to data in other tables and whether the relationships are enforced by foreign keys . You will also need to determine whether any of the data needs to be transformed before it is loaded into a dimension .
You can use the Data Source View Designer to explore dimension data . The Data Source View Designer lets you use a grid, chart, pivot table, or pivot chart to view the data in a table . But first you’ll need to open the AdventureWorks BI solution .
Open the AdventureWorks BI solution
1. On the Microsoft Windows task bar, click Start, point to All Programs, expand the Microsoft SQL Server 2008 folder, and then select SQL Server Business Intelligence Development Studio .
2. On the File menu, point to Open and select Project/Solution .
3. In the Open Project dialog box, browse to the C:\Microsoft Press\Analysis Services 2008 SBS\Analysis Services 2008 SBS\Chapter 04\AdventureWorks BI folder .
4. Select the AdventureWorks BI .sln file and click Open .
Now that you have opened the AdventureWorks BI solution, you are ready to explore some of the dimension tables in the AS2008SBS data mart .
Explore dimension data
1. In Solution Explorer, expand the Data Source Views folder, right-click SSAS2008SBS .dsv, and select View Designer .
2. In the Diagram Organizer pane, select the First Dimensions diagram .
The First Dimensions data source view diagram displays the data source tables for the Product, Date, and Employee dimensions .
The Product dimension is a snowflake dimension: the DimProduct table has a foreign key relationship to the DimProductSubcategory table, which has a foreign key relation-ship to the DimProductCategory table .
DimDate is a standard dimension table, but several calculated members have been cre-ated so that the data will be transformed as it is loaded into Analysis Services .
The DimEmployee table has a foreign key relationship between the ParentEmployeeKey column and the EmployeeKey column . This self-referencing relationship indicates that Employee is a parent-child dimension .
3. In the Diagram pane, right-click the header of the DimProduct table .
4. Select Explore Data . You can see that many of the rows have a null value in the ProductSubcategoryKey column .
This example of missing data means that the subcategory of many products is un-known . You can determine how severe this problem is by creating a chart .
5. Click the Chart tab . In the column list, select ProductSubcategoryKey and clear all the other column check boxes . Press ENTER .
The chart shows that more than 200 rows in the table have a missing value for ProductSubcategoryKey . In the procedure titled “Use the Dimension Wizard to Create the Product Dimension” in the following section, you will create a Subcategory at-tribute in the Product dimension . When you browse the Subcategory atat-tribute, you will see an Unknown member . Because you have taken the time to become familiar with the data in the DimProduct table, when you see the Unknown member in the Subcategory attribute, you will be able to understand that it represents the rows in the DimProduct table where the ProductSubcategoryKey contains null values .
6. Close the Explore DimProduct Table viewer .
In the First Dimensions diagram, you can see that several of the columns in the DimDate table have been marked with a calculator icon . In Chapter 3, you learned that these are
calculated members . If someone else created the data source view and you are unfamil-iar with the calculated members, you should understand the SQL expression they con-tain and view the results of the transformation that they apply to the source data . 7. In the DimDate table, right-click the FiscalYearName calculated member and select Edit
Named Calculation .
The FiscalYearName calculated member contains the following SQL Expression .
'FY ' + CONVERT(CHAR(4), FiscalYear)
This expression appends FY to the value in the FiscalYear column .
8. Click OK . In the First Dimensions diagram, right-click the header of the DimDate table and select Explore Data .
9. In the Explore DimDate Table dialog box, scroll to the right until you can see the FiscalYear column and the FiscalYearName calculated member .
You can see that the FiscalYearName calculated member appends FY to the value in FiscalYear column . This will make it very easy for report readers to determine whether a year is a fiscal year or a calendar year .
10. Continue scrolling to the right and notice the values of the other calculated members . Many of the calculated members in this table have been designed to help you create Fiscal Date and Calendar Date hierarchies in the Date dimension .
11. Close the Explore DimDate Table viewer .
Take a moment to explore the data in the other tables . In particular, you may want to explore the Employee and EmployeeSort calculated members in the DimEmployee table . These calculated members were included to help you create an Employee attri-bute that displays employees’ first and last names, but sorts their names by last name and then by first name . The Employee calculated member combines an employee’s first and last names into a single value for display and the EmployeeSort calculated member combines the employee’s last and first name into a single value that you can use for sorting purposes .
Now that you have become familiar with the dimension data in the source database, you are ready to begin creating Analysis Services dimensions .