Dimension from table with two keys

[gleapman]

We are unable to use a table with two fields for the key as a dimension table and have it connect successfully with the measures in a cube.

In our data source view we've added a table from Great Plains with product information. The table uses two fields for the key: productnumber and companyname. We're using a Sales table as our fact table. Each record in the Sales table has a productnumber, but not a companyname. To get to the company name, you have to join two other tables to get to the Customer table to find the customername.

In the data source view, we first tried having a relationship between Product and Sales and another between Product and Customer. Then we tried adding a Named Calculation to the Sales table to bring over the customername from the Customer table, so that both relationships with the keys in the Product table are with the Sales table.

In each case we then created a cube with a Product dimension. But when we drag the Product dimension as a row field, it doesn't connect up with the related data from our measures.

If we eliminate the customername field from the key for the Product table and just use the productnumber (and have just one record for each product number in the table), the cube works fine, connecting the right facts with the right products.

Hope this explanation makes sense. As a newbie, I don't know all the terminology.

Any suggestions appreciated. Thanks.

[Island1]

I did this as recently as a couple of weeks ago with a client here in Atlanta, using similar tables within a Navision environment. It's certainly do-able - if you get everything joined properly (seems a little bit confusing re: the interchangeability in "company" and "customer," in your description, but I may have simply misread the scenario you outline.

Could you detail the situation, say, with table and column names? Also, once you have completed the steps you outline, did you make the associated settings on the Dimension Usage tab of the Cube Designer?

Keep in touch, and Good Luck!

Bill

[gleapman]

Thanks for your reply.

I believe we fixed the problem a few hours ago. It was certainly a newbie mistake on my part. I created two relationships between the tables instead of one relationship listing both key pairs. I haven't tested it fully, but it appears to be working properly.

Bad things happen when a C# coder is assigned a database project!

[Island1]

Newbie or not, you seem to have an eye for resolving issues ... Congrats on getting there.

Keep the forum in general, and myself in particular, in mind for anything you confront in working with the MS BI solution - I have many articles here that show "how to" from a practical standpoint, and am happy to answer questions you have here, as well, as time permits.

Best of luck with your implementation!

Bill