Karan Gulati (SSAS Maestro)

Applies to Windows7, Windows 2008/R2 and Vista

SQL Server all builds >= SQL 2005

Generally, for troubleshooting SSAS Issues, we recommend to collect below mentioned Perfmon Counters

– All Counters Related to SSAS (MSAS_ or MSOLP$)

For default instance SSAS Counter Name suffix with MSAS.n and for named instance it start with MSOLAP$Instance Name

– System, Process, Processor, Physical Disk, Logical Disk, Memory , Page File

· Got to Run & type Perfmon

· Under Data Collector Set, right click on User Defined and Click New Data Collector Set

· Create New Data Collect Set and Give name as MSTrace & Select Create Manually, Click Next

· Click on Add required counters (Sample Interval as 10 Seconds)

· Here you can specify the Run as user which has Admin Access over box (in case you have logged with credential which is non admin)

• By default Location of Trace file is – %systemdrive%\PerfLogs\Admin\ but you can change it.

Right Click on Newly Created Trace – Mstrace and select Properties – In Properties under directory tab you can specify the location also you can schedule as per your requirement.

Once you are done with that click OK and Run trace as shown below

Reproduce the issue and collect traces, once you are done just stop traces and check for traces in location which you had provided in Properties.

Issue:

Intermittently Getting below mentioned error during processing
OLE DB error: OLE DB or ODBC error. Transaction (Process ID n) was deadlocked on thread| communication buffer resources with another process and has been chosen as the deadlock victim.

Cause:
A deadlock is a circular blocking chain, where two or more spids are each blocked by the other so that no one can proceed.

You don’t have control over the Select Statement generated by SSAS Processing job (If you are not using Named Query in DSV), in that case you need take care of things from SQL Engine Perspective.

Here are few recommendations, which can reduce deadlock situation:

• Use separate environment for Reporting and Transaction
• Ensure the database design is properly normalized.
• Have the application access database objects in the same order every time.
• Avoid cursors in DW environment
• Use as low a level of isolation as possible for user connections.
• Right indexes for getting correct execution plan (rebuild indexes after huge data modifications)
• Have updated Statistics what will generate a right execution plan
• During DW data refresh don’t do Cube Processing
• In DSV refer View instead of Tables – Processing through a view provides you with an extra layer of abstraction on top of the database  is a good design strategy. In the view definition, you can add a NOLOCK or TABLOCK hint to remove database locking overhead during processing.
• Try experiment with MAXDOP setting – Set it to one, in your dev / test environment and see if you can reproduce issue; Keep in mind Performance Caveat of using MAXDOP=1.

Ref:

SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

How To Monitor Deadlocks in SQL Server

*SSDT – SQL Server Data Tools

*SSMS – SQL Server Management Studio

In SSDT Query Designer getting Null for All members:

One of my customers recently reported this issue so thought of sharing with world……

Demo given below will provide an insight……………

Simple query, where we are doing cross join of allmembers from Product and Date dimension

select non empty ([Product].[Category].allmembers*[Date].[Calendar].[Calendar Year].allmembers ) on 1,

([Measures].[Internet Sales Amount]) on 0

from [Adventure Works]

Output in SSMS: All Products visible from SSMS

In SQL 2012 SSDT we have an option of writing MDX queries directly in SSAS Project.

Pretty simple if you haven’t used it…here you will find Query Writer option in same lines you will find in SSRS Project

For opening Query windows in SSDT, open your SSAS database in SSDT and right click on a cube  -> Browse this will open your Browse Window ( you can get the same windows in SSMS as OWC is deprecated)  and click on design mode button which is highlighted below.

Once opened execute the same query and you will find All Products which were showing earlier will turned to NULL – now question is why?

Output in SSDT, if you do comparison with SSMS Query Windows and this, you will find “All Product” are showing as NULL.

Explanation goes with Format of Query when its executed from SSDT Design Mode or SSRS  Vs. in Query Windows of SSMS

Pulled this query from Profiler Trace and the difference you will find in format, in case of SSMS its Native and in-case of SSDT / SSRS its Tabular.

Now what’s tabular format, its basically flattening multi-dimensional result to tabular using Flattening as explained in this article – Flattening Algorithm

Important thing to keep in mind is “If a hierarchy has an ALL member and that member appears on an axis, this is represented in the flattened rowset by a row that has a NULL in every column that corresponds to a level below it”

SSMS:

<PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis">

<Format>Native</Format>

</PropertyList>

SSDT / SSRS

<PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis">

    <Format>Tabular</Format>

</PropertyList>

“Another behavior of the flattening algorithm is that the "[(ALL)]" level is not included in the dataset. This has a couple of implications. The first is that if you want to include data from the "All" member of a dimension, you’ll need to create a calculated member to represent this member as shown below:

