SSAS Tabular Compatibility 1400 : Use SQL query to import data

In SSAS Tabular with compatibility level 1200, you can import data either from table or by specifying query using second option shown in the following snap however same option is not present in SSAS Tabular with compatibility level 1400.

So what to do if you want to import data using a query instead of importing table or view in SSAS Tabular with compatibility level 1400. You can achieve this by using following steps.

1. Open the "Tabular Model Explorer" and right click on "Expressions" folder and click on "Edit Expressions" option.

2. When you click on "Edit Expressions" option, it will open "Power Query Editor" window. Navigate to "Query" -> "New Query" and then click on "Blank Query" option.

3. Under formula bar, type like the following syntax where DataSourceName is the name of your data source and mention query as per your requirement.

= Value.NativeQuery(#"DataSourceName", "SELECT * FROM Table")

4. Once you click on Enter button, you will be able to see data below formula bar. Right click on newly created expression. here in my case, I have renamed it to DimEmployee. And click on "Create New Table" option.

5. Once you click on "Create New Table" and navigate to "Tabular Model Explorer", you can see new table being created under "Tables" folder.

OLE DB or ODBC error while processing SSAS Tabular Model on Azure instance

Recently one of mine friend was facing the error while processing SSAS model on azure instance and he searched a lot on internet but did not found the solution. Might be he missed some posts so I thought to share the one so if others face same issue then this post may help.

He was trying to process SSAS model on SSAS Azure instance and he encountered following error

Error returned: 'OLE DB or ODBC error: Received error payload from gateway service with ID 2330343: An exception encountered while accessing the target data source

This was not a major issue because he has created SSAS model using 1200 compatibility and tried to process the same on Azure instance. Issue was resolved after changing the "Compatibility Level" to "SQL Server 2017/Azure Analysis Services (1400).

1. Open your SSAS model using SSDT and right click on "Model.bim" and select "Properties" option.

2. Under model properties, change "Compatibility Level" to "SQL Server 2017/Azure Analysis Services (1400).

3. Close the SSDT and reopen again and deploy the solution again and try processing.

SSAS Tabular : Date dimension table

SSAS Tabular :- Create Date dimension table with the help of Calculated table

Following DAX can be used to create Date dimension table if you do not have the same in your database. You can expand this table as per your requirements further by adding columns required


=
ADDCOLUMNS (
    CALENDAR (
        DATE ( YEAR ( TODAY () ) - 5, 1, 1 ),
        DATE ( YEAR ( TODAY () ), 12, 31 )
    ),
    "DateKey", FORMAT ( [Date], "YYYYMMDD" ),
    "MonthId", MONTH ( [Date] ),
    "Month", FORMAT ( [Date], "MMMM" ),
    "QuarterId", IF (
        MONTH ( [Date] ) < 4,
        1,
        IF ( MONTH ( [Date] ) < 7, 2, IF ( MONTH ( [Date] ) < 10, 3, 4 ) )
    ),
    "Quarter", IF (
        MONTH ( [Date] ) < 4,
        "Quarter 1",
        IF (
            MONTH ( [Date] ) < 7,
            "Quarter 2",
            IF ( MONTH ( [Date] ) < 10, "Quarter 3", "Quarter 4" )
        )
    ),
    "Year", YEAR ( [Date] ),
    "DateId", FORMAT ( [Date], "dd-MMM-YYYY" ),
    "Day", WEEKDAY ( [Date] ),
    "DayName", FORMAT ( [Date], "DDDD" )
)