Category Archives: Data Warehousing – DW

Side-by-side Role-Playing Dimensions In Power BI

Role-playing dimension is one those concepts that is discussed a lot from time to time. I also posted an article about implementing role-playing dimensions in Tabular models. In one my recent posts I used the same concept to implement role-playing dimensions in Power BI. So in this post I’m not going to explain role-playing dimensions in Power BI again.

In some cases you need to have especial calculated measures based on the roles you have in a fact table. One the most common role-playing dimensions is Date dimension. Consider you have to show Internet Sales Amount by Order Date, Due Date and Ship Date in a single chart in your report. In this case, having 3 different date tables won’t help us to achieve the goal. Again I refer to my previous post where I said I keep the original Date table in the model for a reason.

Defining a new Calculated Measure in Power BI Desktop

Basically, what I’m going to explain in this post is using inactive relationships between FactInternetSales table and the DimDate dimension by adding a new Calculated measure. In this case, we’ll be able to show Sales Amount by different roles, well, dates in this sample in a single chart.

Continue reading Side-by-side Role-Playing Dimensions In Power BI

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.

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