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.

OLE DB error: OLE DB or ODBC error: The SELECT permission was denied on the object 'TableName', database 'DBName', schema 'dbo'.; 42000.

Recently I came across following error while processing cube and after some research I was able to resolve this error. I thought its better to share the solution if someone come across same issue.

OLE DB error: OLE DB or ODBC error: The SELECT permission was denied on the object 'TableName', database 'DBName', schema 'dbo'.; 42000.

If you come across such error then open SSMS instance which cube is using as a underlying relational database server. Go to "Security" folder and then Logins. If your SSAS services are running under "NT AUTHORITY\NETWORK SERVICE" user then double click on that user under Logins.

Under "Login Properties" window, click on "Server Roles" and select role "sysadmin". Click ok and you have resolved your issue. check again with processing cube.

SSAS Calculations tab : Unexpected error occurred: 'Length cannot be less than zero. Parameter name 'length'

Recently I came across one error under Calculations tab. I was not able to access my calculations tab and calculations tab was showing following error.

Unexpected error occurred: 'Length cannot be less than zero. Parameter name 'length'

Sometimes if you are modifying your MDX code under calculations tab and if you missed any syntax under any of the calculations then probably you may encounter the same issue. Now how to resolve this error because you are not even able to access calculations tab if want to correct the syntax, So the solution is simple, connect to analysis services under management studio, right click on SSAS database and open your cube database XMLA using "Script Database as" and then using "ALTER To" option.

Under XMLA, find <MdxScript> node and check the syntax of your calculations, correct that and execute the script. As you are doing changes to calculations tab, you don't need to process/deploy cube. After execution of XMLA, if you open your project using BIDS, you will  be able to access your Calculations tab.

OLE DB error: OLE DB or ODBC error: Login failed for user 'NT AUTHORITY\SYSTEM'.; 28000; Cannot open database "DatabaseName" requested by the login. The login failed.; 42000.

Sometimes while doing cube deployment, you may come across following error and we are going to talk on the resolution for the same in this post.

OLE DB error: OLE DB or ODBC error: Login failed for user 'NT AUTHORITY\SYSTEM'.; 28000; Cannot open database "ODW" requested by the login. The login failed.; 42000.

You may encounter this error if the user you are using to deploy cube does not have access. Since error is saying "NT AUTHORITY\SYSTEM", just go to relational database and expand "Security" folder.

Double click on "NT AUTHORITY\SYSTEM" node, it will open "Login Properties" wizard, go to "User Mapping" tab and check the check-box of relational database which you are using for processing cube.
Give appropriate permissions and click ok. And you will be able to deploy cube successfully.

Install SSAS AdventureWorks 2012 Multi-Dimensional cube database (on Enterprise Edition)

If you want to install SSAS AdventureWorks 2012 Multi-Dimensional database and don't have enough information on how to proceed for the same then this post is designed for you.

1. Open CodePlex and download AdventureWorksDW2012 Data File (DirectLinkToDownload) mdf.

2. Open SQL Server 2012 Database Engine and you can create a relational database using downloaded mdf file. For step-by-step instructions and more details on How-to-attach, you can have a look at technet article.

3. After successful procedure from step2, check whether your relational database instance is showing you the appropriate database named "AdventureWorksDW2012" under database folder.

4. Go to the CodePlex site and download "AdventureWorks Multidimensional Models SQL Server 2012" (DirectlinktoDownload).

5. After successful download, you will get a zip file named "AdventureWorks Multidimensional Models SQL Server 2012", extract the file and go under "Enterprise" folder. Open Solution file.

6. Check the analysis server name under deployment properties and start deployment. After successful completion of deployment, you will be able to access your 2012 Multi-dimensional cube db.

Error while deploying cube "Object reference not set to an instant of an object"

If you are doing some structural changes to your multi-dimensional cube database and while deployment if you receive an error saying "Object reference not set to an instant of an object" then its really hard to find which reference is missing or what exactly error wants to say. So today we are going to discuss about this error. Generally this error appears while cube deployment like the following one.

So if you receive error like this then simply go to "Dimension Usage" tab and check whether all the required dimension relationships are in place or not and if not then you might see like;

Just set the appropriate relationship wherever they are missing and deploy cube. This will resolve your issue.

MOLAP Vs ROLAP

