Hello all,

I am completly baffled with the following problem, and I hope someone here
can help me:

As a "learning" exercise, I am re-creating our AS database in AS2005. Our
current one is in AS2000, and has been (and continues to) work fine.

My problem is when I try to make a "Products" dimension.

The structure of the underlying dimension table is as follows:
ProductID
CategoryID
ParentCategoryID
Description

The fact table links to this table, via the ProductID.
The dimension is a tree of the categories with the products as leaf nodes.
Creating this in AS2000 was simple. I just specified a parent child
relationship, selected the parent, and children, and it worked fine.

In AS2005 I can't figure it out. If I tell the Dimension wizard the the
"CategoryID" is key, it will let me create a parent child, but of course,
this key isn't in the fact table, and that will bring back bad results. If I
pick the "ProductID" as a key, the parent will try to reference back to a
key that is not related to it, and if I pick both as keys, I don't have the
option of creating a parent child relationship in the wizard.

I really hope someone out there can help me out.
Thanks,
Ryan

Hi Ryan,

Here's one idea (haven't tried it out yet, but..):

1) In the Data Source View, change the fact table to a named query,
which joins the fact table with the Products dimension table, to derive
a CategoryID field, like:
"select fact.*, prod.CategoryID
from fact
join prod on fact.ProductID = prod.ProductID"

2) Connect the derived CategoryID fact field to the CategoryID field in
the Products dimension table

3) Now you could use the CategoryID as the key for the Products
dimension, with a parent/child relation.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hi Deepak,

Thank-you for your response. I have already tried something simular.
Although I was able to get it working using this type of approach,
unfortunatly, the behavour of the cube is slightly different now, than how
the original was.

In any event, I have now, gotten it to work. However, the approach I took
was not the easiest.
What I ended up doing , was editing the dimension and cube XML files with
notepad, to define the relations I needed. After building, processing, and
deploying the solution, I have confirmed that the results match that of the
AS2000 version.

If I tried to duplicate what I did in visual studio, the options either do
not exist, or the fields that needed to be changed were non editable.

Anyway, thanks again for the suggestion. If anyone else out there knows an
easier way to do this, I would appreciate it. It seems that having to
manually edit XML files isn't the best way to go about these types of tasks.

Thanks,
-Ryan




"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

Hi Ryan,

When you have time, could you post the names of the XML
elements/attributes that you had to edit - maybe someone will be able to
suggest a way to modify them via BIDS; or they'll be candidates for
future BIDS enhancements?


Thanks,
- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hi Ryan,

Are all the ProductId's having the same CategoryID have the same
ParentCategoryID?

If it is right, I would be create 2 dimension.

1. Flat for Product
2. ParentChild for Product group.

Than you could attach 2. Dimention to FactTable over 1. Dimension. Both
2000 and 2005 support it.

Thanks, Vladimir Chtepa

"Ryan Z" <ryanz (AT) remove (DOT) iqmetrix.this.com> schrieb im Newsbeitrag
news:%235pbzEiFGHA.3532 (AT) TK2MSFTNGP14 (DOT) phx.gbl...

Hi Deepak,

The file that needed to be edited was the Sales.cube (Just the cube file)
file.
Inside that file, I found the xml description for that dimension. Inside
that, I had to change the tableID, and ColumnID fields to point to the
hierarchy in the dimension table.


To respond to the other poster, I was able to get it working like you
suggested. However, we wanted to keep the design the same as as2000 because
we will be having to support both for some time.
Thanks everyone for their responses.
-Ryan


"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

Hello,

I am dealing with the same problem at the moment, is there really no easier
possibility than changing the view or dimension structure? Shouldn't it be
possible to define it with the wizard or dimension editor?

I hope that there is another solution because otherwhise it will be very
uncomfortable to change a whole datawarehouse...

Greetings,
Roberta

"Ryan Z" wrote: