Microsoft Office Tutorials and References
In Depth Information
FIguRE 5-24 Average unit price per product and year.
You probably created this table to integrate its information with other tables in the same
PowerPivot workbook. Nevertheless, to relate this table with other ones—for example, by
product—you need a product key. Now the OLAP cube lets you easily load the product
name because this is what people want to see to identify a product. On the other hand, in
the PowerPivot tables, tables tend to use the ProductID as the product key. The ProductID
is not useful in reporting, so cube designers do not normally show this instance of data,
even if they store it in the database for technical reasons.
Note You can include the ProductID in the MDX query, but this requires the ability to manually
change the MDX query code created by the query designer, and after that query has been
modified, the designer cannot be used anymore to change that query. You might ask your IT
department to help you create these MDX queries, if necessary.
All this means that, if you want to create relationships with this table, you need to use the
product name and not the product ID because the product ID is not available. Sometimes
you will find it hard to detect a valid key to create relationships; it all depends on the
availability of good candidates for the key column in OLAP cubes.
Investigate the Presence of a Data Mart
If you can load data from an OLAP cube, it is highly likely that the OLAP cube is fed
from a SQL data mart. Normally, the data mart contains a lot more information than
that made available through the cube, and table identifiers are among the items. Data
marts store identifiers as the internal key even if they are hidden in the final OLAP
If you face such a situation, it is surely worth investigating whether you can load data
directly from the data mart so that you can have access to all the technical information
and make relationships using identifiers.