Mastering Enterprise BI: Extend the Data Source with Named Calculations, Pt. II - Page 4
April 10, 2006
Add an Aged Periods Dimension
Because of the many options that await us in Analysis Services dimensional structure design, specifically with the introduction of Attributes, in addition to Hierarchies, there might be numerous ways to proceed, even at this point, in creating our "aging buckets." We will take the route we developed in Mastering Enterprise BI: Create Aging "Buckets" in a Cube, the article to which we referred earlier, where we developed a solution for an Analysis Services 2000 cube. We might gain direct reporting benefits via this approach, namely the capability to drill down aging buckets to the transactional dates that make them up, but that is not to say that we could not obtain similar capabilities using another approach. The focus here is to show that, with our newly added Named Calculations, we can flexibly support "aging buckets" within the dimensional structure of the cube, based upon calculations we have created at the abstract layer of the Data Source View, which did not exist in our underlying relational data source.
To create an Aged Periods dimension, we will construct dimension Attributes using our Named Calculations, together with a dimension table column. We will then build a simple dimension Hierarchy using the Attributes that we have created. We will begin with the creation of a standard dimension, using the Dimension Wizard, by taking the following steps.
1. Right-click the Dimensions folder within Solution Explorer.
2. Select New Dimension from the context menu that appears, as shown in Illustration 16.
The Welcome to the Dimension Wizard page appears, as depicted in Illustration 17.
3. Click Next.
4. Select Build the dimension using a data source (the radio button is selected by default) on the Select Build Method page, which appears next.
This selection directs that the dimension structure will be based upon dimension tables, their columns and any relationships that exist between columns existing within an available data source view.
5. De-select the Auto build option.
The Select Build Method page appears, as shown in Illustration 18.
6. Click Next.
The Select Data Source View page appears, defaulted to the sole Data Source View within our project, Adventure Works DW, as depicted in Illustration 19.
7. Click Next.
8. Select Standard on the Select the Dimension Type page that appears next, as shown in Illustration 20.
9. Click Next.
The Select the Main Dimension Table page appears.
10. Using the dropdown selector, select dbo.DimTime, in the Main table section atop the page.
11. Click the checkbox to the immediate left of TimeKey, the top item in the Key columns list, to select it as the Key Column.
12. Using the dropdown, select FullDateAlternateKey, in the optional Column containing the member name selector at the bottom of the page.
The Select the Main Dimension Table page appears as depicted in Illustration 21.
13. Click Next.
We arrive at the Select Dimension Attributes page.