Joseph Michael Pesch
VP Programming

SSAS Cube Queries to Extract Cube Measures, Dimensions, Attributes, etc.

by 1. January 2014 19:50



MDX Query in C#

by 17. October 2011 08:46


Requires the AdomdClient, which is included in Microsoft SQL Server 2008 Feature Pack, October 2008, found here:

Download "Microsoft ADOMD.NET " from the link above and after running msi file you will get
Microsoft.AnalysisServices.AdomdClient.dll under folder
C:\Program Files\Microsoft.NET\ADOMD.NET\100.



Deploying SSAS Cube from Development to Test/Production

by 23. September 2011 08:47

The easiest way to deploy the cube is to generate a create database script from within SQL Management Studio.  When generating a database script of an Analysis Server database the resulting script file will be XMLA rather than SQL script (as when generating a normal SQL database script).  The resulting XMLA file will contain a DataSources section that contains connection information about the relational database (e.g. datawarehouse database) that the SSAS cube will be reading it's data from.  Below is an example of that section where I have put in variable placeholders to replace the original values of the following items:

  • $(DWServer) - Target database server where the relational data exists.
  • $(DWDatabase) - Target database where the relational data exists.
  • $(DwReaderAccount) - User account name that has read access to the relation database.
  • $(DwReaderPassword) - Password of the reader account.

Here is a sample section of the XMLA file with the above indicated replacements:

  <DataSource xsi:type="RelationalDataSource">
    <ConnectionString>Data Source=$(ImpacDWServer);Initial Catalog=$(ImpacDWDatabase);Integrated Security=True</ConnectionString>
        <ID>DataSourcePermission 1</ID>
        <Name>DataSourcePermission 1</Name>
        <RoleID>Role 1</RoleID>

In order to run the XMLA file through command line (rather than directly in SQL Management Studio) you will need the ASCMD.EXE utility from Microsoft.  You can download it from the SQL Server Analysis Samples page on (below are links to the SQL 2005 and 2008 pages).

SQL 2005

SQL 2008



Microsoft SSAS OLAP Error: A duplicate attribute key has been found when processing table column value

by 9. September 2011 21:28

Here is the specific error I encountered:

"Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'MyTableHere', Column: 'MyColumnHere', Value: '0'. The attribute is 'MyAttributeHere'."

At first I was very confused because I didn't understand how having multiple values of 0 was an issue. It turns out the real issue is having both 0 values AND null values. The issue is manifest by the processing engine as it performs a select distinct which results in one row for the 0 values and another for the null values. However, the null value is then converted into a 0 by default (if you have the "NullProcessing" attribute of the Dimension set to "Automatic").  This is what causes the conflict.  In my case making the nulls 0 before processing the cube resolved the issue.

NOTE: With the default NULL processing for dimension attributes being set to 'Automatic' nulls will be converted to a Zero (for numerical values) or an empty string (for string values). The issue is that the engine first performs a "select distinct ..." on the raw values, so it will come up with one row for NULL and another row for ZERO/BLANK (depending on data type), then the conversion from NULL to the corresponding default occurs thus resulting in conflicting (or duplicate) keys.

Thanks to this post for helping me out:



MDX CellSetGrid Source Code - Browse SSAS Cube

by 2. September 2011 08:38

This is a C# .Net framework 2.0 based custom web control that allows dynamic navigation of SSAS cube through a standard Asp.Net web page using the Microsoft.AnalysisServices.AdomdClient for data access.  To use it in Visual Studio, create a new solution and include the attached CellsetGrid project.  Then add a new file based web site and reference the CellsetGrid project from the web site.  Then drag the "CellSetGrid2" custom control onto the web page and set the ConnectionString and Cube properties.  ConnectionString will need at a minimum "Data Source=ServerName\InstanceName;Catalog=DatabaseName;".  Below is a screen shot of the control in action.