Named Calculation or separate Dimension?

  • 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.

  • 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

  • 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

  • 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)

  • 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

  • Ah yes, a much more elegant and easier to read solution!

    Many thanks Dan.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply