Creating a dynamic Date Table in SSAS Multidimensional

[Anonymous]

Hi,

I found a lot of stuff on how to create a dynamic DateTable ("dynamic" includes a dynamic date range from e.g. 2 years ago till next year as well as offsets from the current date, month, year).

Most of this is done in DAX which is not available in SQL Server Analysis Services Multidimensional Cube. The connection to PowerBI is "live" i.e. I cannot add a date table in Power BI.

I have to prepare several cubes each in a single database, so I'd preferably do this in SQL since all cubes share the same SQL database and I don't want to duplicate the DateTable stuff if possible. The cubes are refreshed once a day and refresh time is not an issue.

Thanks

Fabian

[ibarrau]

Hi. I didn't completely understand if you prefer the table on dax or sql. Anyway I hope this link with the code for both of the languages helps you.

https://blog.ladataweb.com.ar/post/185012464051/data-modeling-como-hago-una-tabla-fecha

Regards,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @ibarrau ,

As far as i know there is no DAX in SSAS. There is MDX für measures.

Also if I do it in the cube, I have to prepare the date table for every of the 10 Cube projects. This means updates have to go into ten places.

And I cannot use DAX in Power BI either (live connection to SSAS => no DAX).

The link you provided is not suitable since it's table based not view based (I need a fresh date table every day).

[ibarrau]

The SQL table in the link is for a store procedure that can run everytime you want it to be refreshed. You can take the idea and adapt it in a view if that is exactly what you are looking for.

I know MDX is not DAX, but it's pretty similar, you should try it.

I'm not sure what you mean with dynamic data table. This is not a valid concept unless you have time involved because updating it once a day will be just enough to have the date table updated.

Regards,

[Anonymous]

@ibarrau 

After some tinkering I found a solution involving only a view.

Basically I conjure first a plain date table containing only dates with a dynamic range (e.g. this year +-2 years) and then beef it up with other fields like a difference to today, month, month and year etc.