Linked Server for Analysis Services

Following command creates a linked server named AM_OLAP:
EXEC
@server = N'AM_OLAP', -- name of linked server
@srvproduct=N'MSOLAP',
@provider=N'MSOLAP', -- see list of providers avaoilable on SQL Server under Linked Server > Prover node in SSMS Object Browser
@datasrc=N'MyOlapServer', -- machine that host Analysis Services
@catalog=N'MyOlapDatabase' -- Analysis Services database (cube)


If database server and Analysis Services are on different machines, queries executed through it may have a problem with "double hop" (fact that security context cannot be trasfered from one server to the other):

An exception occurred while executing a Transact-SQL statement or batch.

(Microsoft.SqlServer.Connectionlnfo)

Cannot initialize the data source object of OLE DB provider MSOLAP’ For linked server "OLAP_AM",

OLE DB provider “MSOLAP” For linked server “OLAP_AM” returned message “An error was encountered in the transport layer.”.

OLE DB provider “MSOLAP” for linked server “OLAP_AM’ returned message “The peer prematurely closed the connection.”. (Microsoft SQL Server, Error: 7303)

To resolve that problem you have 3 options:
1. Run SQL queries from data server (you need to be remotly connected to the database server)

2. Enable use of Kerberos on the database server

3. Set proxy account for linked server, so that MDX queries are executed in its context instead of in context of the user that is issuing t-sql query:  

EXECmaster.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'AM_OLAP',@useself=N'False',@locallogin=NULL,@rmtuser=N'myDomain\MyLogin',@rmtpassword='########'
Now you may be able to use it in queries like this:
Select * From OpenQuery(AM_OLAP, 'mdx query')

Axis numbers specified in a query must be sequentially specified

Select ({[Dim Date].[Hierarchy].[Month Name].&[2011]&[June].Lag(11):on rowsfrom [AML DW]where
[Dim Scenario].[Domain ID].&[1002]);



Error message from Analysis Services: Query (1, 8) Axis numbers specified in a query must be sequentially specified, and cannot contain gaps.

Problem was that MDX query specified what will be placed on rows, but not on columns.
In Analysis Services, columns are first axis and rows second.

(
{[Dim Date].[Hierarchy].[Month Name].&[2011]&[June].

{[Dim Status].[Status].&[7],
[Dim Status].[Status].&[10],
[Dim Status].[Status].&[9]}
) Select ( [Measures].[Alerts Count]) on Columns,Lag(11):[Dim Date].[Hierarchy].[Month Name].&[2011]&[June]},on rowsfrom [AML DW]where
[Dim Scenario].[Domain ID].&[1002]); ([Dim Domain].[NAME].&[AML - Suspicious Activity], ([Dim Domain].[NAME].&[AML - Suspicious Activity],
[Dim Date].[Hierarchy].[Month Name].&[2011]&[June]},
[Measures].[Alerts Count],
{[Dim Status].[Status].&[7],
[Dim Status].[Status].&[10],
[Dim Status].[Status].&[9]}
)

SSAS Cube Processing Optimization

Sometimes there is not enough time to process you cubes. You will have to optimize processing. You can do many things.

• Try to separate different processes - break everything into smaller processing tasks. Then you will be able to tackle them individually. It will also allow you to more precisely orchestrate which task are you doing when.
• Separate processing of individual cubes.
• Instead of doing full processing of cube, do processing of data and processing of indexes (cube indexes and aggregations) separately. The first operation will involve you relational source, but the second one will use just SSAS. While SSAS server is busy you can do something else with relational engine/server.
• Try to manually set number of parallel tasks for cube processing. You may have better results. Note that you are not limited to numbers in the drop down list. You can type in other numbers.

Cost of Virtualization in SQL Server 2008 R2

In current version of SQL Server you can have have as many virtual instances of SQL Server as you want. Unfortunately, that is going to be changed in May when R2 is going to be shipped. Only Data Center Edition ($60K per socket) will be unlimited.

Tip: Therefore, if you plan to set virtual servers, buy license before May 2010. Get also Software Assurance license if you want new features that are coming in R2.

