Microsoft Office Tutorials and References
In Depth Information
Remember that after data is loaded in PowerPivot, there are no differences between SQL
Server, Access, or any other database we use to import data; all PowerPivot tables can relate
to one another and be used together.
Loading from Analysis Services
In the preceding sections, you learned how to load data from relational databases. Different
relational data sources might have some slight difference among them but the overall logic
of importing from a relational database remains the same. PowerPivot allows you to load
data from other data sources, which are completely different kinds of databases. So we are
now going to describe the Microsoft SQL Server Analysis Services data source, which has
some unique features, as we are about to discover.
It might seem strange to have the option to load data from Analysis Services, whose main
scope is the construction of pivot tables, into PowerPivot, whose scope is to build pivot tables!
You might wonder why you ever need to load data from an Analysis Services database when
you already can create pivot tables directly from the original source.
Once again, the reason is data integration. Even if you can easily create pivot tables on an
Analysis Services OLAP cube, it is not easy at all to integrate different information in the same
pivot table. With PowerPivot, you can load data coming from the Analysis Services database,
integrate this information with other tables coming from other databases (including your
personal Excel workbooks), and then use PowerPivot to create new and interesting pivot
tables, as you will do in later chapters.
To connect to Analysis Services, you need to select the From Analysis Services Or PowerPivot
option of the From Database button in the PowerPivot window. The Table Import Wizard opens
again (see Figure 5-17), and you need to provide the server name and the database to which
you want to connect. This time, you also need to point to a valid Analysis Services database.
Clicking Next in this first window brings you to the MDX query editor. The MDX editor is similar
to the SQL editor, but this time the language that you need to use to query the database is not
SQL but MDX, which is the query language for OLAP cubes. As with the SQL editor, you do not
need to know the language to build a query: PowerPivot contains an advanced MDX query
designer, which you can open by clicking the Design button.
Note As you might have already noticed, you cannot import tables from an Analysis Services
database; the only way to load data from an Analysis Services database is to write a query. The
reason is very simple: OLAP cubes do not contain tables, so there is no space for a table selection.
OLAP cubes are composed of measure groups and dimensions, and the only way to retrieve data
from there is to create an MDX query that creates a dataset to import.