SSAS: Utility to export SQL code from your cube's Data Source View (DSV)
Posted
by DrJohn
on SQL Blogcasts
See other posts from SQL Blogcasts
or by DrJohn
Published on Fri, 24 Feb 2012 14:19:00 GMT
Indexed on
2012/03/18
18:11 UTC
Read the original article
Hit count: 300
When you are working on a cube, particularly in a multi-person team, it is sometimes necessary to review what changes that have been done to the SQL queries in the cube's data source view (DSV). This can be a problem as the SQL editor in the DSV is not the best interface to review code. Now of course you can cut and paste the SQL into SSMS, but you have to do each query one-by-one. What is worse your DBA is unlikely to have BIDS installed, so you will have to manually export all the SQL yourself and send him the files. To make it easy to get hold of the SQL in a Data Source View, I developed a C# utility which connects to an OLAP database and uses Analysis Services Management Objects (AMO) to obtain and export all the SQL to a series of files. The added benefit of this approach is that these SQL files can be placed under source code control which means the DBA can easily compare one version with another.
The Trick
When I came to implement this utility, I quickly found that the AMO API does not give direct access to anything useful about the tables in the data source view. Iterating through the DSVs and tables is easy, but getting to the SQL proved to be much harder. My Google searches returned little of value, so I took a look at the idea of using the XmlDom to open the DSV’s XML and obtaining the SQL from that. This is when the breakthrough happened. Inspecting the DSV’s XML I saw the things I was interested in were called
- TableType
- DbTableName
- FriendlyName
- QueryDefinition
Searching Google for FriendlyName returned this page: Programming AMO Fundamental Objects which hinted at the fact that I could use something called ExtendedProperties to obtain these XML attributes. This simplified my code tremendously to make the implementation almost trivial. So here is my code with appropriate comments. The full solution can be downloaded from here: ExportCubeDsvSQL.zip
using
System;using System.Data;
using System.IO;
using Microsoft.AnalysisServices;
... class code removed for clarity
// connect to the OLAP serverServer olapServer = new Server();
olapServer.Connect(config.olapServerName);
if (olapServer != null)
{ // connected to server ok, so obtain reference to the OLAP database
Database olapDatabase = olapServer.Databases.FindByName(config.olapDatabaseName);
if (olapDatabase != null)
{
Console.WriteLine(string.Format("Succesfully connected to '{0}' on '{1}'",
config.olapDatabaseName,
config.olapServerName));// export SQL from each data source view (usually only one, but can be many!)
foreach (DataSourceView dsv in olapDatabase.DataSourceViews)
{ Console.WriteLine(string.Format("Exporting SQL from DSV '{0}'", dsv.Name));
// for each table in the DSV, export the SQL in a file
foreach (DataTable dt in dsv.Schema.Tables)
{
Console.WriteLine(string.Format("Exporting SQL from table '{0}'", dt.TableName));
// get name of the table in the DSV
// use the FriendlyName as the user inputs this and therefore has control of it
string queryName = dt.ExtendedProperties["FriendlyName"].ToString().Replace(" ", "_");
string sqlFilePath = Path.Combine(targetDir.FullName, queryName + ".sql");
// delete the sql file if it exists
... file deletion code removed for clarity
if (dt.ExtendedProperties["TableType"].ToString() == "View")
{
File.WriteAllText(sqlFilePath, dt.ExtendedProperties["QueryDefinition"].ToString());}
if (dt.ExtendedProperties["TableType"].ToString() == "Table")
{ File.WriteAllText(sqlFilePath, dt.ExtendedProperties["DbTableName"].ToString());
}
}
Console.WriteLine(string.Format("Successfully written out SQL scripts to '{0}'", targetDir.FullName));}
}
}
Of course, if you are following industry best practice, you should be basing your cube on a series of views. This will mean that this utility will be of limited practical value unless of course you are inheriting a project and want to check if someone did the implementation correctly.
© SQL Blogcasts or respective owner