del.icio.us Tags: SQL Server 2008 R2,license,virtual server

SQL Server 2008 R2 Reporting Services

R2 brings new Reporting Services features:

• New Report Builder 3.0
• Maps
• Report components
• Improvements in server mode

Maps

• Product sales by shelf space
• Integration with Bing Maps

Report Components

• Parts of reports can be extracted as components and reused in other reports (i.e. datasets and reports items)
• Users are notified when components are updated and can choose to download them

Shared Datasets

• Share queries between reports
• Saved in .rds files; deployed on report server
• Pass parameters to it
• Add filters and calculations
• Can be cashed

Maybe you thought that only new feature of SQL Server 2008 R2 is Power Pivot, but this is another good reason to upgrade your current version of SQL Server.

All Member Formula

Dimensions in Analysis Services 2000 have property that defines value of All member:

All Member Formula = [Reporting Period].[All reporting Period].FirstChild

To convert it to Analysis Services 2005 or Analysis Services 2008 you will need to set it as calculation on cube designer:

CALCULATE;

[Reporting Period].[Reporting Period].[All Reporting Period] = [Reporting Period].[Reporting Period].[All Reporting Period].FirstChild

del.icio.us Tags: MDX,All Member,dimension,SSAS,Analysis Services

What is default member of dimension

Simply put the name of the dimension in the select statement:

select [Product Completion Date] on 0

from [SBCD New Business]

You will get a result set with default member of the dimension and default measure.

del.icio.us Tags: SSAS,Analysis Services,default member,dimension,cube,MDX

MDX Error: "Permission to Access the Referenced Mining Model"

If you get an error like the following back from SSAS server:

Either the user, DEV\Smith, does not have permission to access the referenced mining model, ABCD New Business, or the object does not exist.

This occurs when you omit to specify axis of your MDX query:

select [Product Completion Date] - -on 0

from [SBCD New Business]

del.icio.us Tags: SSAS,Analysis Services,MDX,error,Permission to Access Referenced Mining Model

Dimension Grouping and Ordering in SSAS

Relatively complex example of how to set properties of dimension attributes and hierarchies to get a time dimension that has groupings and sorting.

Scenario

We will use following dimension and hierarchy to demonstrate in this text:

It is based on the following table:

We want to create a complex hierarchy that is ordered using descending order:

Sorting sequence will be used to define order of members.

Ordering by Attribute

You probably know that an attribute in SSAS dimension can be sorted by Key and Name. That is controlled using OrderBy property of the attribute.

You can also sort it using another attribute. You fist have to specify the attribute using OrderByAttribute property and then to specify in OrderBy to be sorted by AttributeKey or AttributeName.

For sorting attribute to appear in the OrderByAttribute list, it must have attribute relationship with the attribute.

Attribute Relationships

The following picture shows how you can set attribute relationships between attributes in a hierarchy. This can be used in both SQL Server 2005 and 2008.

Most people have a trouble to visualize relationships between attributes. Should Month Alias be under Quarter or Quarter under Month Alias? The way I remember this is to say to myself sentence about aggregating. Something like Months aggregate (or roll up or are part) of Quarter. And Quarters are part of Year.

In SQL Server 2008 there is now a new interface that is easier to use. It will visuale manage relation like:

Month ----> Quarter ------> Year

To be able to use sorting attribute it needs to be included in attribute relationship. The following picture shows one way to do this:

Sort Seq will take place in relationship between Moth and Quarter

Month ----> Sort Seq ----> Quarter ------> Year

 

Combining Grouping & Sorting

Since Quarters and Years also have to be sorted in Descending order, you could use two more instances of Sort Seq attribute to order them. Just include them in attribute relationship:

Month ----> Sort Seq ----> Quarter ------> Sort Seq 1 ----> Year ----> Sort Seq 2

Introduction

I am a consultant that works on SQL Server and Analysis Services in Toronto. I would like to share some of my experiences.

Articles will be focused on Analysis Services 2005 and Analysis Services 2008.

I would like to get feedback from readers, so please contact me or leave comment.