Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to manipulate data during import in SSAS Tabular vNext


By:   |   Last Updated: 2017-02-22   |   Comments   |   Related Tips: > 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. Import New Tables in SQL Server Data Tools for Analysis Services

  3. 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.


  4. Query Editor in SQL Server Data Tools for Analysis Services

  5. 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.


  6. Advanced Editor in SQL Server Data Tools for Analysis Services

  7. 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.


  8. Refresh Preview Option in SQL Server Data Tools for Analysis Services

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


  10. Remove Columns Options in SQL Server Data Tools for Analysis Services

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


  12. Remove Rows Options in SQL Server Data Tools for Analysis Services

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


  14. Delimiter Options in SQL Server Data Tools for Analysis Services

  15. 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.


  16. Sorting, Grouping and Casting Data Options in SQL Server Data Tools for Analysis Services

  17. 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.


  18. Index Column Options in SQL Server Data Tools for Analysis Services

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


  20. First Row as Header Option in SQL Server Data Tools for Analysis Services

  21. 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.


  22. Move Column Options in SQL Server Data Tools for Analysis Services

  23. 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.


Last Updated: 2017-02-22


get scripts

next tip button



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

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools