Technical: Database – Dimension Modeling – Dimension – Dimension Wizard – Time (DimTime) in SQL Server Analysis Services / BIDS

Technical: Database – Dimension Modeling – Dimension – Dimension Wizard – Time (DimTime) in SQL Server Analysis Services / BIDS

Introduction

In an earlier post we discussed the importance of the Time Dimension.  In this post we will discuss how to design the Time Dimension.

Development Environment

Here is our Development Environment:

  • Visual Studio Data Tools for Visual Studio 2012

Create SQL Server Analysis Server Project

Create a Project

Once you launch your IDE, you can choose to create a new project or open an existing one.

For the sake of completion, we will create a new Project:

The entries we will use are:

  • Name – TimeDimension
  • Location – E:\DanielAdeniji\Microsoft\SQLServer\BI\AnalysisProject\v2012
  • Create directory for solution – Uncheck 
  • Add to source control – Uncheck

NewProject

Create a new Data Source

Let us add “Data Sources” :

  • Access the “Solution Explorer”
  • Access the “Data Sources” node
  • Right-click on the “Data Sources” node,

Access “Data Sources” node:

CreateANewDataSource

Welcome to the Data Source Wizard:

WelcomeToTheDataSourceWizard

Select how to define the connection (before adding new connection):

SelectHowToDefineTheConnection (before)

Connection Manager :

  • Provider : Native OLE DB \ SQL Server Native Client 11.0
  • Server Name : MSSQLDW
  • Log on to the server : Use Windows Authentication // Use SQL Server Authentication
  • Select or enter a database :- DBLawDW

ConnectionManager-MSSQLDW

Select how to define the connection (after adding new connection):

SelectHowToDefineTheConnection (after)

Impersonation Information:

Set Impersonation Options (SSAS – Multidimensional)
http://technet.microsoft.com/en-us/library/ms187597.aspx

Here are the choices:

  • Use a specific Windows user name and password
  • Use the service account
  • Use the credentials of the current user
  • Inherit

Account Configuration

Ensure that the Account meets the following qualifications:

  • Set password to never expire
  • Allow account to “Log on Locally”
  • Locked down to specific machines

The choices breaks down as follow:

Specific Windows User name and Password

Accept the “Specific Windows User name and password” option, if you will like to create a so called Service Account.  You will create one in “Active Directory” and use that Account as a surrogate account per Analysis Services.

Use the Service Account

Choose this option if you will like to use that the “SQL Server Analysis Service” is configured to “run as”.

 

Again for the sake of simplicity and for quicker initial development, we will go with SSAS  “Service Account”

ImpersonationInformation

Completing the Wizard

Accept the default “Data Source name” unless you have reasons such as “Naming” guidelines.

CompletingTheWizard
Connection String:

Please review the “Connection String”, as well:


Provider=SQLNCLI11.1;Data Source=MSSQLDW;Integrated Security=SSPI;Initial Catalog=DBLabDW

Explanation:

Item Name Item Value Explanation
Provider SQLNCLI11.1 SQL Server Native Client 11.0 OLE DB Provider Connection Strings …
Data Source MSSQLDW SQL Server Instance Name
Integrated Security SSPI Use Integrated Security; which means we do not have to explictly state user and passwords
Initial Catalog DBLabDW Once DB Connection is made make DBLabDB your database name

Solution Explorer (After Adding Data Sources):

Once the Data Source has been added, we will see our new Data Source in the Solution Explorer.

SolutionExplorer-DataSources-AfterAddingDataSource

Add “Data Source Views”

Let us add “Data Sources Views” :

  • Access the “Solution Explorer”
  • Access the “Data Sources Views” node
  • Right-click on the “Data Sources Views” node,

Access “Data Sources Views” node:

SolutionExplorer-DataSourceViews-BeforeAddingDataSourceViews

Welcome to the Data Source Wizard:

WelcomeToTheDataSourceViewWizard

Select a Data Source:

Select the Data Source that we will like to access

SelectADataSource

Select Tables and Views:

Review the list of tables and views and move the objects that you will like exposed:

SelectTablesAndViews-Before

Once objects have been selected:

SelectTablesAndViews-After

Completing the Wizard:

Review the list of Database Objects that will be exposed.

CompletingTheWizard

Diagram Organizer:

Once we have selected our tables and we have the proper primary keys, alternate primary keys & unique constraints, and foreign key constraints in place we are good.

The “Diagram Organizer” is reflective and representative of the Data Source Logical Model.

DataSourceRelationalModel

 

Create Dimensions

Welcome to the Dimension Wizard:

WelcomeToTheDimensionWizard

Select Creation Method:

In the “Select Creation Method” window, we have the options listed below:

  • Use an existing table
  • Generate a time table in the data source
  • Generate a time table on the server
  • Generate a non-time table in the data source

SelectCreationMethod

As we have our own Time table, we will select the “Use an existing table” option.

Specify Source Information:

In our case, we select the options listed below:

