Data Warehousing and Business Intelligence

10 August 2014

Setting the default date to last available date

Filed under: Analysis Services,MDX — Vincent Rainardi @ 12:56 pm

I always forget what the MDX is for setting the default member of the date dimension to point to the last date available in the fact table. Worse still, when I found it after I searched in Google, I always forgot what they mean. I thought it’s about the time I write it down.

This is the MDX:

tail(nonempty([DateDim].[DateAttribute].Members)).item(0)

Note:

  • [DateDim] is the name of the date dimension.
  • [DateAttribute] is the name of the date attribute in the date dimension, which you want to get the latest member.
  • [Measure1] is the measure from where you want to get the latest member on.

We can test it in Advanture works like this:

select tail(nonempty([Date].[Date].Members)).item(0) on 0
from [Adventure Works];

And this is what they means:

  • Tail returns a set (the last N subset).
  • To get a tuple out of a set, we use Item function.
  • Item(0) means the first tuple in the set.
  • The nonempty function limits the Date members to just the ones which has the default measure.

The above MDX expression uses the default measure. To use a specific measure, the MDX is like this:

select
  tail
  ( NonEmptyCrossJoin
    ( [Date].[Date].Members *
      [Measures].[Internet Sales Amount]
    )
  ).item(0).item(0) on 0
from [Adventure Works];

Meaning:

  • The tail returns 2 dimensional set of “[July 31st, 2008], [Internet Sales Amount]”.
  • The first Item(0) gives us “[July 31st>, 2008], [Internet Sales Amount]” as a 2 dimensional tuple.
  • The second Item(0) gives us the first item of that tuple, which is “[July 31st, 2008]”, as a member.
  • You can try it be removing just 1 item(0), to see the difference.

If the date dimension is used a few times in the cube with different names (role play dimensions), we can’t put the Default Member in the Property of the Date attribute because it will cause name resolution problem. Instead, we need to put it in the Calculation Script, like this:

ALTER CUBE CurrentCube UPDATE DIMENSION [Date],
DEFAULT_MEMBER = 'tail(NonEmptyCrossJoin([Date].[Date].Members * [Measures].[Internet Sales Amount])).item(0).item(0)';

We can put this ALTER CUBE anywhere in the Calculation Script; it can be at the top or bottom, it doesn’t matter.

The error message we get if we put the default member in a role play date dimension is:

Error 21: The specified default member '[Date].[Date].&[YYYYMMDD]' must be from the '[Delivery Date].[Date]' hierarchy.

But if we change it to [Delivery Date], it will say:

Error 21: The specified default member '[Ship Date].[Date].&[YYYYMMDD]' must be from the '[Delivery Date].[Date]' hierarchy.

Hope this helps. As usual if you have any questions or points to discuss you can contact me at vrainardi@gmail.com.

1 September 2011

Tomislav’s MDX Book

Filed under: Analysis Services,MDX — Vincent Rainardi @ 11:15 pm
Tags: ,

I met Tomislav Piasevoli for the first time in Manchester in 2009 at SQLBits conference. I attended at his Advanced MDX session and said to him afterwards that his MDX skill was one of the highest in the world.

Last month his new MDX book was published, titled MDX with Microsoft SQL Server 2008 R2 Analysis Services: Cookbook. I am amazed by this book because of how it is structured, as well as the quality of the content. It contains 80 problems. Each problem is described in problem-solution-explanation format.

To me this format is ideal. This is how people should learn. It is always easier to learn by example, rather than by theory. I think all IT forums should have this format, i.e. after 2 years of people asking and answering questions, the moderator should package up the knowledge in the problem-solution-explanation format. I’m sure this FAQ section will answer 90% of the questions in that forum.

We are all so lucky that Tomislav has done this for us in MDX. I wish somebody else do the same in AS, IS, RS, SQL and all other areas of DWBI. The “cook book” format is spot on!

