Role-playing Dimension in SSAS Tabular Models

First of all I’d like to explain what a Role-playing dimension actually means. Then I’ll express the way you can implement it in a SSAS tabular model.

When you link a dimension to a fact table several times for logically distinctive roles you’re using a role-playing dimension.

The key points are:

1.       You are linking a fact table to a dimension multiple times. The relationships are defined by linking multiple foreign keys in the fact table to a single key in the dimension table.

2.       Each linkage represents a single role or concept

The most popular role-playing dimensions are DimDate and DimTime. Do you want to see how to implement Role Playing Dimensions in Power BI, Click here and here.

NOTE: The sample is from Microsoft “AdventureWorksDW” for SQL Server 2012 and might be different from your own data warehouse design.

For instance, in a sales system that you have something like FactInternetSales fact table which has several links, or relationships, to a DimDate or DimAddress for distinct concepts like “Order Date”, “Ship Date” and “Due Date”.

As you see, all of the above columns obviously represent different meanings of date. In the data warehouse design you’ll see something like this:

role-playing dimension 01

Although this is absolutely OK in the relational database layer, but, this sort of relationship is NOT permitted in the tabular model, so what should we do?

Continue reading “Role-playing Dimension in SSAS Tabular Models”

Creating a Simple Date Dimension Using Recursive Common Table Expressions (CTE)

In this post I’ll explain how to create a simple date dimension to use it in your data warehouses and your BI solutions. So, this article is for you if you need a fast and easy way to make a simple date dimension that supports the most commonly used date elements like

·         Integer date key

·         Different date formats

·         Quarter

·         Month names

·         Week numbers

·         Day of the week

·         Day of the year

·         Is day end of month

·         Not available (N/A) row

Due to the fact that there are lots of fellows that are still using SQL Server 2008 and earlier, I put the codes that support SQL Server 2008  as well as SQL Server 2012. But, I’ve commented the 2008 lines.

Continue reading “Creating a Simple Date Dimension Using Recursive Common Table Expressions (CTE)”

Digging into SQL Server 2012 columnstore index

The SQL Server 11.0 release (code named “Denali”) introduces a new data warehouse query acceleration feature based on a new type of index called the columnstore. Columnstore indexing is officially announced in SQL Server 2012. It is working based on xVelocity memory optimised technology and it improves data warehouse query performance significantly. Due to the fact that data warehousing, decision support systems and business intelligence applications are growing very quickly, we need to be able to read and process very large data sets quickly and accurately into useful information and knowledge. Columnstore index technology is especially appropriate for data warehousing data sets. It improves the common data warehousing queries’ performance significantly.

Continue reading “Digging into SQL Server 2012 columnstore index”