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.
Illustration 16: Select New Dimension from the Context Menus
The Welcome to the Dimension Wizard page appears, as depicted in Illustration 17.
Illustration 17: The Welcome to the Dimension Wizard Dialog
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.
Illustration 18: The Select Build Method Page with Our Selections
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.
Illustration 19: Select Data Source View Page with Selection
7. Click Next.
8. Select Standard on the Select the Dimension Type page that appears next, as shown in Illustration 20.
Illustration 20: Selecting the Standard Dimension Type ...
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.
Illustration 21: The Select the Main Dimension Table Page, with Our Selections
13. Click Next.
We arrive at the Select Dimension Attributes page.