DAX (Data Analysis Expressions) is a language used to create expressions (formulas) for making the reporting dimensions that are used in Power Pivot tables. Once created these expressions can be both used within tabular model of Microsoft SQL Server Analysis Services. A large number of DAX functions have the same syntax as Excel functions, while others can work with relational data and perform dynamic aggregation of data, their filtering etc.
Power Pivot has functionality that offers the choice of a data set that will be used to create reports. Under a set of arbitrarily selected data I mean that for some reason we want to follow, and we want to save them and, if necessary, all together add to the table. Once created sets can be seen as all other reporting dimensions, and their inclusion or exclusion of we influence on the contest of display data.
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.