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" )
)

Sql "Like" in MDX

If you are looking for a Like keyword in MDX then you are not going to get that but if you want to write MDX which should work like "LIKE" keyword in MDX then you can achieve that by writing MDX in the following way;

Just consider an example wherein you want to show all Members from Calendar hierarchy like "March",  in that case you can write mdx in following way;

WITH
SET CalendarMembers
AS
FILTER(
      DESCENDANTS([Date].[Calendar]),
      vbamdx!INSTR([Date].[Calendar].CURRENTMEMBER.Name,'March',1 >= 1 )
)
MEMBER [Measures].[Caption] AS
    [Date].[Calendar].CURRENTMEMBER.NAME  
SELECT
{[Measures].[Caption]} ON COLUMNS
,
CalendarMembers 
ON ROWS
FROM [Admin]

Output : Above MDX will return all members with caption like "March"

MDX Order Function

Order MDX function is used to show the result set in specified order. Generally we set OrderBy property of key attribute as Key. Sometime we want to define the customized order wherein we can create another attribute on different column which store Order like 1,2,3.....and then we can set OrderBy property of KeyAttribute as "AttributeKey" and specify the attribute name (which stores customized ordering) under "OrderByAttribute" property.

Consider an example wherein you have set the OrderBy property of KeyAttribute as "Key" and while displaying result set, you want to show the members in different orders....in such cases you can use Order MDX function. Consider following example build using AdventureWorks sample;

Open AdventureWorks sample and execute following MDX query

SELECT 
{[Measures].[Internet Sales Amount]} 
ON COLUMNS,
DESCENDANTS([Product].[Product Categories],[Product].[Product Categories].[Category],SELF) 
ON ROWS
FROM [Adventure Works]

After execution, you will get following result set;

Above result set is showing Ordering by Name because OrderBy property of Category attribute is set as Name.If you want to show ordering Descending by Name while showing MDX results then you can use Order function in the following way;

SELECT 
{[Measures].[Internet Sales Amount]} 
ON COLUMNS,
Order
  (
DESCENDANTS([Product].[Product Categories],[Product].[Product Categories].[Category],SELF) ,
[Product].[Product Categories].CURRENTMEMBER.MEMBER_NAME,
DESC
  )
ON ROWS
FROM [Adventure Works];

After executing above MDX, you will get following result set Ordered by Name DESC

Now consider you want to show the results as per Key Order, in such case you can modify your MDX in following way;

SELECT 
{[Measures].[Internet Sales Amount]} 
ON COLUMNS,
Order
   (
DESCENDANTS([Product].[Product Categories],[Product].[Product Categories].[Category],SELF) ,
[Product].[Product Categories].CURRENTMEMBER.MEMBER_KEY,
ASC
)
ON ROWS
FROM [Adventure Works];

After executing above MDX, you will get following result set Ordered by KEY ASC

If you specify DESC in above MDX, you will get result set Ordered by KEY DESC

MDX Scope Statement

Recently someone asked me what is the use of SCOPE statement in SSAS Calcultions tab, I shared one example with that guy to understand the basics of Scope statement. I thought, I should share the same so if anyone wants to understand Scope statement then they can refer this post.

Scope statement is used to limit the scope of specified MDX to a specified subcube i.e. you can specifiy the behaviour for subcube.

Consider Date dimension and Calendar hierarchy from AdvantureWorks sample, Consider, you want to multiply your measure value if user select Month level members. We are taking [Measures].[Internet Tax Amount] and Calendar hierarchy in MDX.

Open SSMS and connect to Anlysis services and execute following MDX query.

SELECT
NON EMPTY [Measures].[Internet Tax Amount] ON COLUMNS,
NON EMPTY
DESCENDANTS([Date].[Calendar].[Calendar Year].&[2008],[Date].[Calendar].[Month],SELF)
ON ROWS
FROM [Adventure Works]

When you execute above query, you will get following results;

Values displayed in above snap are actual values coming from fact. Since Facts are at Date granularity, it is getting aggregated at higher level and displayed at Month level but consider client wants to show double the value if they see at Month level. So in such cases you can use Scope statement so SSAS engine can show expected values at Month level.

Put following Scope statement in Calculations Tab and save changes. We are passing Month level from Calendar hierarchy and [Internet Tax Amount] because we want to show double to same measure at Month level. This returns the current subcube.

Scope 
  ( 
     [Date].[Calendar].[Month],     
     [Measures].[Internet Tax Amount]
  );    

    This = [Measures].[Internet Tax Amount] * 2;    

  End Scope;

Once saved, execute same MDX query and you will find results are doubled up.

Date Difference using MDX VBA functions

Recently I came across one project wherein I see lots of calculated measures build on the top of Date difference and I see finding number of days, number of months, number of years is very common in most of the requirements hence thought of sharing the same.

I am demonstrating samples using AdventureWorks sample;

1. If you want to calculate Days between two supplied dates

In this sample, I am passing Date dimension member and current date and finding Days between two dates supplied.

With Member [Measures].[MemberKey] As
[Date].[Calendar].CURRENTMEMBER.MEMBER_KEY
Member [Measures].[DateFormat] as
VBA!Cdate(
VBA!Mid([Measures].[MemberKey],5,2) + '/' +
VBA!Mid([Measures].[MemberKey],7,2) + '/' +
VBA!Left([Measures].[MemberKey],4)
)
Member [Measures].[CurrentDate] As
Format(Now(),"M/d/yyyy")
Member [Measures].[Date Diff] As
DateDiff ("d", [Measures].[DateFormat],[Measures].[CurrentDate])
SELECT
{[Measures].[MemberKey],[Measures].[DateFormat],[Measures].[CurrentDate],[Measures].[Date Diff]} ON 0,
DESCENDANTS([Date].[Calendar],[Date].[Calendar].[Date],SELF) ON 1
FROM [Adventure Works]

2. Finding Number of Months 

You can find the Number of Month between two dates.