Dimension Hierarchy members processed incorrectly

I have an interesting problem with Analysis Services that I was able to resolve (sort of), but I would appreciate some feedback to see if this is known behavior, a bug, or if I'm just setting up my dimension incorrectly.

Basically, the intermediate members of a hierarchy are showing up incorrectly in my date dimension. For example, when I browse the dimension, I'll end up with a tree that looks like this:

All CY
- CY 1998
- Qtr 1 CY 1998
- January 1998
- Jan 1, 1998
- Jan 2, 1998
- etc.
- .....
- .....
- CY 2005
- Qtr 1 CY 1998
- January 1998
- Jan 1, 2005
- Jan 2, 2005
- etc.

As you can see, the years are correct and the lowest element (date) is correct, but the names of the intermediate elements (quarter and month) are incorrect. These intermediate elements are the same for each calendar year (i.e. it's always 1998, which is the earliest year we have data for in the table).

I've included the table/hierarchy structure below for reference.

So the way that I (sort of) resolved the issue was by using the calculated name values as the keys for the attributes instead of using the integer-based keys that I was using. It seems that you can't/shouldn't have key values that repeat for different higher-level elements (i.e. the month_si value of "12" (December) is present in the table for rows from CY 1998, 1999, 2000, etc.). I'm guessing here, but it seems that when it went to build the hierarchy underneath, say, CY 2006, it found key values of "1","2","3", & "4" for [quarter_si] (correctly so). It then pulled the value from [Column15] for those key values, but instead of limiting itself to data underneath CY 2006, it looked at the whole table, and thus pulled the name values for those quarters from the first records in the table, which happened to be "Qtr 1 CY 1998","Qtr 2 CY 1998" and so on. But since "Qtr 1 CY 2006" is only present in CY 2006, using "Qtr 1 CY 2006" as the key and name value gave me the correct hierarchy.

Unfortunately, this resolution doesn't work because the keys won't order correctly (i.e. "June 2006" comes before "May 2006" under "Qtr 2 CY 2006") and this is a show-stopper.

What's odd is that I converted this dimension directly from SSAS 2000, and this was not a problem we encountered in 2000. Is this issue resulting from something new introduced in SSAS 2005? Is there a property flag that controls this behavior?

Or do I just not have a good understanding of how dimensions work? ;)

I would appreciate anyone's feedback or comments.

Thanks in advance,
Jamie C.

TABLE/DIMENSION/HIERARCHY INFO:

The underlying table used for the dimension is "dbo.dci_Date" - the structure of the table (including calculated columns within AS) is this:

[dbo].[dci_Date]
[date_id_si] [smallint] NOT NULL,
[year_si] [smallint] NOT NULL,
[quarter_si] [smallint] NOT NULL,
[month_si] [smallint] NOT NULL,
[date_sd] [smalldatetime] NULL,

The following "virtual" columns are also present as calculated columns - I've included the formulas for reference:

[Column4] [WChar] = left("date_sd",11)
[Column13] [WChar] = 'CY ' + convert(char,DatePart(year,"date_sd"))
[Column15] [WChar] = 'Qtr ' + convert(CHAR, DatePart(quarter,"date_sd")) + 'CY ' + convert(char,DatePart(year,"date_sd"))
[Column17] [WChar] = convert(CHAR, DateName(month,"date_sd")) + convert(char,DatePart(year,"date_sd"))

Here's an example of what a couple of rows from the table look like (including the values for the calculated columns) - I've included the min & max as well:

[date_id_si],[year_si],[quarter_si],[month_si],[date_sd],[Column4],[Column13],[Column15],[Column17]
-729,1998,1,1,1998-01-01 00:00:00Z,Jan 1 1998,CY 1998,Qtr 1 CY 1998,January 1998 ** MIN
2495,2006,4,10,2006-10-30 00:00:00Z,Oct 30 2006,CY 2006,Qtr 4 CY 2006,October 2006
2496,2006,4,10,2006-10-31 00:00:00Z,Oct 31 2006,CY 2006,Qtr 4 CY 2006,October 2006
2497,2006,4,11,2006-11-01 00:00:00Z,Nov 1 2006,CY 2006,Qtr 4 CY 2006,November 2006
4270,2011,3,9,2011-09-09 00:00:00Z,Sep 9 2011,CY 2011,Qtr 3 CY 2011,September 2011 ** MAX