Which one is the best option among-st MOLAP and ROLAP ?, This one is very common question came across most of the newbie SSAS developers and the answer to this question is "Depends on the requirement"...............So Today I am going to talk on this topic.

MOLAP (Multi-dimensional Online Analytical Processing):

When you select MOLAP storage mode and process partitions, it stores a copy of source data and aggregations in a multi-dimensional structure in analysis services server. You can expect a good query performance if you are using MOLAP storage mode because MOLAP structure is highly optimized to maximize query performance and queries fetch data from multidimensional structure instead of source data. Aggregations also help in maximizing query performance.

Advantages:
   1. Good query performance than ROLAP
   2. If your cube is processed then you can access cube data even if you don't have relational source data  available

Disadvantages:

   1. Cube data gets updated only if you process cube (dimensions and partitions) so latency is high

   

Most of the organizations use MOLAP storage mode because they want high query performance for which OLAP systems are widely used. If your client is ready to work on one day prior data then its always better to use MOLAP storage mode because you will get good reporting performance and you can automate daily processing of your cube data through SQL Server jobs

ROLAP (Relational Online Analytical Processing):

When you use ROLAP storage mode, it does not store a copy of source data in the Analysis services. Aggregations of the partition also get stored in indexed views in the relational database. When you execute a MDX query on a cube having ROLAP storage mode, it first check the cache engine and if cache engine does not return data then it access the indexed views to answer a query hence it gives poor query performance than MOLAP.

Advantages:

     1. As data always get fetched from relational source, data latency is low or almost none

     2. Users always get current data without processing cube

Disadvantages:

     1. Poor query performance

If your client always need current data from relational data source and they are not worried about query performance then you can use ROLAP storage mode but you will surely see poor query performance as compared to MOLAP storage mode.

There are few other disadvantages of ROLAP mode like you cannot use MIN or MAX aggregate functions. you cannot use Views for creating your DSV. You can refer BOL article for more details.

MDX EXISTING keyword

Today I am going to talk about MDX "EXISTING" keyword. Seems the details provided on technet article is slightly complex to understand for a newbie developer hence I thought its better to share the details so that everyone can understand the basic usage of it.

Details: EXISTING keyword forces a specified SET to be evaluated within the current context.

Syntax: EXISTING Set_Expression

Example:

Consider a requirement wherein you want to COUNT the number of Products present under each Category. In such requirements you can build MDX using EXISTING keyword which actually explains the usage of keyword too. So we need to write a MDX query in following way for the requirement we have mentioned above.

WITH
  MEMBER [Measures].[X] AS
    COUNT([Product].[Product].[Product].MEMBERS)
  MEMBER [Measures].[Y] AS
    COUNT((EXISTING [Product].[Product].[Product].MEMBERS))
SELECT
  {
    [Measures].[X]
   ,[Measures].[Y]
  } ON 0
 ,[Product].[Category].[Category].MEMBERS ON 1
FROM [Adventure Works]

Output:

If you check the output, you will find that X is returning count of all products whereas Y is returning count of products belongs to each Category because we have given Product Category attribute on rows hence the set of products are evaluating under the context of Category.

Configure web server to access SSAS cube using Excel

This post contains step-by-step instructions for configuring web server in order to access cube using excel. After successful configuration users can access the cube using excel and can create reports using cube data.

1. Connect to the server on which you want to configure web server for accessing cube using excel.
2. Create a folder named CUBE under C:\Inetpub\wwwroot.
3. Copy all the contents from the folder C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\bin\isapi into the C:\Inetpub\wwwroot\CUBE directory.
4. Connect to “Internet Information Services” console. Follow following steps for the same. Click on “Start” -> click on option “Run” -> type “inetmgr” and press OK button. Refer following screen shot for the same.

     5. When you click on OK button, it will open an “Internet Information Services (IIS) Manager” console.   IIS Manager Console looks like following one.          

     6. Create “Application Pool”: 

• Right click on “Application Pools” node and select option “Add          Application Pools”. Refer following screen shot for the same.

You will get following screen when you click on “Add Application Pool”. Mention following details       under “Add Application Pool” window.

• Name: CUBE

• .Net Framework version: .Net Framework v2.0.50727

• Managed pipeline mode: Classic

• Start application pool immediately should be in a checked state.

         

      7. Convert to Application:

• Expand “Sites” folder and then expand “Default website” node. Right click on “CUBE” folder and select option “Convert to Application”. Refer following screen shot for the same.

