Remove Unnecessary Rows and Columns

It is very important to incorporate technology to help reduce the data loaded into Import models. The import models hold data that is compressed and optimized and then stored to disk by the VertiPaq storage engine. Further, irrespective of the data efficiencies achieved by the VertiPaq storage engine, still it is crucial for you to strive to reduce the data loaded into your models. We shall learn how to remove rows and columns in this section.

In general, following are the different data reduction techniques to reduce the data loaded into the model –

• Remove unnecessary columns
• Remove unnecessary rows
• Group by and summarize
• Optimize column data types
• Preference for custom columns
• Disable Power Query query load
• Disable auto date/time
• Switch to Mixed mode

Steps to remove unnecessary columns

There are two main objectives of the Model table columns –

• Firstly, reporting is used to achieve report designs that appropriate filter, group, and summarize model data
• Secondly, Model structure helps by supporting model relationships, model calculations, security roles, and even data color formatting

Therefore, columns which do not serve any of the above purposes should be removed. This process of removing columns is referred to as vertical filtering. Further, it is suggested to design models with exactly the right number of columns based on the known reporting requirements. Also, your requirements specified may change over time, since it is easier to add columns later than to remove it later. Since, removing columns may lead to breaking reports or the model structure.

Steps to remove unnecessary rows

It is always suggested that model tables should be loaded with minimum rows as per requirement. Now, this can be achieved by loading filtered rowsets into model tables primarily for filtering by the entity or by time. The process of removing rows is referred to as horizontal filtering.

• Firstly, filtering by entity requires loading a subset of source data into the model. Further, this design approach will lead to many smaller models, and help to eliminate the need to define row-level security. Moreover, you can leverage the use of Power Query parameters and Power BI Template files to simplify management and publication.
• Secondly, Filtering by time requires limiting the amount of data history loaded into fact-type tables as well as limiting the date rows loaded into the model date tables. Therefore, it is suggested not to automatically load all available history, unless it is an identified reporting requirement. Remember, time-based Power Query filters can be parameterized, and even set to use relative time periods. Moreover, retrospective changes to time filters will not break reports; it will just lead to lesser (or more) data history available in reports.

For More visit: Modeling Data Reduction Techniques