Dharma's Data Blog

My second post of the Relative Period Modelling – detailing how to use the Relative Period database design in a SSAS (SQL Server Analysis Services) multidimensional cube. Remove the need to write a SINGLE MDX code for relative periods!

Note, this post assumes a good understanding of modelling with SSAS.

The first part, I detailed the database design of the Relative Period modelling – that design allows custom relative periods to be modeled and not be restricted by the relative period functions provided by the databases and SSAS. Please review that post if you have not already.

As with best practices of SSAS modelling (Multidimensional or Tabular), I am using VIEWS for the modelling rather than physical tables (Named Queries should also be avoided where possible) – done to avoid unnecessary cube rebuilds.

Note: I prefer to define all cube views in the cubefeed schema, allows the views to be isolated. 

The RP prefix has been used to distinctively identify objects related to Relative Periods, this will assist in large models dealing with numerous facts.
(Dim Date based views have NOT been prefixed with RP_ to avoid confusing modelers)
Am utilising AdventureWorksDW2016 database for other facts/dimensions

SSAS Modelling with Relative Period

• Create a new SSAS multidimensional database, called “RelativePeriods”
  Create a datasource and dsv.
• Import all the view objects defined above into DSV and map as below:
  
  Note: Relative Periods are kept isolated from other objects. They are linked in the cube via Dimension Usage – detailed later.
• Dimensions
  Create the following dimensions (3 for Relative periods & 2 for the sample adventure works db)
  • DimDate
    This dimension is built off DimDate view, define hierarchies as needed.
    Ensure key is DateKey
    
    
  • RP_ComparativePeriod
    a very simple one attribute dimension, using the similar named corresponding view created earlier (cubefeed.RP_DimComparativePeriod)
  • RP_DateSet
    a very simple one attribute dimension, using the similar named corresponding view created earlier (cubefeed.RP_DimDateSet)
  • Sales Territory
    Based off AdventureWorksDW2016 sample DB
    
  • Product
    Based off AdventureWorksDW2016 sample DBYou should now have the following dimensions in the project:
    
• Sales Cube
  time to create a sales cube that will bring the Sample Data and the Relative Periods
  together. 
  • Create a new cube and label it Sales
  • Add Dimensions to cube
    Add all dimensions created previously
  • Mapped Date* needs some special handling, well not so special, simply add DimDate again and rename to Mapped Date.
    Ensure this dimension’s visibility is set to hidden
    * This dimension is responsible for linking all measure based facts using dates to the relative periods

  • Add Measure groups to cube
    Add the following measures groups/facts
    • RP Fact Date Set Date
      add only a count fact and set visibility to hidden
    • RP Fact Comparative Period Date
      add only a count fact and set visibility to hidden
    • Internet Sales
      added from AdventureWorksDW2016
      for simplicity only Sales Amount and Order Count measures are added 
  • The sales cube should now have the following, no MDX needs to be defined.
    
• Dimension Usage – Linking it all together
  It is at this step where all pieces are linked together and the relative period process starts working for all facts.
  • Switch to the Dimension Usage tab
  • Link Product and Sales Territory dimensions to the Internet Sales measure group using ProductKey and SalesTerritoryKey respectively
  • Date dimension (not “mapped date”)
    • Link to “RelativePeriod Fact Date Set Date” & “RelativePeriod Fact Comparative Period Date” measure groups using DateKey.
      
    • Link Date dimension to “Internet Sales” using many to many via “RelativePeriod Fact Comparative Period Date”.
      
  • RP_DateSet
    • Link to “RelativePeriod Fact Date Set Date” using DateSet column.
      
    • Link to “RelativePeriod Fact Comparative Period Date” using Many to Many relationship via “RelativePeriod Fact Date Set Date”
      
    • Link to “Internet Sales” using Many to Many relationship via “RelativePeriod Fact Comparative Period Date”
      
  • RP_ComparativePeriod
    • Link to “RelativePeriod Fact Comparative Period Date” using “Comparative Period” column
      
    • Link to “RelativePeriod Fact Date Set Date” using Many to Many relationship via “RelativePeriod Fact Comparative Period Date”
      
    • Link to “Internet Sales” using Many to Many relationship via “RelativePeriod Fact Comparative Period Date”
      
  • Mapped Date – key date dimension that joins to other facts
    • Link to “RelativePeriod Fact Comparative Period Date” using DateKey and MappedDateKey.
      
    • Link to “RelativePeriod Fact Date Set Date” using Many to Many relationship via “RelativePeriod Fact Comparative Period Date”
      
    • Link to “Internet Sales” using DateKey and OrderDateKey
      
    • Future facts are all to join to the Mapped Date dimension using DateKey