• When you click on “Convert to Application”, it opens “Add Application” form.

• Click on Select button of “Add Application” window and that will open “Select Application Pool” window. Select “CUBE” under “Application Pool” drop down and click OK button.

• When you click on OK button, you will find that CUBE folder now appears as an application. Refer following screen for the same.

       8. Directory Property settings:

• Click on CUBE node and double click on “Handler Mappings” option. Refer following screen shot for the same.

• When you double click on “Handler Mappings” option, you will find following screen.

• Click on “Add Script Map” option which will open “Add Script Map” window. Insert the details as per following screen shot.

• When you click on OK button, you will get following message box. Click Yes.

       9.  Setting Authentication:

• Select CUBE node under IIS manager console. Double click on “Authentication” option.

• When you double click on “Authentication” option that will open Authentication details screen which looks like following one.

• Right click on “Anonymous Authentication” and select “Edit” option. When you click on “Edit” option, it opens “Edit Anonymous Authentication Credentials” window which looks like following one.

• Click on “Set...” button and insert credentials of user.

       10. Change Binding settings:

• Click on “Default Web Site” under IIS Manager Console and click on “Bindings” option. Refer following screen shot for the same.

• When you click on “Bindings” option, it will open “Site Bindings” window. Select a row which contains Port 80 and click on “Edit” button. Refer following screen for the same.

• When you click on “Edit” button, it will open “Edit Site Binding” window. Change Port to 8081 and click on OK button.

        

         11. Start “Actions”:

• Select “Default Web Site” node under IIS manager console. On the right side there is “Actions” pane, click on Start option.       

Conclusion: You have successfully established the configuration settings of web server and now user can access cube using excel.

Create Tabular Project (for newbie)

Tabular model is very new to most of the developers and if someone wants to create his/her first tabular model then this article will help. Following are the steps to create a tabular model. I am giving an example considering only 2-3 dimensions and one fact.

1. Open "Microsoft SQL Server 2012" folder and launch "SQL Server Data Tools"

2. When you launch the wizard, you will get the Start Page of Microsoft Visual Studio. Click on "New Project" and you will get "New Project" wizard. Expand "Business Intelligence" node and click on "Analysis Services" node.

3. Click on "Analysis Services Tabular Project" and give appropriate name and Location to your project. Click on OK button.

4. Click on "Model" menu from a menubar and click on "Import From Data Source.." option.

5. When you click on "Import From Data Source.." option, you will get "Table Import Wizard" wherein you can see different relational databases options which you can use to create your tabular model.

As I am using AdventureWorks sample database, I am selecting "Microsoft SQL Server" option. So select "Microsoft SQL Server" and click on "Next" button.

Note: You can download a sample database named AdventureWorksDW2012 Data File from a link.

When click on "Next" button, you will be get "Connect to a Microsoft SQL Server Database" wizard. Give server name on which you have your database restored and select "Database name"

6. Click on Next button and select the Impersonation information i.e. you can give your windows credentials or you can use service account. Click on Next button.

When you click on Next button, you will get "Choose How to Import the Data" wizard. As I am going to demo this project through tables, I am selecting option "Select from a list of Tables and views to choose the data to import". you can even write a query to import data. Click on Next button.

When you click on Next button, you will get "Select Tables and views" wizard. Select tables which you want to use to create your tabular model. For sample purpose, I am selecting "DimDate", DimProduct, DimProductCategory, DimProductSubCategory and FactInternetSales. After marking the tables as checked, Click on "Finish" button.

When you click on Finish button, you will get following "Importing" wizard.

Your Tabular Project is ready to use. Bydefault you will get Data View of your tabular model. You can check the model by selecting "Diagram View" from Model View option of Model menu.

7. You can create measures on the columns of fact table. let me show you one example. toggle to Data view model. go to FactInternetSales table and select "SalesAmount" column. Click on summation icon and select SUM option. this will create a measure on SalesAmount column with SUM as aggregate function.

8. Open the properties of measure and change name to "Internet Sales Amount". Save the changes. Right click on project and select Deploy option.

9. After successful deployment, you can browse the data using Excel pivot tables similar to multi-dimensional cube. You can hide attributes, measures which you don't want to show to client using "Hide from Clients tools". Right click on column and select option "Hide from Clients tools".