SSAS - How to connect a Time Dimension to a DateTime attribute?

1
votes

I created a Time Dimension by using SSAS Dimension Wizard. It works well, I can browse it easily.

enter image description here

However, I would like to connect this dimension to a "createdAt" attribute of type DateTime from my fact table.

This is an extract of my FactTable enter image description here

Then, I linked the dimension to the fact table : enter image description here

I don't understand how to process to connect the new dimension table to the attribute. Each time I process my cube I get this error :

Erreurs dans le moteur de stockage OLAP : Clé d'attribut introuvable lors du traitement de : Table : 'dbo_Sale', Colonne : 'createdAt', Valeur : '01/01/2014 08:58:17'. L'attribut est 'Date'.

It seems an Attribute Key is missing during the process. Any idea ? Should I convert type createdAt attribute to another format ?

2
sqlsql-server-2012ssasolap-cube

2 Answers

2
votes

I would truncate the time portion of your createdAt column, eg by using a double CAST function e.g.

CAST ( CAST ( createdAt AS DATE ) AS DATETIME ) AS createdAt

Then the values should match your Dimension Keys.

2
votes

  1. In the data source view, right click the table with 'createdAt' column and click "New Named Calculation".

  2. Give your column a name. I will call it here createdAtDate. Add the following in the Expression:

    cast(createdAt as date)

  3. In the dimension usage, set the relationship as Regular and use the new defined calculated column createdAtDate instead of createdAt.