• The final Dimension Usage matrix should look like below:
  

Update Data & Deploy Model 

Modelling is now complete,
Since most of the relative dates configured is in the current period, whilst the AdventureWorksDW2016 database is using periods in 2016 and prior.
To allow testing, you have two options:

1. Update a subset of AdventureWorks database dates to being more recent
2. or Change the RelativePeriods to be in history

Will leave the decision to you, personally I have opted for option 1, allowing me to test relativeperiods using current dates.

Once data is updated, deploy model and ensure it is built and processed successfully.

Connect via Excel & Test

Lets connect via Excel and test if the relative period model actually works or not.

1. Test Comparative Periods in Excel
   1. Bring Dates to rows and Comparative Period dimension to columns
   2. Drag in Internet Sales Amount in measures
   3. The Default node in Comparative Period dimension always returns the measure without any comparative period calculations being applied. Use the Default node to test whether comparative period calculations are working or not.
      See below output:
      
   4. Comparative Periods is working!!!
      Compare last year values to the corresponding prior year values under default and notice how they are matching!
      Similar checks can be done for Last week and yesterday.
   5. You can further test this by adding new Comparative periods in dimensions and facts, such as last 2 days back etc, reprocess the cube (no need to deploy) and these will now simply appear and work.
   6. Similarly, bring DateSet dimension to the filters are and select a DateSet, the dataset in Excel will change to the constant period selected. In my case I selected “Last4CompletedWeeks”,which causes the rows to show the last 4 weeks – notice how the Date dimension on the row has no filters applied and yet is only showing last 4 weeks with data!
      

We now have a completely data driven relative period model, no need to perform complex MDX logic, simply insert the DateSets and/or Comparative Periods in the respective Dimensions and Facts and you have these relative periods working. You can make these relative period as simple or as complex as required – all that is needed is a good handle of SQL and ZERO MDX!

Hope you find this useful.

There are a lot of write-ups on relative periods, and different methods exist for a variety of tools. Interestingly each platform requires special handling, e.g. COGNOS Transformer & TM1 handles relative periods differently to SSAS multidimensional, and again differently to SSAS Tabular, than you have paginated reporting tools such as SSRS, Crystal reports, BO when reading directly off a database, they need another method to handle relative periods, and soon we end up maintaining a few methods to handle the same relative period logic.
Add to that additional complexity if your organisation uses a CUSTOM date calendar, in which case not all of the builtin functions for the platforms may work!!

