BI Accelerator SetCurrentDay Problem

I'm having a problem with the SetCurrentDay DTS package
of the BI Accelerator.

I'm loading a set of data using text files therefore I'm using
Master_Import.dts and Master_Update.dts unchanged. Then I run the VBScripts that are alsgo generated by Accelerator (VAMPanorama1_Load_VAMPanorama.vbs).

This loads the data from text files into the Staging Database and SM Database, and also runs the Set Current Day package with a fixed date of "2001-12-31".

The problem is data for the year of the Current Date is multiplied by a factor of six. Now, when I run SetCurrentDay Package Alone, to try to set a new date (say "2003-07-01") either opening the package in Enterprise Manager and changing the global variable, or using DTSRUN utility I get the same problem. The data es multiplied by a factor of 6 for the year 2003 and data for 2002 and 2001 shows up correctly at the year, qtr and month level.

I tried this on the Retail Analysis sample project that comes with Accelerator and had exactly the same problem.

Has anoyone seen something like this? am I doing something wrong here?

Any help is appreciated,I assume you've done something else by now, but I had the same problem. After verifying that the proper number of facts were loaded, and that a join wasn't creating additional records, I stumbled on the solution: There is no problem with the cube.

The problem is that the auto-generated base time dimensions each have an 'All' level and they should not. Setting all of them (for me they are Time.Standard and Week.Standard) to 'Current' will fix the problem and show the proper number of transactions. The reason they are multiplied is that 'All' aggregates across all time periods including the actual periods and the calculated ones like Current, Last Year, Change from Last, etc.

Originally posted by rsada
I'm having a problem with the SetCurrentDay DTS package
of the BI Accelerator.

I'm loading a set of data using text files therefore I'm using
Master_Import.dts and Master_Update.dts unchanged. Then I run the VBScripts that are alsgo generated by Accelerator (VAMPanorama1_Load_VAMPanorama.vbs).

This loads the data from text files into the Staging Database and SM Database, and also runs the Set Current Day package with a fixed date of "2001-12-31".

The problem is data for the year of the Current Date is multiplied by a factor of six. Now, when I run SetCurrentDay Package Alone, to try to set a new date (say "2003-07-01") either opening the package in Enterprise Manager and changing the global variable, or using DTSRUN utility I get the same problem. The data es multiplied by a factor of 6 for the year 2003 and data for 2002 and 2001 shows up correctly at the year, qtr and month level.

I tried this on the Retail Analysis sample project that comes with Accelerator and had exactly the same problem.

Has anoyone seen something like this? am I doing something wrong here?

Any help is appreciated,|||It's been some time since I last saw this thing. But could you refresh my memery
What do you mean by "Setting all of them (for me they are Time.Standard and Week.Standard) to 'Current'"? . Is this changing the name of the All Member or removing the All member ?

thanks|||It's actually constraining the dates to 'Current' but I found the real source of the problem.

The auto-generated date dimensions have custom members built into the dimension table, and one of them is called 'Current'. My original recommendation was that you could not leave the date dimensions set to 'All' (meaning that they are unconstrained) because that caused the totals to be much higher than they should be. Setting them to 'Current' or to some other specific value (a particular year, month, etc.) would fix the totals.

Here's the real problem: the unary operator property is not set by the spreadsheet generator. If you go into Analysis Manager and edit the Time.Standard dimension, you can fix it. For each level, look at the advanced properties and click the ellipse button beside the value for Unary Operators. A dialog comes up that lets you check "Enable Unary Operators" and tell it there is an existing field of <level name>_Oper (e.g. Year_Oper). This one change (setting it for all levels) fixes all of the auto-generated date dimensions.

What's happening is that without the unary operator, calculated dates like Current, Previous, Change, %Change, etc. are treated just like any other year value (like 2004) so the cube adds together all of the values causing data to be added both for the actual date that it occurs, and to the calculated dates (current, etc.). The unary operator lets the value in <level name>_Oper determine if the values will aggregate or not, and thankfully, the attribute has the right values.|||Thanks a lot Barclay I got it workin fine now..!! :)