Slowly Changing Dimension in SSAS Cube

Posted on November 28, 2014 by

 

This is a simple example of SCD (Type-2) in OLAP cube.  We have a Dimension table for Employee and their Departments. This dimension is an SCD. One employee worked in different department over the course of time. EmpID is business key and SID is surrogate key.

DimEmpolyee table

112814_1040_SlowlyChang1.png

We have another dimension called DimTime. It’s a very simple Month Dimension table.

Fact table looks like this:

FactTable

Fact

Both business key (EmpID) and Surrogate key (EmpSID) is present in Fact table, but we will use only EmpSID key here.

Let’s have a look at the cube now. First, look at the Dimension called [Dim Employee]

Dimension

And it’s attribute relationship…..

AttrRelationship

See carefully that I did not use SID in hierarchy. I used employee name (attribute called ‘Employee’) for reporting purpose. Its key column and name column both are set to Employee column of the physical dimension table.

We are almost done. Let’s process the cube and write an MDX to see result:

MDXResult

 

You can clearly see that an employee named David sold different amounts in different months. David sold under Product Development Department for up to Aug-2013 and then he started selling under Android Apps department from Sep-2013.

Hope you will like it.

Posted in MDX, SSAS (Analysis Service), Cube | Tagged , , , , , , , | Leave a comment

5 Tips to avoid running out of space in SQL Server Database

Posted on November 28, 2014 by

Try to follow these tips. Each of these topics has been explained in different websites. So, I am not going to explain them here.

1. Design

Design your database efficiently (normalization, use of foreign keys, getting rid of unnecessary tables, selecting appropriate data type etc..). Clean up your database before your go LIVE!

2. Estimate Data Volume

Estimate the total data volume you need in your database. Don’t get surprise in future. Know your data and know the space required.

3. Estimate Log file size and Temp DB Size

Set back up plan for your database and then chose a logging mode (Simple, full or bulk logged). Try to find out the log file space required in the first few runs and then set log file size accordingly. If your process uses ‘#’tables (Temp tables), try to find out the space required there as well. Closely monitor TempDB size in first few runs and then set its size accordingly.

4. Compression

After a few months (could be few years depending upon the rate of change in your data), you can run Table compression in your tables. It’s good for reporting systems like DWH but might reduce performance in transactional system. Read pros and cons first.

5. Shrink (use carefully)

If any big sized Ad-hoc operations happen in your database, you can shrink your log files. Otherwise, shrinking of log files will not benefit you much. It will anyway take up the allotted space. Same goes for Database shrink. Read articles found on web. Database shrink will introduce fragmented index. In worst case, you can drop/recreate index rather than rebuild them.

Posted in SQL, Database | Tagged , , , , , , | Leave a comment

Ceiling in MDX

Posted on June 19, 2013 by

It’s just a simple solution to find ceiling of a measure with help of some string operation. We have a measure called [Measures].[Marks]. Now, we define a calculated measure like below:

CREATE MEMBER CURRENTCUBE.[Measures].[CeilingMarks]
AS CInt(Mid(cstr([Measures].[Marks]),0,InStr(cstr([Measures].[Marks]),“.”)-1))+1,
VISIBLE = 1 ;

It works like this;

Let’s say our number is 315.5, first it finds the decimal point’s position. In our case, it is 4. Then, it uses the MID function to find the number prior to that decimal point. In our case, it is 315. And then, it just adds one, so it becomes 316.

Posted in MDX, SSAS (Analysis Service), Cube | Tagged , , | Leave a comment

Pre order, Post order, Level order (BFS) traversing with MDX

Posted on December 17, 2012 by

A tree or hierarchy of my sample cube is being shown below:


BFS or Level Order Traversing:

with
member [Measures].[x]
as [Dim Category].[Parent].currentmember.level.ordinal

select
order(([Dim Category].[Parent].members),[Measures].[x],BASC)
on 1 ,
[Measures].[x] on 0
from SalesCube
And, here goes the output:


Pre-Order traversing:
By default, traversing in MDX is always in pre-order.
select [Dim Category].[Parent].members
on 1 ,
[Measures].[Sales] on 0
from SalesCube


Post-Order Traversing:
Using “Hierarchize” function, with parameter “POST”
select
hierarchize([Dim Category].[Parent].members,post)
on 1 ,
[Measures].[Sales] on 0
from SalesCube

Posted in MDX, SSAS (Analysis Service), Cube | Leave a comment

Leaf members do not sum up to parent – Part 2 (parent child hierarchy)

Posted on October 11, 2012 by

In part 1, we saw user defined hierarchies. For parent child hierarchy, we can use Unary Operator Column. Let’s take the same example from Part1. We have a simple cube with two dimensions called DimCompany and DimCategory, and only one measure called Sales. DimCategory table looks like this: Id    CategoryItems        Parent

1        Phone                            4

2         Tab                               4

3      Web Applications         5

4         Hardware                    6

5          Software                     6

6        AllCategory                NULL

If we browse the cube taking DimCategory to axis 1 and DimCompany to axis 0, we get to see the below result:
Now, imagine that we received an ad-hoc request from our source data provider that total Hardware Sales for Apple should be 9000 and that of Google should be 12000. There have been some unreported sales for both these companies which do not fit into the available sub categories (phone and tab). So, the requirement becomes likes this: For Apple, Phone sales is 1000 and Tab Sales is 7000, but total Hardware Sales is 9000 (1000 greater than children sum) For Google, Phone sales is 8000 and Tab Sales is 0, but total Hardware Sales is 12000 (4000 greater than children sum) To solve this, first we have to insert Hardware sales for all companies into Fact Table. Id for Hardware is 4. Newly inserted records are shown below:

Now, we need to add unary operator column in Dim Category table. So, the table looks like this: Id    CategoryItems              Parent        UnaryOperator

1               Phone                         4                        ~

2                Tab                            4                        ~

3            Web Applications       5                     NULL

4                  Hardware               6                     NULL

5                   Software                6                     NULL

6                AllCategory             NULL             NULL

Tilde (~) has been used to ignore these two members while calculating parent sum. Now, we need to change two properties (MembersWithData and UnaryOperatorColumn) of Parent attribute of our parent child dimension:

Now, process the cube and final result looks like this:

Yes, we could insert incremental value for Hardware sales and avoid using unary operator column. Result would be same.

Posted in MDX, SSAS (Analysis Service), Cube | Tagged , , , | Leave a comment

Leaf members do not sum up to parent – Part 1 (user defined hierarchies)

Posted on October 9, 2012 by

We have a simple cube with two dimensions called DimCompany and DimCategory, and only one measure called Sales. DimCategory table looks like this:

Id    SubCategory                 Category

1    Phone                              Hardware

2    Tab                                  Hardware

3    Web Applications         Software

The key attribute is Id and attribute relationship is     Id->Sub Category->Category

There is one user-defined hierarchy called “Hierarchy” in DimCategory.

In BIDS 2008 cube browser, if we drag the user defined hierarchy in one axis and company names in another, we can see this report:


In Hardware category, total Apple sales is 8000, total Google sales is 8000, total Microsoft sales is 8000 and total Samsung sales is 6000.

Now, imagine that we received an ad-hoc request from our source data provider that total Hardware Sales for Apple should be 9000 and that of Google should be 12000. There have been some unreported sales for both these companies which do not fit into the available sub categories (phone and tab). So, the requirement becomes likes this:

For Apple, Phone sales is 1000 and Tab Sales is 7000, but total Hardware Sales is 9000 (1000 greater than children sum)

For Google, Phone sales is 8000 and Tab Sales is 0, but total Hardware Sales is 12000 (4000 greater than children sum)

One possible solution could be to introduce another Sub Category called “Dummy”. So, DimCategory table looks like this:

Id    SubCategory                 Category

1    Phone                              Hardware

2    Tab                                  Hardware

3    Web Applications         Software

4    Dummy                          Hardware

And, we have to insert the unreported extra sales into Fact table (as “Dummy” sub category sales). Our Fact table has only three columns; CompanyId, CategoryId and Sales. So, the insert statement will look like this:

Insert into Fact (CompanyId, CategoryId, Sales) values (2, 4, 1000) –For Apple

Insert into Fact (CompanyId, CategoryId, Sales) values (4, 4, 4000) –For Google

Now, we process the cube and browse it:


It would be great if we could hide “Dummy”. For that, all we have to do is replace the value “dummy” with “Hardware” in DimCategory table, then go to the properties of sub-category level of user defined Hierarchy of DimCategory dimension and set “Hide Member If” to “Parent Name”.


Final result looks like this:


We can also insert total values instead of incremental values; 9000 for Apple and 12000 for Google and use MDX Scope statement to handle Parent sum.

