Microsoft Office Tutorials and References
In Depth Information
If you now click Finish, PowerPivot begins loading the data. Nevertheless, if you look at the
imported table, you discover that some work is yet to be done. Let us look at Figure 5-23,
where you can see the imported table.
FIguRE 5-23 The table created from an MDX query has some pretty long column names.
You can see that the names chosen by PowerPivot for the columns are far from being user
friendly. PowerPivot uses a pattern to assign column names that includes the name of the
dimension, the hierarchy, and finally the attribute, all concatenated. This leads to some very
funny names, such as SalesTerritorySalesTerritoryCountrySalesTerritoryCountry, something
that you will surely rename Country in the PowerPivot window.
There is no way to define friendly names in the MDX editor, so you just need to remember to
create some order after loading the table. Furthermore, if you refresh the table later, the
columns retain the correct new names, so this work needs to be done only once.
The last step you need to take to have a clean table is to review the data types of the imported
columns. The Analysis Services data source loads all the data as strings, which might not be
the correct representation. To correct the data types, you just need to use the Data Type box
in the PowerPivot window, and in case of any error, correct it by setting the right type for
each column. This is particularly important for all the numeric measures, which are not
aggregated unless you change their type to the correct numeric type in PowerPivot.
Handling of Keys in the OLAP Cube
You have seen that by using the MDX editor, you can easily load data from Analysis Services.
Nevertheless, when loading data from an OLAP cube, you need to face a subtle problem, which
we are going to describe now. OLAP cube programmers often design the cube structure so
that it is easy for people to query. So the programmers tend to expose textual attributes that
are easy to understand from a human point of view and hide all the more technical attributes,
which are not useful when you are pivoting data. In other words, they follow the same process
of hiding technical columns that you learned in Chapter 2. This data model is well suited
for pivoting data but it is seldom the best one for further processing, as you want to do
An example will surely help to clarify this concept. If, for example, you want to load the
average unit price per year for each product, you can easily create a query that returns a
table like the one shown in Figure 5-24.