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.