Tuesday, June 9, 2015

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

Thursday, April 30, 2015

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.



Wednesday, April 8, 2015

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.


Sunday, March 23, 2014

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.

Friday, February 14, 2014

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.





Wednesday, February 12, 2014

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.



Wednesday, January 1, 2014

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.