Posted in MDX, SSAS (Analysis Service), Cube | Tagged , , , , | Leave a comment

Maximum value of a measure from an MDX result set

Posted on July 27, 2012 by

Below is an MDX Query which returns the sales of different product categories:

select {[Dim Category].[Category].[Category]} on 1,
{[Measures].[Measure Value]} on 0
from [MY CUBE]

result set returned:

We can see that the maximum value is 42092.83. Using max and axis function, we can show the maximum value as a seperate measure. So, we add a measure and the query becomes :

with
member [Measures].[MAXSales]
as
max(axis(1),[Measures].[Measure Value])

select {[Dim Category].[Category].[Category]} on 1,
{[Measures].[Measure Value],[Measures].[MAXSales]} on 0
from [MY CUBE]

the result set looks like this:

Posted in MDX, SSAS (Analysis Service), Cube | Tagged , , , | 2 Comments

Understanding crossjoin, set, tuples with help of SetToStr function

Posted on July 26, 2012 by

MDX cross join function returns the cross product of two sets. For example, we have two sets A = {1,2} and B = {x,y}. After crossjoin, we get, A X B = {(1,x),(1,y),(2,x),(2,y)}.

(1,x), (1,y) are individual tuples. The resultant set consists of these tuples.

Now, let’s see the below mdx query which uses crossjoin to form a named set called ASet. Crossjoin is done between a particular member of category hierarchy (“Hair Care”) and sub-categories hierarchy. A measure called [SetToString] is decalred. It calls SetToStr function on the named set. So, all the members of the set are converted into string.

with
set ASet
as
{crossjoin([Dim Category].[Category].[Category].&[Hair Care],[Dim Category].[Sub Category].[Sub Category])}
member [Measures].[SetToString]
as
SetToStr(ASet)
select ASet
on 1,
{[Measures].[SetToString]}
on 0
from [MY_CUBE]

returns result

A close look at the returned measure
{
( [Dim Category].[Category].&[Hair Care], [Dim Category].[Sub Category].&[Conditioner] ), <————-tuple
( [Dim Category].[Category].&[Hair Care], [Dim Category].[Sub Category].&[Shampoo] )      <————–tuple
}

Posted in MDX, SSAS (Analysis Service), Cube | Tagged , , , | Leave a comment

Composite Domain Rules (CD Rules) in Data Correction

Posted on April 9, 2012 by

Hello all

I am just learning DQS and would like to share a few things that I learned so far. One of them is the use of composite domains (CD). I will go straight into an example rather than wasting time on definitions. You can find lots of them in MSDN.

Let’s say, you have a table as shown below:

race_venue       year          won_by

malaysia            2012         UNKNOWN
australia            2012         XYZ

Now, you like to correct the values of the ‘won_by’ column using DQS. As, u can see, the value of the field ‘won_by’ depends upon the other two columns. That’s why we need a composite domain(CD) in DQS. Because, we are dealing with cross domain relationships here.

Now, in the domain management section of your knowledge base (KB), create 3 domains called  venue(string), year (int) and winner(string). Now create a CD called ‘set_winner’ combining all the 3 domains.

 Now, open the “CD Rules” tab and create a rule there. The rule looks like below:

CD Rules

In case, u r travelling the same troubled path as I did and wondering how to add another domain name/clause in the “IF” section of the rule, the solution is ‘clicking the right button’. If u click on right button, u will see an option called “Add Clause”.

So, u r done with the setting up of domains. It’s time to create a new data quality project(cleansing project). After u create a new project, map the 3 columns with the 3 domains. Don’t forget to include the composite domain before start processing your data.

composite domain project

Now, u r ready to start processing your data. Here is the result of applying our CD Rule on the data:

Posted in Data Quality Service (DQS) | Tagged , , , , , | Leave a comment

Set Synonyms in DQS

Posted on April 9, 2012 by

This is my first post on the newly added Data Quality Service of MS SQL Server. It’s about setting up synonyms in Domain Values. The steps are :

1. select “domain management” activity for your knowledge base (KB)
2. select a domain from your domain list
3. select the “domain values” tab
4. select all the values that you like to make synonyms of each other
5. right click (all the selected values are highlighted bright yellow)
6. select ‘set as synonyms’ from the right click menu
…and the job done

Posted in Data Quality Service (DQS) | Tagged , , , | Leave a comment