I need to make a comparison of the number of transactions per week and month of different stores since its opening date. The problem is that the stores has a different opening date.

So, The idea is to compare the number of transactions in the first weeks or months

That's what I have:

 Canal Key 1

   Recount FactTransactions

                              Month1-2015 Month2-2015 Month3-2015 Month4-2015 Month5-2015 Month6-2015

 Type of store     Cod Store 

         2          Store 1                                              5         6        10
                    Store 2      10          20            40            50        60       85        
                    Store 3      31          45            100           315       441      625              
                    Store 4                                10            20        32       45
                    Store 5                                                                 12

That's what I want to obtain (comparing different stores since its opening date):

    Canal Key 1

   Recount FactTransactions

                              Month1 (or Week) Month2 Month3 Month4 Month5 Month6

 Type of store     Cod Store 

         2          Store 1       5              6      10
                    Store 2       10             20     40     50     60       85        
                    Store 3       31             45     100    315    441      625              
                    Store 4       10             20      32    45
                    Store 5                                                                 12

That's my data model:

 FacTransaccion: *Measures* Recount FactTransaction / Amount / Amount discount / Unit Price

 DimDate: *Hierachy* Date (YYYYMMDD) / Month / Month Name / Week of Month / Week of year / Year (It's the same Dimdate than AdventureWorksDW2008 with its member calculation and relationship)

 DimProduct: *Hierachy* Product Key

 DimStore: *Hierachy* Type of store / Store key / Opening date / Country 

 DimType: *Hierachy* Type of transaction

 DimCanal: *Hierachy* Canal  (there are two channel where people can buy) 

Reading a lot about MDX, after a thousand times of MDX. I don't have anything.

Several attemps:

Attempt #1:

SELECT CROSSJOIN({TAIL([Dim Store].[Opening date].Children, 30) }, { [Measures].[Transactiones] }) ON COLUMNS
    ,{ [Dim Store].[Store names].Children} ON ROWS FROM [CUBE]

Attempt #2: (It does not work) It seems to me better than first attempt.

WITH MEMBER [Measures].[Count] AS
SUM([Dim Store].[Name Store].currentmember*Tail([Dim Store].[Opening Date].children,1),
[Measures].[Count Fact Transacciones])
SELECT {[Measures].[Count]} ON COLUMNS,
{[Dim Store].[Name Store].[All]} ON ROWS
from [CUBE]
  • So store one opened in month 4, store 2 in month 1 and so on? – Tom V - try topanswers.xyz Aug 27 '15 at 13:41
  • Yes. There are like 40 stores, each one of them has a different opening date. – Miguel Aug 27 '15 at 14:08

We really need more detail on your measures and dimensions to give you an exact answer on this, but since you are using SSAS a few general rules apply.

If you add more detail I can expand my answer to be in line with your data model.

Since you want to compare the number of transactions you should have a measure showing the number of transactions.

As you want to compare them over time you would also need a Time Dimension with a hierarchy.

If you have both those prerequisites you can resort to MDX to calculate sums and differences of your measure over your dimension hierarchies and members.

In essence what you need to try is to construct a set and sum your measure over that set.

Sum({[opening date]:[opening date+2weeks]},[Measures].[Number of transactions])

The hard part in your problem will be constructing the set however. And that's the part we can't answer without your data model and some sample data.

As you say you have different start dates for different stores you could use a property on the store dimension indicating it's start date and use that to construct a set to sum over.

A great resource to get started with MDX is the Stairway to MDX series on SQL Server Central.

You should look at functions like PeriodsToDate.

You also have the option of resorting to VBA functions and could use that to use DateDiff and DateAdd if you can't get there with pure MDX.

You can even write your own SSAS Stored procedure if all else fails.

  • Thank you. It's a great resource to start with MDX. But I also need help to construct the set. Meanwhile I'll try to practice with AdventureWorks. Let me know if you need more information about my data model – Miguel Aug 28 '15 at 15:04
  • @Miguel didn't have time yet, I'll look into an example when I find some spare time but I'm a bit overloaded at the moment – Tom V - try topanswers.xyz Sep 1 '15 at 18:48
  • Hello @Tom V, as I told you I'm still working on it. I think I have advanced a lot, I have a question; How can I integrate the MDX in Analysis Services? I mean, I'm doing the query in SQL Server Management Studio, but I don't know How can I integrate it in SQL Data Tools (BI SSAS) – Miguel Sep 10 '15 at 14:00
  • I'm not able to do it. I have tried a lot of querys, but it's impossible for me – Miguel Sep 15 '15 at 17:38

Ok, I finally did it! Without a complicated set, just creating a new dimension "DimOrdinal" with values 1-9999, It's connected to the Fact table with a new Ordinal column which contents a numerical value. That new column contents the datediff in days to the openingdate :D

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.