Date hierarchy - going back to basics

[dean12]

I'm migrating a cube from SQL 2000 to 2005. I find that certain dimensions don't behave as I would expect - specifically elements of the dimension appear in the wrong places. I think this may be due to dimension settings before the migration.

So I'd like to go back to basics and make sure I have things setup properly. The dimension is based upon a table that is a series of dates. It looks like this:

FRM_DOS FmtDate Yr Qt FmtQt Mo FmtMo
20061115 11/15/2006 2006 4 2006/Q4 11 11/2006
20061116 11/16/2006 2006 4 2006/Q4 11 11/2006

The dimension structure is:

Key Name Unique Unique
Name Column Column Key Name
Year Yr Yr Y Y
Quarter FmtQt FmtQt N Y
Month FmtMo FmtMo N Y
Service Date FmtDate FmtDate Y Y

I don't think this is right. I would have set it up
like this:

Key Name Unique Unique
Name Column Column Key Name
Year Yr Yr Y Y
Quarter Qt FmtQt N Y
Month Mo FmtMo N Y
Service Date FRM_DOS FmtDate Y Y

Appreciate any thoughts you might have.

 

[johnherman]

The first thing I notice is that year is marked unique, but it appears twice in your sample data. Thus year is not a unique key.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[dean12]

Yea, I don't think I get a choice. I believe Analysis Services says keys & names are unique at the top level of a hierarchy.

I just tried this arrangement:

Key Name Unique Unique
Name Column Column Key Name
Year Yr Yr Y Y
Quarter Qt FmtQt N N
Month Mo FmtMo N N
Service Date FRM_DOS FmtDate N N

and everything comes out properly when I browse the data. I migrate the cube to SQL2005 using the wizard and after processing the dimension, items are mixed together - meaning I find 2006 quarter entries under 2005.

I'm starting to think the migration wizard is not doing something properly or there are new values I have to set.

 

[johnherman]

When you use the hierarchy Yr down to SvcDt, can you drill properly?

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[dean12]

Sure, drill-down works fine, ProClarity works fine with the dimension. Everything works fine its just wrong. If I browse the dimension and do an Expand All, I see this:

All
... 2001
...... 2001/Q1
......... 02/2001
............ 02/19/2001
...... 2001/Q4
......... 11/2001
............ 11/05/2001
......... 12/2001
............ 12/17/2001
... 2002
...... 2001/Q1
......... 01/2002
............ 01/02/2002
............ 01/07/2002
............ 01/08/2002
......... 02/2001
............ 02/07/2002
............ 02/11/2002
............ 02/12/2002

Exact same dimension works great in SQL 2000 Analysis Services.

I think the migration wizard is screwing things up. Other date dimensions that have this Yr, Qtr, Mo, Date breakdown structure convert fine and browse without error. The migration wizard is doing something different with the other date dimensions, I don't know exactly what yet but it seems to be using some attributes that it creates.

If I open the cube definition up in BIDS and look at the dimension, I see this warning:

Attribute relationships do not exist between one or more levels. The following levels do not have a direct or indirect relationship defined:

Qtr --> Year
Mo ---> Qtr

Of course that begs the question of why the migration wizard doesn't setup whatever is missing. I also see the same message on the other time dimensions that are operating properly so I'm now sure what this warning indicates much less how to fix it.

 

[johnherman]

Is this declared as a date dimension or as an ordinary dimension in SSAS? Maybe you're better off using SvcDt as a date and letting SSAS provide your Mo, Qtr, Yr drills. Just an idea...

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[dean12]

Well, I've read and studied (and cursed) and figured out that the key for the MO level should be YR+MO not just MO and the same for QT (YR+QT). Evidently that is "best practice" anyway.

So this one's solved after 4 days of solid working with AS2005. Then, to add insult to injury, the aggregation wizard refuses to define aggregations. Two days on that problem and I give up. This AS2005 ain't ready for primetime.