How to structure dimension....?

I have a standard employee-manager tree that I want to use in a dimension. the underlying table has [id] and [parent-id] columns that I've used to create a parent-child relationship in the dimension (via the Usage = /Parent/ field)

Because there are so many top-level members in the tree, I want to create a level above it consisting of the starting letter for manager's last names. This would allow users to select B and see Badden, Browne, Bronson, etc. after which the user could click on Browne and see his employees: Alder, Carson, Domino, etc.

what is the best way of implementing this? I've tried a number of ways without success.including creating a hierarchy consisting of the [Group] (the column witht he first letter of the last name) in the first level and the [parent-id] (Usage = /Parent/, where the the [id]'s NameColumn = /Last Name/) but I can't build it as it pukes with the error:

"Error 1 Dimension 'OrgChart' > Hierarchy 'Hierarchy' > Level 'ParentID' : The source attribute of the level is marked as 'Parent'. 0 0 "

can anyone help?

You cannot mix and match other attributes in with a parent child hierarchy.

The only way I know of to implement this is to add the records for the A-Z members to your dimension table and link them up appropriately in there. If you did not want to mix these records in with "real" employees you could create a separate table and link it in with a union query in a view or a named query in the DSV.

How to start with historization of dimensions!?

Hi there, I've got a question regarding the historization of dimension tables. How do I approach it at best? Let's say I've got 4 dimensions I want to apply historization to (article, customer, agent, forwarding). What is the starting point in Analysis Services?

Things that aren't clear to me:

1. If I use snapshot for historization I think all of the tables need to have 3 additional columns added (at the moment I've just got data-table without time stamps). Let's take an exemplary look at the article table:

Code Snippet

artno artgroup comgroup brand description

4711 Orange Juice Fresh TropicalWonder

So, If I am correct I need to add these rows in order to guarantee a historization:

Code Snippet

artno valid from valid to last refresh
4711 2005-01-01 2005-12-31 2006-01-01
4711 2006-01-01 2099-12-31 2006-01-01

2. If this is correct, my second problem is, how do generate these informations?

3. And last but not least, how does a cube draw a distinction between the two values? As the fact table is just connected to the dimension via the artno...

Thanks in advance!

Hello! These are my comments:

1. Correct, but you are missing a technical/surrogate key which is an simple integer. You can create one column in the dimension table like:artkey int indentity(1,1) .

2. You do this in the ETL process, that is in SSIS(Integration services)

3. The fact table will connect to the dimension table with the surrogate key, not the source key. You will find the valid surrogate key by using the time stamps in the dimension table and compare them with the time stamp of the fact record in SSIS. In the cube you will have a problem with fragmentation of the information.

On my blog(http://thomasianalytics.spaces.live.com/) I have some comments regarding pros and cons with slowly changing dimension. Look for "Slowly changing dimensions or avoid that problem" . You can read a good comment by Tim Petersson

HTH

Thomas Ivarsson

|||Thanks, great post!!!