The hierarchy for this dimension (Calendar Year) is built with the following attributes:

Year -- key: [year_si] name: [Column13]
Quarter -- key: [quarter_si] name: [Column15]
Month -- key: [month_si] name: [Column17]
Day -- key: [Column4] name: [Column4]

Hello. You must assure that each attribute in a dimensions has an unique key. Like month, if you represent this with a month number like 1 to 12, this is not unique over years. To solve this problem you simply go to the key property of the attribute and change that to a collection by adding each year to column. By clicking to button to the right for the key column you will see the "Data Item Collection editor".

I would recommed to use numbers or collections of numbers as keys for year, quarters and months. Change the name column to something for informative instead, like a text based description. Check that the attribute is ordered by key, not by name in the properties for each attribute.

HTH

Thomas Ivarsson

|||

Thanks Thomas! I had a feeling that was the case, but it never hurts to be sure.

Best,

Jamie C.

Different execution plan between stored proc and ad-hoc query

We have just encountered a problem with a particular stored procedure which I would appreciate anyone's thoughts on.

The proc in question contains a single select statement which simply joins four tables and filters based on two parameters. It normally runs in well under a second but this morning we came in to find it was running in anything up to a minute.

After investigation we found that it was running a clustered index scan on one of the larger tables (approx 10.5m rows) rather than using the appropriate index. However, when we took the select statement out and ran it as an ad-hoc query it was utilising the index and performing correctly.

We tried recompiling the proc and also ran sp_updatestats on the database but we were still getting the different execution plans being generated. It was only after we ran UPDATE STATISTICS WITH FULLSCAN on the table in question that the proc went back to performing normally.

Can anyone shed any light on why the proc and query were using different execution plans, even after recompiling the proc? The table is heavily utilised in all areas of the system so I could understand if the stats got out of date (although autostats is on and sp_updatestats does get run on the database 2 or 3 times a day through an automated process).

Also, this is the second week in a row where this behaviour has occured so we need to try and mitigate the risks next week. Obviously we could schedule a stats update with fullscan early Monday morning but we would like to understand what is causing this and try and fix it "properly".

cheers

James

If the system is heavily updated, the stats could get stale. Consider running [sp_updatestats 'resample'] or explicitly forcing an index (the latter is not really recommended unless you fully understand the consequences).

e.g.

select * from tb with (index(myindex))

|||

It's very quite possible to get two different plans. Take for example these two TSQL statements:

a)

declare @.x int

set @.x = 99

select * from t1 where col1 = @.x

b)

select * from t1 where col1=99

In a), the optimizer evalutes the whole batch, and cannot evalute the literal value of 99 since it's a parameter value set inside a batch. So it makes a guesstimate and optimizes for a given value which it thinks may be the most correct/optimal. Often times it is, often times it is not. In b), the statement is evaluated and immediately it knows the value of 99, so it can get a very accurate estimate, thus producing a more reliable plan.

The same holds true for stored procedures. If you create a stored procedure and pass in a parameter value used in your WHERE clause, the optimizer will most likely produce a far better estimate than if you created a procedure which declared variables, set them, and then used them in your WHERE clause. It's always beneficial in a proc to try not to declare variables, set them and then use them in a query. If you can pass them as a parameter, the query has a better chance of producing an optimal plan based on accurate estimates.

Where you can also get into trouble is if the plan is cached with a value that gave good estimates/performance at the time it was created, but then as time passes and changes occur to the tables, yes the stats can get out of date, and you'll need to either update stats or clear the cache to remove the stale stats/plan.

If you got lost with what i was trying to say above, forgive me, I'm sure it's documented in some whitepaper somewhere, but I know this is an issue as merge replication procs and triggers hit this kind of problem often.

|||

Thank you both for your comments. Greg, that makes sense and does explain why we may have been seeing the different plan.

This problem actually appeared again yesterday, although this time the ad-hoc query was using the same (flawed) plan as the proc. We have decided in this instance to use an index hint as there are really no circumstances where it should be doing a full scan. As OJ says, I know these are not really recommended but I think I'm happy for this to be one of the rare expections!

cheers

James

|||Which version of SQL Server are you using? If 2005 then you might opt for the "Optimize For" hint or "Option Recompile". When using an index hint, you run the risk of accidentally breaking code later if that index is dropped or changed to include different columns.