When we use Power Pivot reports often happens that we’ll use two or more reporting dimensions, hierarchically arranged, to provide organized access to information. If something like this occurs frequently we can use specific functionality of Power Pivot reports: Hierarchy. Hierarchy is a named set of hierarchically arranged reporting dimensions, and more about ways for creating and using them you’ll find out if you read this recipe.
Power Pivot is an interactive tabular report, similar to traditional Pivot table, from which it differs in that it is based on tables that belong to the Data model. It can handle large amount of data that are unpacked when needed, when we have to use them in report, thus saving memory space. It has several specific options, such as KPI, hierarchies or data sets, which can be very helpful when we are making complex reports.
We use Data model, in which we add and connect several tables, to create reporting dataset. In one of previous recipes we were talking about creating links between tables by dragging & dropping keys. The subject of this post will be manual modifying, changing properties or removing data connection. Also you’ll be able to see properties of tables imported from other data sources, such as MS SQL, MS Access etc.
In one of previous recipes I described how to make an Excel database connection. Similarly you can create a connection between Data model and various data sources. Main difference is in fact that Data model uses technologies for compressing data, during their import, and decompressing them only when used in the report, which saves memory and allows you to work with large amounts of data. You can create a link to a single table, multiple tables or to a data set made by running SQL queries.
When you start using Excel business intelligence tools you’ll realize that they enable connecting with data from multiple sources. This can be an Excel spreadsheet or text files, databases, data from the Internet, data warehouse “in the cloud” … If you have ever used MS Access, you will easily understand how to add and link tables within the model. If not, read this article and you will see that it is easy and quite intuitive task.