Situation

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.

Issue

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.

Troubleshooting

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.

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.

Resolution

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.

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):

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.

From my About.Me page

Family man - Scoutmaster - Data Pro

I have been married to my beautiful wife Sheila since 1993. We are the parents of four awesome kids who keep us busy with all of their adventures including Burnsville High School Marching Band, Winter Drum Line, basketball, dance, scouts (boy & girl) and even learning to drive.

I am also the Scoutmaster for BSA Troop 226 in Savage, MN where I enjoy mentoring boys and seeing them become young men. Along the way we get to serve the community and spend a lot of time outdoors.

I am a Business Intelligence Architect at Pragmatic Works. I am passionate about using data effectively and helping customers understand that data is valuable and profitable. Not only do I do this for customers, I have also delivered over 30 presentations on SQL server and data architecture at local, regional and national conferences. I am also a regular blog contributor at http://dataonwheels.com.