Microsoft Data Appliances Help Simplify MSBI Projects

As some of you know, I am really excited about the data appliances Microsoft and HP have released this year.  I really believe that they make it even easier to get MSBI projects up and running while minimizing the complexity of building out servers. Read more about my thoughts on this in an article I wrote for Magenic:  Microsoft Data Appliances Lower the Entry Bar for MSBI Adoption.


X on XMLA: i. Using XMLA


In my previous blog, I introduced the “X on XMLA” series.  In this blog, I will do more of an introduction to XMLA by discussing how I have used XMLA in my experience with SSAS.

What is XMLA? XML for Analysis or XMLA is the API used to interact with Analysis Services and other similar multidimensional database servers.  The goal was to build out a standard API to meet this goal and it has been widely accepted by the leading OLAP vendors.  You can find out more about the standard at XMLAforAnalysis.  This is a good resource for generic XMLA topics including the current specifications document and a newsfeed.

A key point about XMLA is that it is, as its name states, XML.  The API uses SOAP protocols to communicate with the servers.  (Refer to the specification above if you would like more details.)  I like this because XML is easy to read.  While it can be annoyingly verbose at times, you are able to easily identify what you are doing which reduces the learning curve.

Uses of XMLA.  Now that you have the cursory introduction to XMLA, what is it commonly used for?  I have primarily used XMLA in the areas of loading data and deploying objects.  I use SQL Server Management Studio (SSMS) extensively to interact with my SSAS databases so I do not use the interrogation or query functions within XMLA that often.  However, you should be familiar with all of these uses.

Deploy.  I use XMLA to deploy new objects and alter existing objects on a regular basis.  In particular, this is a standard way to test deployment and to script deployment operations for production releases.  XMLA supports Create, Alter and Delete commands for every object in SSAS.  In SSMS you can generate any of these scripts using the short cut menus as noted in the image below.  You should become familiar with using XMLA as a deployment method as it is an simple way to promote changes between environments.

XMLA Create Script Menu

Load.  The Process commands are used to load data in a variety of methods including full replace or reload, incremental load, and updates.  XMLA also supports commands which will drop and recreate indexes and aggregations if that is required.  SSAS exposes additional processing options through XMLA that are not available in SSMS or Business Intelligence Development Studio (BIDS).  The script below is a basic example of processing a dimension using XMLA.  (Note:  ->> designates a line break.)

<Process xmlns=  ->&gt;
        <DatabaseID>Adventure Works DW 2008R2 SE</DatabaseID>
        <DimensionID>Dim Time</DimensionID>

Interrogate.  While I have not used XMLA for this activity as much as the two activities noted above, I will be discussing this in detail in one of the later blogs.  The Discover method allows you to retrieve information about the SSAS instance and related database objects.  For instance, you can use XMLA to return a list of available databases on the target server.

Query. XMLA can also retrieve data from the cube.  It is able to accomplish this by executing MDX against the target.  I do not usually have a need to query from XMLA, but I will discuss this usage in this series as well.

Hopefully this gives you an idea of how powerful XMLA is and why it is a necessary tool for any BI developer working with multidimensional database servers.

Introducing “X on XMLA”


XMLA or XML for Analysis is used in SQL Server Analysis Services and other multidimensional data systems to manage the server.  It is a cross between DDL and Server Scripts.

I am starting a new blog series on XMLA called “X on XMLA”. I have had to use this scripting language extensively in my projects and would like to discuss how I implemented it in my SSAS work.  The goal of this series is to provide an introduction to XMLA with practical application shown throughout.

The series will consist of ten (X) entries which is where the pithy name came from.  Here is the current list of topics that are planned.  As more topics are developed this list will be filled out. Topics will include processing, creating, altering, deleting, and deploying SSAS objects with XMLA.  As the entries are completed, this blog will be updated with links to the other entries in the series.

i. Using XMLA
ii. Basic Structure of XMLA
iii. Basic DDL Funcitons in XMLA (Create, Alter, Delete)
iv. Deploying Databases with XMLA
v. Creating XMLA from Visual Studio Projects
vi. Processing and Out-of-Line Bindings in XMLA
vii. Partition Management with XMLA
viii. SSIS and XMLA
ix. Executing a Select with MDX in XMLA
x. Using the Discover Method

XMLA is very powerful and I hope this series will help you realize the potential and power of using XMLA with SQL Server Analysis Services.

Why It’s Called a User Group…

On Tuesday night was the first meeting for PASSMN, the SQL Server User Group in Minneapolis/St. Paul this year.  As the new Chair, I came early and got the announcements rolling, the LiveMeeting set up and the SWAG laid out.

For this meeting, Joe Webb of WebbTech Solutions was presenting on writing good queries via LiveMeeting.  No big deal, right?  When it came time for him to take over I was unable to make him a presenter.  I am sure there is something I missed, but I was unable to get this resolved.  Joe and the users, to their credit, were very patient as we tried worked through the technical issue.

After struggling with LiveMeeting, Joe and I decided to have him send me the slides and he would have to direct traffic via the conference call we had going.  About that time one of our users (Bob) came up to the podium and suggested that we try  This would allow Joe to share his screen with us so we could see the demos he had ready for us.  Joe fired up and he was able to do his presentation complete with demos.

I think there are a couple of lessons learned from this experience:

  1. Check the LiveMeeting or conference call prior to going live
  2. Always have a backup plan (which for us is (Check it out, you may find it useful in your group as well.)
  3. Users can and should contribute, it is there group after all

While 1 and 2, I will try to remember each time never forget because I will remember and the call capability of our space, number 3 may be the most important.  Because a user rescued me and the meeting Tuesday night we were able to enjoy Joe’s presentation.  This even spurred additional conversation afterwards about the importance of users being involved in the group.

So, in that vein, users, you need to step up and contribute whenever there is an opportunity.  You are the key to the success of every user group out there.  Thanks for your ongoing participation and contributions, it matters.

User group leaders, we need to find more ways to get more users involved in our groups.  PASSMN has a six member board with more opportunities at events such as SQL Saturday.  We continue to encourage more involvement and are looking at ways to grow the speaking skills and the speaker base in our group.  Without users our group will die we need them.  A group of one is not much fun.

Our meeting was a success because a user stepped up to help out.  Thanks again Bob.  You helped me survive my first meeting as the Chair and MC.