Applicable to Tabular Compatibility Level:
SQL 2012 SP1 / SQL 2014 (1103) | SQL 2016 (1200) | SQL 2017 (1400)
Right-clicking on the DSV of an Analysis Services Multidimensional project lets you open two reports about column usage:
Unused Columns Report This report lists all columns in the DSV which are not used in dimensions, cubes, or mining structures. Download a sample. Double-checking columns on this report is a quick way to identify whether you accidentally missed any columns when designing the cube. Keep in mind, though, the general best practice is only to add columns to the cube that are needed for analysis.
Used Columns Report This report lists all columns in the DSV which are used in dimensions, cubes, or mining structures. Download a sample. This report can be used for proofreading the setup of your cube or for documentation.
Tabular Models
Starting with release 1.6.5, the Unused Columns Report is available for Analysis Services Tabular models. Right click in Solution Explorer on the .bim file to launch this report.
As discussed in the Tabular Performance Guide, when processing a Tabular model, Analysis Services runs the SQL query as provided by the developer. This causes any unused columns to be retrieved, slowing model processing. For Tabular models, the Unused Columns Report helps alert the developer of this problem. (Multidimensional models are different in that when Analysis Services processes a Multidimensional cube, it requests only the columns needed, so unused columns don’t slow down Multidimensional model processing.)
Limitation: Note that only the query defined in the Edit Table Properties dialog is checked for unused columns. The partition queries are not parsed looking for unused columns.
In release 2.0.2 and above, the Used Columns Report is shown for Tabular models. This simple report lists the source system table and columns from the Edit Table Properties dialog which are used to build your model and shows how they map to the model table and column.