December 17, 2009 at 8:41 am
Quick question,
I have a cube dimension based on a dimension table. It has all the columns I need bar one. That column is available in another dimension table. Is it better practice to add the second dimension to my DSV thereby creating a new cube dimension, or to add a named calculation for the column in my existing cube dimension? There is a FK relationship between the two dimension tables.
The advantage I can see with the first approach is that all the columns of the second dimension will be available when browsing the cube (depending on whether I chose to make them visible in the cube of course).
Thanks
Lempster
Update: please ignore the post - I realise an existing column can't be added as a named calculation.
December 19, 2009 at 6:15 am
If you have a FK relationship between the two tables then it sounds like you have what I would consider a snowflake design possibly. You can pull the table into your DSV, setup the relationship, and then in the existing dimension you have you can add the column from the second dimension table in the dimension designer (might need to add the new table in the DSV window in the designer to see it though since you just added it).
This would be similar to the product dimension in Adventure Works DW database from the CodePlex database samples. You have Product Category, Product SubCategory, and then Product (snowflake design).
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
December 21, 2009 at 1:55 am
Thanks Dan, that's what I decided to do in the end. I have got another question about Named Queries and Named Calculations though:
Is it possible to use more complex logic in the Expression for the Named Query or Named Calculation? I want to add a Postcode Mnemonic column that consists only of the letters in the first part of the postcode, e.g. for a postcode of M60 2RJ it would be 'M' and for a postcode of GL19 7SL it would be 'GL'. Obviously I need to perform some logic on the underlying column to determine whether I have 1 or 2 alphabetic characters, but it doesn't seem possible to do this.
Regards
Lempster
December 21, 2009 at 3:04 am
Ok, I've managed to achieve what I wanted. It doesn't look very elegant, but I can't see another way...
SELECT UserKey, SUBSTRING(Postcode, 1, 2) AS PostCodeMnemonic
FROM Dim_Users
WHERE (ISNUMERIC(SUBSTRING(Postcode, 2, 1)) <> 1) AND (Postcode IS NOT NULL)
UNION
SELECT UserKey, SUBSTRING(Postcode, 1, 1) AS Expr1
FROM Dim_Users AS Dim_Users_1
WHERE (ISNUMERIC(SUBSTRING(Postcode, 2, 1)) = 1) AND (Postcode IS NOT NULL)
December 21, 2009 at 5:06 am
You could also use a CASE statement and evaluate the PostalCode field like the following:
CASE WHEN LEFT(postalcode,2) like '[A-Za-z][A-Za-z]'
THEN LEFT(postalcode,2)
WHEN LEFT(postalcode,1) like '[A-Za-z]'
THEN LEFT(postalcode,1)
ELSE NULL
END
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
December 21, 2009 at 5:30 am
Ah yes, a much more elegant and easier to read solution!
Many thanks Dan.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply