Troubleshooting a Data Type Issue during SSAS Processing


I have built a cube on a series of views in SQL Server that mimicked similar views in Oracle.  The views create a “virtual star schema” over an operational data store that consists of imports from various mainframe sources.  There is no plan at the moment to move to a stored star schema, so views are being used to fill the gap.  During the development cycle issues arose with performance from the Oracle environment.  While those issues were being investigated, the data store tables used to support the SSAS project were moved to a SQL Server instance for the interim.  We retained the views to minimize the impact for delivery once Oracle issues were resolved.

I am currently deploying changes only from Visual Studio (see image below for Project Configuration settings).  After that, I used SSMS with XMLA to process the dimensions and measure groups in a targeted fashion.



The issue was that along the way, the data types had to be adjusted to support the more robust data typing in SQL Server.  As a result of the data type changes, I started to see errors during processing which noted that the data was being truncated.


I tried a number of things to resolve this particular error.  Along the way I fixed other various but unrelated issues.  Here are the activities I tried to resolve the issue which did not work.

  • Rebuild all dimensions
  • Reviewed ALL relationships, keys, and measures for offending data types.  Some of this was reviewed through the BIDSHelper tool – Dimension Data Type Discrepancy Check.
  • Reduced data set.  This returned the error faster, but also allowed for quicker processing during the resolution of the issue.
  • Eliminated relationships and measures.  As this issue was clearly in the measure group, I removed the relationships and the currency measure from the measure group.  This worked.


The last step was the key piece – by eliminating the relationships, I was able to determine which reset all of the relationship values.  While I had done this manually, it appears that I needed to remove all of the relationships and then add them back in as I processed.  This appears to have resolved the issue I was encountering.

The moral of this story is that the BIDSHelper tool showed me where the error was likely occurring, but as is the case with many of the BI tools in Visual Studio, the change was not propagating to all of the locations required within the XMLA until I retouched them all.  Now if I can just get the memory to release properly, but that is a topic for another day.


SSAS 2008 R2 Operations Guide Released by SQLCAT

If you work in SSAS, you should become very familiar with this new guide from the SQL Server Customer Advisory Team (aka SQLCAT).

They have also previously published the Analysis Services 2008 Performance Guide.

Together these resources provide valuable insight for anyone developing solutions with or managing instances of Analysis Services. 

Simple batch script to generate XMLA and deploy SSAS DB

As I was working through some issues with my cube design, I realized that I wanted to deploy my SSAS databases using XMLA in SSMS.  This can be easily scripted from SSMS, but I had not deployed the database yet.  As a result, I created a batch file to build the XMLA used to deploy a database.  This script can be used for automated builds or even automated deployments if that fits into your model.  I don’t write a lot of batch files so you may want to update the syntax as needed.

The script I used is here (I saved this as a .bat file on my desktop):

—————SCRIPT START———————-

REM Use this batch file to build and create the database XMLA script

ECHO Build started . . .
”C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe” “<< location of your SSAS solution or project>> /build development /out ssasbuild.log

ECHO Build completed . . .
ECHO XMLA Script generation started

Microsoft.AnalysisServices.Deployment.exe “<< project path >>\bin\<<ProjectName>>.asdatabase /d /o:”<<target location and file name>>.xmla”

ECHO XMLA Script generation complete . . .

PAUSE Completed

—————SCRIPT END———————-

The two key executables are devenv.exe and Microsoft.Analysis Services .Deployment.exe.

Devenv.exe may not be in the same location as mine.  This is the Visual Studio executable.  If you have multiple versions of Visual Studio installed, be sure to pick the correct one.  The parameters used are:

  • The location of the SSAS solution (.sln) or project (.dwproj).  Keep in mind that if you build the solution, you will build all of the projects in your solution.
  • /build selects which configuration you are planning to build.  This is a part of the project properties.  In my case, I picked the development build.
  • /out specifies the destination of the log file.  This log file will log the warnings and errors that occur during the build process.

The next executable is the actual deployment executable – Microsoft.AnalysisServices.Deployment.exe.  Once the database has been built it results in an asdatabase file which this process will generate an XMLA script from.  If you built multiple projects in the previous step, you will need to repeat this step for each database you wish to create an XMLA script for.  The parameters used are:

  • The location of the .asdatabase file.  You will usually find it in the bin folder of your project after you have built the project.
  • /d specifies that the deployment executable not connect to the target server during the build of the XMLA which is what we want in this scenario.
  • /o specifies the file location and name of the XMLA output.

(NOTE:  The ECHO and PAUSE statements were used to make this more friendly for my use and are not required to build or deploy the XMLA script.)

Once you have the XMLA file, you can open it in SSMS and choose the target SSAS instance you want to deploy to.  You can also make changes required prior to the deployment such as data source connection strings.

MSDN reference for the deployment utility:

I also referred to Vidas Mitalis’s FAQ creating the same script from SSAS 2005 here while constructing my SSAS 2008 script: