New Time Dimension Role after cube wizard

If I have a data source view consisting of two tables - a fact table and a time dimension table. The fact table has three date columns - transaction begin date, transaction end date, and updated date. When I initially designed the data source view my requirements only called for the trans begin and end dates so I made two joins to the time table from the sales table. I then ran the cube wizard which correctly detected that I intended to use the time table twice for two different Time hierarchies (Roles) on a single time dimension.

I have recently been asked to add the updated date but I cannot figure out how to add a new hierarchy (Role) on the time dimension. Any Ideas?

You should be able to go to the Dimension Usage tab in the cube designer and click on the Add Cube Dimension button on the toolbar (the third button from the left). When the list of dimensions comes up, simply select your Time dimension and add it again. You'll then see the dimension added within the list of dimensions down the left side of the tab, likely with a name like Time (Time 1) or something similar. Now, simply highlight the newly added role-playing dimension, hit F2, and rename it whatever you want. Then, of course, set the appropriate relationship between this new dimension and the measure group(s) within the cube...

HTH,

Dave Fackler

sql

new fact records

Hi there

Is there functionality in SQL 2005 to update only new records, e.g. only records from yesterday. I've seen functionality for dimension tables to only get new records but nothing for fact tables.

Thanks

Not altogether sure I understand the requirement but I'll try.

Once you have your records that need to be updated (by whatever means you use to do that), have a LOOKUP component that checks the fact table to see which of those rows were loaded yesterday (or whenever).

-Jamie

|||

Hi Jamie

Thanks for the reply, I want to do an iterative update of my datawarehouse add new records only rather than deleting the table and reimporting the entire table. I have read about variables etc and I thought there might be a way to query the data warehose to check the most recent record then use that to update my sql query which brings in the data. By the way I'm using ODBC connection for this.

Cheers,

Kiwi

|||

Read this and try method 2:

http://www.sqlis.com/default.aspx?311

-Jamie

|||Thanks very much I'll have a crack and let you know how I go