Blog Home  Home RSS 2.0 Atom 1.0 CDF  
Mark Mrachek - Business Intelligence Blog - Named Calculations
 
 Saturday, January 07, 2006

Named calculations are basically customized columns (or attributes) that you create in data source views in Analysis Services.  The beauty of the named calculation is that you don't have to make changes to your source tables or views.  Instead you create a SQL statement, such as a case statement, to build an attribute locally within your data source view. 

I've used named calculations to concatenate values in two separate columns and I've also used them to create groupings for some dimensions. 

Here is an example: Let's say that you need to create names for products where the id for the product exists in a dimension table, but the product names do not exist.  For instance, your underlying table or view has a ProductID column (with values 1,2,3) and you want to use a product name instead of an id in your dimension.  What we want to do is create a named calculation called ProductName. 

You simply open your data source view in the 'Business Intelligence Development Studio'; right click on the supporting table or view, then click on 'Add Named Calculation'.  A window opens with two textboxes, one is for the name of your named calculation (Product Name), and the other is where you add your SQL Expression.  You can create the named calculation with the following expression:

CASE ProductID
WHEN 1 THEN 'Product A'
WHEN 2 THEN 'Product B'
WHEN 3 THEN 'Product C'
ELSE 'Unknown'
END

You can now add the ProductName named calculation as an attribute to your dimension in the 'Dimension Designer' (on the 'Dimension Structure' tab).  Click on the name of the named calculation (ProductName) in 'Data Source View' pane and drag it to the 'Attributes' pane.

That's it.  You've now created a customized attribute.  Obviously, you could have added the name of the product to the dimension table or view itself, but this example illustrates what is possible with named calculations.

 

1/7/2006 11:52:56 PM (Pacific Standard Time, UTC-08:00)  #    Comments [0]   Analysis Services  | 
Name
E-mail
Home page

Comment (HTML not allowed)  

Enter the code shown (prevents robots):

Copyright © 2010 Mark Mrachek. All rights reserved.
DasBlog 'Portal' theme by Johnny Hughes.