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
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 ...
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
We arrive at the Select
Dimension Attributes page.
» 
Comment and Contribute
