Search code examples
sqldatessas

SSAS Date stored as text

I have a measure table for forecast that has a MMM-YY date stored as text;

Period Forecast
-------------------
Jan-20 200
Feb-20 300

I also have some other tables in my model that have similar date formats ie. (1/2020) or 2020_1. Hence I created a date dimension that maps the period to an actual datetime and linked it to the fact table;

Period (Month/Year) Year_Month MonthEnd
---------------------------------------------------
Jan-20 (1/2020) 2020_1 31/01/2020
Feb-20 (2/2020) 2020_2 28/02/2020

This is causing me two issues;

  1. If I slice the forecast by period I get the right answer, but if I slice by the datetime field 'MonthEnd', SSAS can't allocate the costs across the attributes and I get the total each month (so 500 in both jan and feb in this example). Why?

  2. I can't connect time as a referenced dimension to the date dimension so I can't use any time intelligence features.

I could just swap the period ID for a datetime on ETL to standardise the date fields across the model, but I wondered if there was a standard way to approach this?

cube

https://i.sstatic.net/2iyvM.jpg

Solution

  • In Analysis Services Multidimensional models you need to standardize on one format for representing a period and have all measure groups use that. I would recommend you change the SQL Query for your Actuals measure group to return values that join to the Period column in your Date table.

    Understanding how this works means understanding attribute relationships and the IgnoreUnrelatedDimensions setting. If set to true then slicing by an “unrelated” attribute (one that’s below the grain or unrelated or an unrelated dimension) will just cause the measure to repeat. If set to false then it will become null.

    I’m unclear why you need Time as a reference dimension. It appears to also contain a Date hierarchy. Typically Date is for days, weeks, months and years. Typically Time is for hours minutes and seconds. For processing performance reasons I would avoid reference dimensions. They are more trouble than they are worth. Add the Time dimension key to your fact tables.