Import data by using a native query
Applies to: SQL Server 2017 and later Analysis Services Azure Analysis Services Fabric/Power BI Premium
For tabular 1400 and higher models, the new Get Data experience in Visual Studio Analysis Services projects provides immense flexibility in how you can mashup your data during import. This article describes creating a connection to a data source and then creating a native SQL query to specify data import.
Create a data source connection
If you don't already have a connection to your data source, you need to create one.
- In Visual Studio > Tabular Model Explorer, right-click Data Sources, and then click New Data Source.
- In Get Data, select your datasource type, and then click Connect. Follow any additional steps required to connect to your data source.
Enter a query as a named expression
- In Tabular Model Explorer, right-click Expressions > Edit Expressions.
- In Query Editor, click Query > New Query > Blank Query
- In the formula bar, type
= Value.NativeQuery(#"DATA SOURCE NAME", "SELECT * FROM ...")
- To create a table, in Queries, right-click the query, and then select Create New Table. The new table will have the same name as the query.
Example
This native query creates an Employee table in the model that includes all columns from the Dimension.Employee table at the data source.
= Value.NativeQuery(#"SQL/myserver;WideWorldImportersDW", "SELECT * FROM Dimension.Employee")
After importing, a table named Employees is created in the model.
See also
Feedback
https://aka.ms/ContentUserFeedback.
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see:Submit and view feedback for