Item Name Item Value Explanation
Data source view DB Lab DW
Main table DimTime Please choose your Time Dimension table
Key columns TimeKey Please choose the key column.  In our case we used an identity numeric column
Name column FullDateAlternateKey This is the date column

 

Here is the “Specify Source Information” wizard:

SpecifySourceInformation

Select Related Tables:

As we took the time to create foreign key relationships for our Dimension table (dbo.dimTime), the system automatically picks out the corresponding tables.

Select Related Tables Table Column
lookupCalenderWeekday dbo.Time DayNumberofWeek
lookupCalendarMonth dbo.Time MonthNumberofYear

Select Related Tables

Leave checked or check the related tables aforementioned.

SelectRelatedTables

Select Dimension Attributes

There are a couple of web documents:

Attribute Name Attribute Type
Date Key
Full Date Alternate Key Date
English Month Name Literal
Calendar Quarter Quarter
Calendar Year Year
Calendar Semester Half Year
WeekDay Day Week
Lookup Calendar Month – Id Month of Year
English Day Name of Week Regular

Here is a quick picture of our Dimension Attributes:

SelectDimensionAttributes

Completing the Wizard

CompleteTheWizard

Modifying the Date Dimension – Attribute Relationship

There are a couple of web documents:

The Date Dimension is very well understood and it is an hierarchical entity.  That is Day rolls into Week, Weeks into Months, Months into Quarter, Quarters into Semester, and Semesters into Year.

To facilitate summarization and “hint” the system to create optimization tooling, we will create Attribute relationships.

Here is a summary of what we will do:

Defining Attribute Relationships for Attributes in the Calendar Date Hierarchy

  • In the diagram, right-click on the Month Name attribute and then select New Attribute Relationship.
  • In the Create Attribute Relationship dialog box, the Source Attribute is Month Name. Set the Related Attribute to Calendar Quarter. In the Relationship type list, set the relationship type to Rigid.
  • Click OK.
  • In the diagram, right-click the Calendar Quarter attribute and then select New Attribute Relationship.
  • In the Create Attribute Relationship dialog box, the Source Attribute is Calendar Quarter. Set the Related Attribute to Calendar Semester. In the Relationship type list, set the relationship type to Rigid.
  • Click OK.
  • In the diagram, right-click the Calendar Semester attribute and then select New Attribute Relationship.
  • In the Create Attribute Relationship dialog box, the Source Attribute is Calendar Semester. Set the Related Attribute to Calendar Year. In the Relationship type list, set the relationship type to Rigid.
  • Click OK.

And, here are the actual steps:

Design Walkthrough

Initial Screen

Here is the initial screen:

To get to this “Initial” Screen, please do the following:

  • In the Solution Explorer, select the node – Dimensions \ Dim Time
  • Double-click your selection
  • On the Design panel, select the “Attribute Relationships” tab

AttributeRelationship-InitialScreen

There are a couple of aspects that our initial screen illuminates:

  • In the “Attributes” group-box, It shows all the attributes we selected earlier
  • In the “Attribute Relationships” group-box, we can see that the “Time Key” column is the base Attribute that all other Attributes are inferred from
  • On the top pane we do not have any obvious hierarchies; all attributes are flat-lined

Design Walkthrough – Month Name -to- calendar Quarter

Let us draw the relationship between “Month Name” to “Calendar Quarter”

  • Select the Time Dimension
  • Select the Month Name Attribute
  • Right click on that selection
  • From the drop-down menu, select “New Attribute Relationship”
  • The “Create Attribute Relationship” window appears
  • For the “Source Attribute” select “English Month Name” attribute
  • For the “Related Attribute” select the “Calendar Quarter” attribute
  • For the “Relationship type” entry, select “Rigid (will not change over time)”

Step:

Here is our Screen as we map “Month Name” to “Calendar Quarter”:

EnglishMonthName-to-CalendarQuarter-Before

Visual:

Upon mapping “Month Name” to “Calendar Quarter”:

EnglishMonthName-to-CalendarQuarter-After

Explanation:

  • In the “Attribute Relationships” panel, we will see that “English Month Name” maps to “Calendar Quarter”
  • The other attributes stayed keyed from “Time Key”

Design Walkthrough – Calendar Quarter -to- Calendar Semester

  • Select the Time Dimension
  • Select the Month Name Attribute
  • Right click on that selection
  • From the drop-down menu, select “New Attribute Relationship”
  • The “Create Attribute Relationship” window appears
  • For the “Source Attribute” select “Calendar Quarter” attribute
  • For the “Related Attribute” select the “Calendar Semester” attribute
  • For the “Relationship type” entry, select “Rigid (will not change over time)”

Step:

CalendarQuarter-to-CalendarSemester-Before

Visual:

CalendarQuarter-to-CalendarSemester-After

Explanation:

  • In the “Attribute Relationships” panel, we will see that “English Month Name” maps to “Calendar Quarter”
  • In the same panel, we see that “Calendar Quarter” maps to “Calendar Semester”
  • The other attributes stayed keyed from “Time Key”

Design Walkthrough – Calendar Semester -to- Calendar Year

  • Select the Time Dimension
  • Select the Month Name Attribute
  • Right click on that selection
  • From the drop-down menu, select “New Attribute Relationship”
  • The “Create Attribute Relationship” window appears
  • For the “Source Attribute” select “Calendar Semester” attribute
  • For the “Related Attribute” select the “Calendar Year” attribute
  • For the “Relationship type” entry, select “Rigid (will not change over time)”

Step:

CalendarSemester-to-CalendarYear-Before

Result:

CalendarSemester-to-CalendarYear-After

Explanation:

  • In the “Attribute Relationships” panel, we will see that “English Month Name” maps to “Calendar Quarter”
  • In the same panel, we see that “Calendar Quarter” maps to “Calendar Semester”
  • And, “Calendar Semester” maps to “Calendar Year”
  • The other attributes stayed keyed from “Time Key”

Completion

Here is the completed result:

Completed – Attribute Relationships – Visual

AttributeRelationship-Final-PanelTop

Completed – Attribute Relationships – Tabular

AttributeRelationship-Final-PanelAttributeRelationship

 

Modifying the Date Dimension – Attribute – Key Columns

http://technet.microsoft.com/en-us/library/ms166578.aspx

Attribute Mapping

It is important to set key columns for the Attributes.

This is important dues to the fact that our actual data is simple.  We have asked for an Hierarchy view of some of our Attributes.

Let us display a snapshot of our data:


select top 10
			  tblTime.Timekey
			, tblTime.FullDateAlternateKey
			, tblTime.EnglishMonthName
			, tblTime.CalendarQuarter
			, tblTime.CalendarSemester
			, tblTime.CalendarYear

from   dbo.dimTime tblTime

where datepart(day, tblTime.FullDateAlternateKey) = 1

and   tblTime.[MonthNumberOfYear] % 3 = 0

order by TimeKey

If we quickly review our Data, we will see that our data points are very simple. English Month Name, CalendarQuarter, and CalenderSemester do not contain identifying markers that denote which parent they belong to.

And, thus the only way to fully qualify them is through member attributes in their row:

DimensionColumnKeys
And, thus we end up with the need for multi-column keys:

Attribute Name Key Columns
English Month Name CalendarYear
EnglishMonthName
Calendar Quarter CalendarYear
Calendar
Calender Semester CalendarYear
CalenderSemester

Design Walkthrough

To make the changes, please do the following:

  • Access the Solution Explorer
  • Access Dimensions \ Dim Time
  • In the properties window, access each attribute that we need to have composite key columns on

Design Walkthrough – English Month Name

EnglishMonthName

Design Walkthrough – Calendar Quarter

CalendarQuarter

Design Walkthrough – Calendar Semester
CalendarSemester

Modifying the Date Dimension – Attribute – Name & Value Column

http://technet.microsoft.com/en-us/library/ms166578.aspx

To add a bit of polish to your Application, please specify specific column names for your NameColumn and ValueColumn.

In fact, this step is necessary in cases where you have multi-columns for an Attribute key.

If you attempt to Build your project, prior to effecting this case, you will get the error pasted below:

Error Image:

Error - NameColumn should be defined

Error Text:



Error 1 DimensionAttribute [Dim Time].[English Month Name] : The NameColumn should be defined because there are multiple KeyColumns.

Error 2 DimensionAttribute [Dim Time].[Calendar Quarter] : The NameColumn should be defined because there are multiple KeyColumns. 

Error 3 DimensionAttribute [Dim Time].[Calendar Semester] : The NameColumn should be defined because there are multiple KeyColumns. 

Attribute – Name and Value Column – Mapping

Here are the name columns:

Attribute Name Name Column Value Column
English Month Name EnglishMonthName MonthNumberofYear
Calendar Quarter CalendarQuarter CalendarQuarter
Calender Semester CalendarSemester CalendarSemester

Design Walkthrough – English Month Name

EnglishMonthName

Design Walkthrough – Calendar Quarter

CalendarQuarter

Design Walkthrough – Calendar Semester

CalendarSemester

Once the NameColumn, has been effected, we can successfully Build our Project.

Image Output:

Create hierarchies in non-parent child dimensions

Textual Output:


------ Build started: Project: TimeDimension, Configuration: Development ------
Started Building Analysis Services project: Incremental ....
Dimension [Dim Time] : Create hierarchies in non-parent child dimensions.
Build complete -- 0 errors, 1 warnings
========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========

Truthfully, we still have one Warning to take care of:



Dimension [Dim Time] : Create hierarchies in non-parent child dimensions.

We will address that in a later post.

References

References – Dimension Wizard – Date Dimension

References – Attribute Types

References – Attribute Sorting

References – Analysis Services – Data Integrity

References – Analysis Services – Impersonation Options

References – SQL Server – Best Practices

References – SQL Server – Analysis Services – Errors

References – SQL Server – Analysis Services – Errors – Duplicate key has been found

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s