I have been on a similar journey with my current company, using COGNOS Transformer, SSAS multidimensional, SSAS tabular, SSRS & Power BI, and ended up managing multiple methods of maintaining relative period logic. Each platform requiring something slightly different, and worse, if rolling out new relative period calculations, the rollout process would be lengthy and error prone! Plus the organisation uses a CUSTOM date calendar, so most of the builtin MDX functions like ParallelPeriods are no good :(.

Looking for a better way to manage this, I came up with a data model that I believe could be maintained centrally and accessed by multiple platforms for relative periods .

The design consists of the following:

• Relative Periods modeled in a database (central foundation piece)
  This model will consist of the Date dimension which will mirror the organisations calendar.
• Using the foundation database objects, incorporate these in the relevant tools, e.g. SSAS multidimensional, SSAS tabular, SSRS etc….
• Any relative period changes or addition of new periods are applied to the central foundation database model, and as long as the tools have incorporated these objects correctly, they will all be able to use the new periods easily.

Relative Periods Database Model

The foundation database model for the relative period design

The database model consists of the following tables/views:

• DimDateSet
  a dateset defines the constant relative periods, e.g. Today, Yesterday, Current Week, for these relative periods, there are only a constant set of dates on a daily basis.
  no dates are specified here, only the dateset names.
  Sample contents below:
  
• DimDate
  the traditional date dimension, here you would define the calendar as required by the organisation. The Primary key is the DateKey which is in the format YYYYMMDD
  Note: Except for RelativePeriod Facts, no Fact table should join to this dimension
  Sample contents below:
  
  
• DimMappedDate
  role playing dimension, this is a view based on DimDate and only exposing the DateKey.
  all facts with measures are mapped to this dimension.
• DimComparativePeriod
  relative periods where a comparison is done for a selected date, these are different to datesets, that are constant. e.g. for any given date, show the same day Last week, or same day Last year etc. No dates are specified here, only the comparative period names.
  Sample contents below:
  
• FactDateSetDate
  factless fact that bridges DimDateSet and DimDate. It is in this object, where the Datesets are mapped to the corresponding Datekeys. Since the dates for each dateset will change daily, this object could be a view consisting of multiple UNION select statements (one for each Dateset). Else if performance is essential, than this could be stored as a table (you will need to have a refresh process to update this daily)
  Sample contents below:
  
  
• FactComparativePeriodDate
  factless fact table bridges DimDate, DimComparativePeriod & DimMappedDate.
  for each comparativeperiod, all datekeys from DimDate are mapped to a corresponding MappedDateKey,
  e.g. for comparative period Last Week, all dates from the DateDimension are mapped to the corresponding date 7 days back.
  Sample contents below:
  
  

These database objects are related as below:

The foundational database model for the relative period design

With the above database model in place, you can now use that in any reporting platform to build your relative periods as needed.
My next post will walk through how to use this in SSAS dimensional & tabular models.

This week, I took on optimising a cube report which I had authored recently. This was a not too complex burst report working off an SSAS 2008 cube. The report was taking around 1 minute per burst, with around 500 bursts required = 500 minutes (over 8 hours).

The issue in my report was the use of Detail and Summary filters. Whilst they do work, the effect they have on an OLAP data source is significant!

Inspecting the MDX generated by Report Studio, showed lots of UNIONS, AND CROSS JOINS. What was occurring – Report Studio was performing full cross joins and then applying the detail and summary filters to the returned record set.
In this case, the cross joins resulted in a record set that could easily surpass 100 million records!!! Even though the end result was correct, this was adding a significant overhead to the report.

Remember; COGNOS firstly performs a cross join between all query members and then applies the summary and detail filters

How to optimise this – SIMPLE, reduce the resultant cross join result set, which would require the use dimensional functions within the Query members, e.g.
previously whilst I had the entire member level, now I had
FILTER ( {set}, {condition})

using the above, I was able to limit my query members to the select few records, which also meant my record sets were significantly smaller.
In the end, I was able to totally remove the Detail and Summary filters and the bursting now takes around 5 seconds each!! not a bad outcome, from over 8 hours to 40 minutes!

As a simple rule, avoid using Detail and Summary filters on dimensional reports.

My name is Dharmesh Kalyan, and I am Data Professional loving all things Data and Business Intelligence. Have been working with Data for a number of years from small analytical databases to large scale Data Lakes and Data warehouses supporting large scale Global Analytical solutions.
SQL is what I work with and love.
Technologies I love – SQL, Analysis Services, MDX, Power BI, DAX, SQL Server & Snowflake.

Some key things I follow in my professional life:

• listen more and speak last
• KISS – Keep it simple stupid
  the simple things in life are often the best , this also applies to technology solutions!
• It always seems impossible until it is done – Nelson Mandela
• Never start any work until you know what you trying to solve
  ….and never write a single line of code until you have a design / data flow 
• WE is better then I or ME

Residing in Brisbane Australia, and been in the Data industry for over 25 years.

I am currently employed as Group Head of Data at Domino’s Pizza Enterprises, but the statements and views expressed here are mine alone.

Feel free to connect with me on LinkedIn