March 24, 2008 at 2:45 pm
Hi, I am new to SSAS and SSIS, and I have question regarding cube building.
I created one fact table and 3 dimension tables. When I created a relationship between fact and dimension table, I used surrogate keys.
When you are populating tables, how would you assign a correct surroage keys in fact table?
For example, I have Sales Fact table and Geography Dimension table.
In Sales fact table, I have Geography Key. Let say item was sold in Los Angeles, CA and Surroage key for Los Angeles CA is 1.
When you loading a fact table data, how would you make your entries to have correct sales region value (correct surroage key)?
March 27, 2008 at 12:56 pm
Well, you "look up" them through your code or SSIS task. Something like this:
Update A
Set A.geo_FK = B.geo_PK
from fact_sales A Join dim_Geo B
on A.City = B.City and A.State = B.State
--and in this case
Where A.city ='LA' and A.State ='CA'
May 1, 2008 at 9:00 am
You can add a lookup data flow task in your SSIS package. I'm a rookie at this, so I only use the visual designer. 😀
Click your control flow task, then go to the data flow page. On the toolbox, in the transformation area, you'll find lookup. drag the green arrow of your source to the lookup, and the source columns should become available.
It's pretty self explanatory from there, you link the original column in your incoming data to your dimension table, and use the dimension's surrogate key as the output column. That's assuming that you have both the surrogate and the original key values in your dimension table.
May 2, 2008 at 8:53 am
Although a dimension table should have a surrogate key to facilitate the join with the fact table, there should be a field or combination of fields that can't be repeated and have a unique constraint applied. In the example you gave, ideally you would use a zip code, but if city and state is all that's available, you could use that too. In my location dimension table, zip code is unique, and could have been the PK except for the need to facilitate rapid joins. As the work orders come in with their zip, I connect on the two and pull the surrogate key number from the join results.
May 26, 2008 at 7:37 am
hi,
In SSAS is it possible for lookup table, for instance: if the purchase goes above 500 then rating is 5,if 400 then rating 4...the purchase values like 500,400 are in one dimension table. hence the rating are in another dimension table.
Is it possible to make a lookup with these dimension table by providing rating?
If it's possible,then how to implement???
May 27, 2008 at 7:37 am
What you're detailing is certain possible, the details are in the implementation.
I think what you want id:
- You have a "fact" table, with purchases (and otherwise keyed).
- You want to split these results by "Rating", where there's some criteria based on the purchase amount.
Easiest implementation:
- Create a dimension table (Rating) with the values / keys you want to use. Include this in your model.
- Add a column to your fact table. (Either via a view or using a Named Calculation). The value for this column would be something like:
[Code]
Case When [Purchase Amt] < 100 Then 1
When [Purchase Amt] < 200 Then 2
...
Else 0
End
[/Code]
- Join this new column in the DSV. Make sure your dimension usage on the cube is properly hooked up.
Now you'll be able to slice and dice by Rating. (Was this what you were looking for?)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply