How to manipulate data during import in SSAS Tabular vNext

By: Siddharth Mehta   |   Comments   |   Related: > Analysis Services Development

Problem

Data is not always in the exact desirable format from the source system. Modulating data in-flight during the import process, reduces the need to make modifications to the schema and/or data post import. SQL Server Analysis Services (SSAS) Tabular vNext introduces a modern data import experience, and provides a number of options to manipulate the data during the import process. In this tip, we will explore these data manipulation options in detail.

Solution

SQL Server Data Tools (SSDT) 17.0 RC1 and SQL Server Analysis Services (SSAS) vNext GetData wizard provides a sophisticated query editor to manipulate data during the import process.

1. Assuming that you have already installed SSDT as well as SSAS Tabular vNext, open any sample SSAS Tabular vNext solution in SSDT. Right-click on the Data Source and select the "Import New Tables" menu option as shown below.






2. After you select the exact source data element in the first step, the next step is to manipulate the query in the Query Editor as shown below. The toolbar on the top provides different options to manipulate the data.






3. The first menu option permits you to directly edit the Power Query that gets formed behind the scenes while selecting different menu options in the query editor. You can change the query, validate the syntax for errors and apply to the source data being imported.






4. The next menu let's you refresh and duplicate / remove the Power Query. This option is handy for refreshing the preview or duplicating the entire query that one may want to modify later.






5. The Manage Columns menu item let's you selectively filter columns.






6. The Reduce Rows menu item let's you selectively filter rows of data.






7. The Split Columns menu item let's you identify columns from a delimited text file.






8. Options until now have facilitated forming the shape and size of the data. The next three options allow for Sorting, Grouping and Casting the data into a new data type as shown below. Also, note that each transformation applied to the data can be seen and removed as well from the Applied Steps section.






9. The next menu is the Add Column from an existing column or based on a condition. Based on the selected column, the options corresponding to the Text, Number and Date & Time would get enabled.






10. The next in-line menu is Table, which provides options to change the shape of the data at table level functions like Transpose.






11. The Any Column menu provides a number of options that can be applied on columns like Pivot, Unpivot, Renaming columns, Changing ordinal positions, etc. The other three menus are just provided again on the toolbar for convenience. These are the same options available in the Add Column menu.






12. In this way, using the query editor options one can transform the data using these sophisticated options. Once done, click on the Import button to import the data into the SSAS Tabular solution.

Next Steps

• Consider exploring these query editor options to generate a complex transformed query and assess the post-import editing efforts saved by the same.
• Check out this previous tip: How to import data for SSAS Tabular vNext.

About the author

Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips