Author Archives: Zahid

About Zahid

10+ years of experience in BI and DWH technologies. Fields of expertise are BI Reporting (MSBI, Microstrategy, Excel, Power BI) , ETL, Data Warehouse, OLAP Cube, MDX etc.

Slowly Changing Dimension in SSAS Cube

  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 … Continue reading

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

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

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, … Continue reading

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

Ceiling in MDX

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 ; … Continue reading

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

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

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 … Continue reading

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

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

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 … Continue reading

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

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

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 … Continue reading

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

Maximum value of a measure from an MDX result set

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 … Continue reading

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

Understanding crossjoin, set, tuples with help of SetToStr function

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 … Continue reading

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

Composite Domain Rules (CD Rules) in Data Correction

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 … Continue reading

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

Set Synonyms in DQS

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 … Continue reading

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