March 15, 2004 at 1:53 pm
I'm trying to use the Description column from a table that is not the discerning table for a dimension, to use as the dimension's Member Name Column.
I have added the table to the dimension, so it's the Location table, which contains the Description column, and the SaleLocation table, which is a sub-category of a Location. The LocationCode (PK) in the Location table corresponds to the SaleLocationCode (PK) of the SaleLocation table. The dimension diagram shows a relationship between the Location and SaleLocation tables, that I interpret as a JOIN. The problem is that when I try to use dbo.SaleLocation.SaleLocationCode for Member Key Column, and dbo.Location.Description for the Member Name Column, I end up with a Cartesian product of the 2 tables, which leads me to believe that Analysis Services isn't JOINing the tables. If I use dbo.Location.LocationCode for Member Key Column, and dbo.Location.Description for the Member Name Column, I don't get a list "filtered" on the existence of a row in SaleLocation, I get all the Locations.
I realize I could solve this by doing this JOIN in t-sql and putting the rows into a table, but we're trying to avoid that for the moment.
Any help is appreciated,
Rick Todd
March 15, 2004 at 3:05 pm
Rick,
The easiest way to set this up would be a view that joins the two tables. Then query the view within OLAP instead of the table.
cl
Signature is NULL
March 15, 2004 at 3:10 pm
Yeah, inevidetably, I figured that out about five minutes after I posted. It also appears that if I get the hierarchy right, with the Location table below the SaleLocation table, eventhough it seems counter-intuitive because it has more members, it works.
Rick Todd
March 17, 2004 at 8:40 am
I have not use Analysis Services, but I have designed a number of data warehouses in other dbs.
A dimension table is highly denormalized and should not require any joins to external data.
dtbl_StoreLocations
StoreLocationID
LocationID
LocationName
StoreName
This would be used by a fact table, for example, daily sales. With this design, you do not need external joins. Like what was suggested before, you can simulate this in a view that joins the Location and store location tables.
March 17, 2004 at 4:20 pm
I agree with raibeart to a point, I would try to use star schema as much as possible but there are certain times where a snowflake helps with changing dims.
Keep in mind that joins in the cube only slow down your processing, not your cube speed.
------------
Ray Higdon MCSE, MCDBA, CCNA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply