Defining Analysis Services Dimensions - SQL Server Analysis Services 2012 Cube Development - page 31

Creating named calculations and
queries
Named calculations allow extending an existing object to include data structures ne-
cessary for defining a dimension. The most common example is of combining the first
and last name columns into a single column named as full_name . Similarly, we could
concatenate the quarter and year columns to define a full description for each calen-
dar quarter, as in Quarter 2, 2013 . If you have sufficient access to the relational
database, you have an option of creating any views you need for building dimensions;
this approach is favored by many data warehouse and cube developers. However, it
is also plausible that you won't have permission to create or alter relational objects.
There is no need to worry though; named calculations are here to help. For example,
suppose you have the Employee dimension based on the DimEmployee table, which
includes the FirstName and LastName columns. This could be great for relational
design, but you'll need to define a named calculation if you want to expose a string
concatenating the first and last names as a single dimension attribute.
In addition to named calculations, you can also define named queries. These are most
useful when you'd like to present columns from multiple tables together as a single
entity. With named queries you can also join tables found in multiple databases on
the same SQL Server instance. If you have necessary permissions to create and al-
ter objects in the relational source, it's best to define relational views (the relational
database administrator will advise you if you have such permissions); however, if you
cannot modify the relational source, you will have to resort to using named queries.
You should also consider the advantages/drawbacks of using named queries versus
relational views. If the view is beneficial for relational queries in addition to populating
an SSAS object, it makes more sense to create the view in the relational database.
On the other hand, if the view is specific to your Analysis Services solution, it may
make more sense to use a named query as opposed to creating a new relational ob-
ject.
How to do it...
To create the full_name named calculation, perform the following steps: