date Hierarchy (simple hopefully)

Question

date Hierarchy (simple hopefully)

erics44

SSCertifiable

Points: 6150
More actions
June 29, 2016 at 8:22 am

#314678

Hi
I'm new to SSAS.
I have a dimension table with multiple date fields and I would like to add a hierarchy to each of the fields so I can view the data by month year etc.
do I need to add multiple date dimensions to achieve this?
thanks in advance

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Nope, just one will do! Each date dimension attribute becomes it's own hierarchy by default, but you can create custom hierarchies in the dimension designer. Just drag & drop the columns you want into the hierarchy space. This will allow you to drill into a hierarchy from the client side. You'll also want to set up attribute relationships on those columns. Here's an example:

https://www.mssqltips.com/sqlservertip/3414/sql-server-analysis-services-attribute-relationships/

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

erics44 SSCertifiable Points: 6150 More actions · Answer 2

thanks for the reply

how do I link each for the date fields to the date dimension?

erics44 SSCertifiable Points: 6150 More actions · Answer 3

or do I add multiple fields to the table

so for example tblApplication has the fields

ApplicationDate

ApplicationMonth

ApplicationYear

and I create a hierarchy using the existing fields?

John Rowan SSC Guru Points: 56440 More actions · Answer 4

I would create your date dimension with an integer key (clustered primary index) with the integer date value for that day. For example, today's key value would be 20160629. There are some date dimension build scripts out there in the internet if you search for them. Then, in your related tables, include the same integer date value for relating the tables. If you can't add this to the table, just add it into the table in SSAS by replacing your tblApplication with a named query in your data source view. You can use the existing date value in your table to derive the integer date value to link your tables in the DSV.

My preference would be to model this into your physical table though. That way, it's easier to query the data mart and get the same values as what your cube pulls up.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

erics44 SSCertifiable Points: 6150 More actions · Answer 5

I create a key on the table dimdate and arrange the dates on the tblApplication with the format/datatype and then create a relationship in the cube designer?

John Rowan SSC Guru Points: 56440 More actions · Answer 6

Yes. If you can do this on the physical tables, great. Otherwise, use a Named Query in the DSV to create these columns.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Luis Cazares SSC Guru Points: 183698 More actions · Answer 7

You can keep your dates as dates using the date data type. It's easier to manage and requires 1 byte less of storage per row. 😉

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

erics44 SSCertifiable Points: 6150 More actions · Answer 8

erics44

SSCertifiable

Points: 6150

June 30, 2016 at 8:01 am

#1887884

thanks for the replys 🙂