CREATE MEMBER CURRENTCUBE.[Product].[Category].[All Products].[All Product]
AS aggregate([Product].[Product].members),
VISIBLE = 1  ;  

After creating this I had re-executed the query and here is the output with Calc Members

For configuring PowerPivot in Excel 2013 you need to follow below mentioned steps:

In Excel 2013 By default PowerPivot add-in is disabled and you need to enable it manually –

1) File –> Options –> Add-in –>Manage Select (Com Add-Ins)

2) After Selecting Com Add-in click GO, by default Add-ins are disabled

3) After checking required Add-ins, in my case I have selected PowerPivot and Power View; you will be able to get PowerPivot Tab in Ribbon

Few things to keep in mind before enabling Add-In

You will need…

• .NET Framework 3.5 SP1 for PowerPivot
• 64 BIT – this will enable PowerPivot to load extremely large data and take leverage of available memory on your box
• Silverlight 5 for Power View

Often I need to share these steps with customers while troubleshooting Connectivity issues, so thought to write an article with screenshots which will ease in collecting traces:

Please go to the following URL and install the latest version of Network Monitor 3.4 , make sure you download the right version depending upon your machine’ architecture (64bit or 32bit)

http://www.microsoft.com/en-us/download/details.aspx?id=4865

• Once installed, go to start –>All Program –>Microsoft Network Monitor 3.4 –>Click on Microsoft Network Monitor 3.4

• Once Nentmon is opened, click on File –> New –> Capture

• Click on Capture and then Click on Start Button

• After then it will start capturing the packets and you can see the data coming in Frame Summary View, try to reproduce the error and once successfully done click on stop Capturing button

• After Stop –> go to File->Save As  with .Cap extension, zip it and mail to me, if I have requested 🙂

Power View implementation paper is out and covers major scenarios of implementing Power View with PowerPivot and Tabular Server including Kerberos.

Power View Infrastructure Configuration and Installation: Step-by-Step and Scripts

http://msdn.microsoft.com/en-us/library/jj591528

Scenario 1: Install a SharePoint 2010 Farm, Add the Power View and PowerPivot Infrastructure, and an Analysis Services Tabular Instance

Scenario 2: Add the Power View and PowerPivot Infrastructure to the SharePoint 2010 Farm

Scenario 3: Install an Analysis Services Tabular Instance

Scenario 4: Install the Power View Infrastructure (without PowerPivot) on a Single Windows Server 2008 R2 Computer that is a Workgroup Member

Scenario 5: Install Client Tools on a Windows 7 Computer and Verify the Power View and PowerPivot Installations

And Configuring Kerberos

This will show you how to import data from SSAS to SQL Engine.

Connect to SQL Engine and Right Click on database where you like to import data from SSAS – Let’s say in my case I have a test database

It will open Import Windows – Provide Data Source (Select Provider for Analysis Services – in my case I am using SQL 2008R2, so selected 10.0)

Hit Properties for providing SSAS Server and Database Details

Choose a Destination

So you have an option of Writing a query or select a table – I haven’t explored option of writing Query, so selected Select Table

Select Single or Multiple table – for demo purpose, I have selected Item as Source

Hit Preview – this will show you data which you are going to import

there is a gotcha, if you click on Edit Mapping you will find something strange –

Yes, you got it – its data types

So, you need to change the data types, something as shown below –

Click OK – > Next and Hit Finish

Some warnings – related to truncate of data, I stated Type as Varchar and Size 254 – so it’s just informing that I can import only that much data in a column,

so I can ignore this message, because I know that I have less than 254 chars in my Attribute / Column / Filed.

After import checked table and found – as I have very small result set, so I can confirm that imported successfully

Questions – If I lost my SQL Database (RDBMS), can I use this methodology to get my data back from the SSAS Database?

Answer – No, it may not give you complete data again depends on design of your Database, let’s say you have ROLAP Partitions (Fact) in case of ROLAP you will not able to import data because AS doesn’t store data in SSAS – and if RDBMS source is missing for ROLAP Partition, Select query will not work; for MOLAP you can do but as I said earlier design of your database may impact data import.

As you have seen earlier in Item Dimension, I have only two columns but if you check DSV in a SSAS Database you will find three columns – oops which means, I lost one column – yes you are right.

So why Col is missing during import – here is an explanation, if you notice in my Dimension Structure I have only two attributes, that explains why only two columns were visible when we were importing.

Second question is why we are getting Item Name, in Item ID column.

Item – is a key attribute but if you notice Properties of this Key Attribute you will find what’s going on

Notice here, Name Col is ItemName – that explains – why I can’t see ItemId Value when I have imported data; Instead of ID as integer value its showing Name of the Item – which is expected because I set name column property.

Like I told you earlier – for disaster recovery you can’t rely on such imports – you need to have your SQL / RDBMS Backups in Place.