The book contains extremely useful stuff. You only need to look at the table of content to see how useful this book is. From the basic, everyday problem to very advanced ones, they are all there. Date calculations, formatting, navigation, DMVs, context-aware, they are all there. This is a book that provides a guiding hand to SSAS developers. I think all SSAS developers need to have this book inside their laptop, iPad or mobile phone*. It will help us solve most of the MDX problems in our work. Essentially, this book contains the code that you need to type into your cubes.

*or on their desks, for those who still use paper version. For this book the electronic version is better, because you can search your problem and quickly find the solution. Very handy when you have to finish the cube tomorrow!

I need to emphasise that we are very lucky Tomislav spent a lot of his time writing this book. Not many people in the world have the required level of skill to do it. As I said in the beginning, Tomislav’s MDX skill is one of the highest in the world, along with people like Chris Webb, Greg Galloway, Deepak Puri, Darren Gosbell, and Marco Russo, all of whom are the reviewers of this book. If top people like them spend their precious time writing and reviewing this book, those of us who work on MDX should read it! It is a real treasure.

This is my favourite MDX book. Thank you Tomislav for writing it.

Vincent Rainardi, 2/9/2011.

Link to the book’s page on publisher’s web site: http://link.packtpub.com/UUrJM6

27 July 2010

MDX: Except

Filed under: Analysis Services,MDX — Vincent Rainardi @ 1:52 pm
Tags: ,

Background

It’s a stock brokerage firm. They do some promotion campaigns and would like to know how many customer accounts in each campaign never bought or sold a certain stock. They would like to have this as a calculated measure in the cube.

Let’s do it in SQL first, then in MDX. This way we know what the result should be, so we can verify the MDX output.

So the article will flow like this:

  1. Create SQL tables. Do this as simple as possible. Only create the neccesary columns.
  2. We populate these tables with data.
  3. Write a SQL query which gives us the count of customer accounts in each campaign that never bought or sold a certain stock (say ‘stock1’)
  4. Create the SSAS cube from those tables.
  5. Write an MDX query which gives us the count of customer accounts in each campaign that never bought or sold stock1.
  6. Explain the MDX

If you are looking only for the MDX just scroll down to the end of this article.

1. Create the SQL tables

Let’s create the table:

— Create tables
create table dim_campaign
( campaign nvarchar(10) )
— simplified (no media, target, design)

create table dim_account
( account nvarchar(10) )
— simplified version (no name, number, customer details)

create table dim_stock
( stock nvarchar(10) )
–simplified (no symbol, company name, sector, segment, cap)

create table fact_campaign
( campaign nvarchar(10),
account nvarchar(10),
hit int
)
— simplified version (no run date, duration & budget)

create table fact_transaction
( account nvarchar(10),
stock nvarchar(10),
qty int
)
— simplified (no dates, price, amount)

I simplified every table. Only creating columns which were absolutely necessary.

2. Populate the tables

— Dim Campaign
insert into dim_campaign values (‘campaign1’)
insert into dim_campaign values (‘campaign2’)

— Dim Account
insert into dim_account values (‘account1’)
insert into dim_account values (‘account2’)
insert into dim_account values (‘account3’)
insert into dim_account values (‘account4’)

— Dim Stock
insert into dim_stock values (‘stock1’)
insert into dim_stock values (‘stock2’)

— Fact Campaign
insert into fact_campaign values (‘campaign1’, ‘account1’)
insert into fact_campaign values (‘campaign1’, ‘account2’)
insert into fact_campaign values (‘campaign2’, ‘account3’)
insert into fact_campaign values (‘campaign2’, ‘account4’)

— Fact Transaction
insert into fact_transaction values (‘account1’, ‘stock1’,100)
insert into fact_transaction values (‘account2’, ‘stock1’,100)
insert into fact_transaction values (‘account3’, ‘stock1’,100)
insert into fact_transaction values (‘account2’, ‘stock2’,100)
insert into fact_transaction values (‘account3’, ‘stock2’,100)
insert into fact_transaction values (‘account4’, ‘stock2’,100)

Now let’s query them:

select * from dim_campaign
select * from dim_account
select * from dim_stock
select * from fact_campaign
select * from fact_transaction

3. Write the T-SQL query:

