Sql-server – How to compare the number of transactions in the first weeks or months of different stores

sql serverssas

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 

Spain     
         2          Store 1                                              5         6        10
                    Store 2      10          20            40            50        60       85        
         4          
                    Store 3      31          45            100           315       441      625              
                    Store 4                                10            20        32       45
Portugal
        1       
                    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 

Spain     
         2          Store 1       5              6      10
                    Store 2       10             20     40     50     60       85        
         4          
                    Store 3       31             45     100    315    441      625              
                    Store 4       10             20      32    45
Portugal
        1       
                    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]

Best Answer

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.

Related Question