So the tables are all populated now. We need to find out how many accounts in each campaign that never bought or sold stock1.

select distinct account from fact_transaction where stock <> ‘stock1’

Output: account2, account3, account4

This is not what we want. Account2 and account3 have purchased stock1.
What we want is account4 only.

So looking at the fact_campaign table, campaign1 is for account1 and 2, and campaign2 is for account3 & 4:

So how many accounts in each campaign that never bought or sold stock1?
Campaign1   0
Campaign2   1   (account4)

That’s the answer. Now let’s write the T-SQL query, then the MDX query.

select distinct account from fact_transaction
where account not in (select distinct account from fact_transaction where stock = ‘stock1’)
Output: account4

Good, that’s heading to the right direction. Now bring-in the campaign:

select distinct FT.account, FC.campaign
from fact_transaction FT
join fact_campaign FC on FC.account = FT.account
where FT.account not in (select distinct account from fact_transaction where stock = ‘stock1’)

Output:

So far so good. Now let’s list down all campaign, then join to the above output.

select DC.campaign, count(x.account) as count_of_accounts
from dim_campaign DC
left join
( select distinct FT.account, FC.campaign
from fact_transaction FT
join fact_campaign FC on FC.account = FT.account
where FT.account not in (select distinct account from fact_transaction where stock = ‘stock1’)
) x on x.campaign = DC.campaign
group by DC.campaign

Output:

In the above SQL, we do “group by and count”, because the requirement is to find out how many accounts in each campaign that never bought or sold stock1.

So that’s the SQL query. Now let’s do the MDX. But before that we need to create the cube first.

4. Create the cube

Data source view:

Cube structure:
(as the dimensions are straight forward, I used the cube wizard to create the dimensions)

Define many-to-many relationship:

Browse the cube:

5. Write the MDX

Now we write the MDX:

with member [Measures].[X] as
distinctcount
( nonemptycrossjoin
  ( { [Measures].[Qty] },
    { except
      ( [Dim Stock].[Stock].[Stock].Members,
        [Dim Stock].[Stock].CurrentMember
      )
    },
    { [Dim Account].[Account].[Account].Members }
  )
)
select X on columns,
non empty [Dim Campaign].[Campaign].[Campaign].members on rows
from [Campaign]
where [Dim Stock].[Stock].&[stock1];

Output:

This is not what we are looking for, because account1, account2 and account3 did bought stock1. The question was: how many accounts in each campaign that never bought or sold stock1? So for campaign1 (account 1 & 2) the answer is 0, and for campaign2 (account 3 & 4) the answer is 1 (only account4).

Here’s the correct MDX:

with
set set1 as
nonempty
( [Dim Account].[Account].[Account].Members,
  [Dim Stock].[Stock].&[stock1]
)
member x as
count
( except
  ( nonempty
    ( [Dim Account].[Account].[Account].Members,
      ( [Dim Campaign].[Campaign].CurrentMember,
        [Measures].[Qty]
      )
    ), set1
  )
)
select {x} on columns,
non empty [Dim Campaign].[Campaign].[Campaign].members on rows
from [Campaign]

Output:

6. Explanation

set1 is the accounts which have bought/sold stock1, i.e. {account1, account2, account3} as shown below:

with set set1 as
nonempty
( [Dim Account].[Account].[Account].Members,
  [Dim Stock].[Stock].&[stock1]
)
select set1 on columns
from [Campaign]

The nonempty inside the except is to get the accounts for each campaign:

with set x as
nonempty
( [Dim Account].[Account].[Account].Members,
  ( [Dim Campaign].[Campaign].CurrentMember,
    [Measures].[Qty]
  )
)
select {x} on columns,
non empty [Dim Campaign].[Campaign].[Campaign].members on rows
from [Campaign]

Output:

Except takes a way member from a set. For example, campaign2 is for account 3 & 4. So if we take away account1,2&3 from account3&4 what’s left is account4:

with set x as
except
( { [Dim Account].[Account].[Account].[Account3],
    [Dim Account].[Account].[Account].[Account4]
  },
  { [Dim Account].[Account].[Account].[Account1],
    [Dim Account].[Account].[Account].[Account2],
    [Dim Account].[Account].[Account].[Account3]
  }
)
select {x} on columns
from [Campaign]

Output:

The complete MDX is basically saying:

  1. Create set1 = all account which have bought or sold stock1
  2. Find out accounts for each campaign using nonempty
  3. Take away 1 from 2 using except
  4. Count the members in that set
with
set set1 as
nonempty
( [Dim Account].[Account].[Account].Members,
  [Dim Stock].[Stock].&[stock1]
)
member x as
count
( except
  ( nonempty
    ( [Dim Account].[Account].[Account].Members,
      ( [Dim Campaign].[Campaign].CurrentMember,
        [Measures].[Qty]
      )
    ), set1
  )
)
select {x} on columns,
non empty [Dim Campaign].[Campaign].[Campaign].members on rows
from [Campaign]

25 June 2010

MDX: EXISTING

Filed under: Analysis Services,MDX — Vincent Rainardi @ 2:31 pm
Tags: ,

EXISTING evaluates a set in the current context. It’s like (hierarchy, measure), except that we don’t have to specify the hierarchy.

In this MDX, measure X is the Sales Amount for the current country:

with member [Measures].[x] as
( [Sales Territory].[Sales Territory Country].CurrentMember,
[Measures].[Internet Sales Amount]
)
select [Product].[Category].[All].Children on 1,
[Measures].[x] on 0
from [Adventure Works]
where [Sales Territory].[Sales Territory Country].[United Kingdom];

Output:

Using EXISTING, it’s like this:

with member [Measures].[x] as
sum(existing [Measures].[Internet Sales Amount])
select [Product].[Category].[All].Children on 1,
[Measures].[x] on 0
from [Adventure Works]
where [Sales Territory].[Sales Territory Country].[United Kingdom];

Output:

Measure X is the Sales Amount in the current context i.e. current country.

Simple, isn’t it?

21 June 2010

MDX: Filter

Filed under: Analysis Services,MDX — Vincent Rainardi @ 10:03 pm
Tags: ,

We can filter an axis based on the members, or based on the measure value.

Based on the members: begins with F or after      Based on the measure value: Sales > $40,000

To filter based on members, we can use string comparison:

select non empty
filter
( {[Customer].[City].[All].Children},
[Customer].[City].CurrentMember.Name > “F”
) on 1,
[Measures].[Internet Sales Amount] on 0
from [Adventure Works];

Or use string functions:

select non empty
filter
( {[Customer].[City].[All].Children},
left([Customer].[City].CurrentMember.Name,1) = “F”
) on 1,
[Measures].[Internet Sales Amount] on 0
from [Adventure Works];

To filter based on measure value, we use numeric comparison:

select non empty
filter
( {[Customer].[City].[All].Children},
[Measures].[Internet Sales Amount] > 40000
) on 1,
[Measures].[Internet Sales Amount] on 0
from [Adventure Works];

Other examples of string functions:

Second letter is A:
mid([Customer].[City].CurrentMember.Name,2,1) = “A”

Ends with A:
right([Customer].[City].CurrentMember.Name,1) = “A”

Begins with O and ends with A:
left([Customer].[City].CurrentMember.Name,1) = “O” and
right([Customer].[City].CurrentMember.Name,1) = “A”

MDX: Sorting

Filed under: Analysis Services,MDX — Vincent Rainardi @ 9:56 pm
Tags: ,

We can sort an axis based on the member on that axis, or on the measure.

Based on the member:                        Based on the measure:

To sort an axis based on the member, we can use the NAME property:

select non empty
order
( {[Customer].[City].[All].Children},
[Customer].[City].CurrentMember.Name, DESC
) on 1,
[Measures].[Internet Sales Amount] on 0
from [Adventure Works];

To sort an axis based on the measure, we use the measure name:

select non empty
order
( {[Customer].[City].[All].Children},
[Measures].[Internet Sales Amount], DESC
) on 1,
[Measures].[Internet Sales Amount] on 0
from [Adventure Works];

Blog at WordPress.com.