Using Analysis Services Scripting Language - SQL Server 2008

ASSL is the scripting language used by clients to communicate with Analysis Services. SQL Server Management Studio and BIDS both use ASSL and its command subdialect, XML/A. ASSL may be used by developers as a means to programmatically communicate with Analysis Services. To examine an ASSL command, we will create an object whose underlying representation is ASSL. As each object type within a project (data source, DSV, dimension, cube, and so on) is represented by ASSL, using BIDS to manipulate ASSL will enable us to explore the complexities and power associated with this language. For this example, we will add a named query to a DSV.

Creating a DSV with a Named Query

When browsing data, a customer’s LastName displays in conjunction with Sales Amount. It would be possible to display both the LastName and the FirstName columns, but this is an inconvenient way to view named data, such as “Show me all people named Patel who bought bike helmets.” It might make more sense to include a full name (LastName + ", " + FirstName) so customers could be uniquely identified, say, Preshant Patel vs. Baiju Patel. We can use a named query for this.

A named query in a DSV is similar to a view within a relational database. The benefit of a named query is that it does not require changes to the source system because the expression defining the named query is stored within Analysis Services.

Create a named query by double-clicking a DSV within Solution Explorer to open the Data Source View Designer (or select View ➤Designer), which shows the database diagram of tables and named queries for the DSV. To create a named query, right-click an empty region in the designer and select the New Named Query option. You will see the Create Named Query dialog box.

In the Name text box, enter the name of the named query, DimCustomerMaster in this example. Enter the code associated with generating the named query at the bottom of the dialog box, as shown in Figure. After you click OK, the named query will be added to the Data Source View Designer.

Create Named Query dialog box

Create Named Query dialog box

The new named query added to the designer still needs some work before it can be used. Specifically, the named query does not contain a primary key and does not have relationships set up to other tables in the diagram. To add these missing entities, do the following:

  1. Right-click the CustomerKey column of the DimCustomerMaster named query and select Set Logical Primary Key. This causes this named view to mirror the primary key of the DimCustomer table.
  2. Drag the DimCustomerMaster.GeographyKey column and drop it on the like-named column in the DimGeography table. This sets up the GeographyKey foreign key in the DimCustomerMaster named query.
  3. Drag the FactInternetSales.CustomerKey column and drop it onto the primary key for the DimCustomerMaster named query (DimCustomerMaster.CustomerKey).

In order to use this newly created named query, either a new cube must be created or a dimension of an existing cube must be edited. If every cube in the Analysis Services project used the named query instead of the underlying table, DimCustomer, it would be possible to delete the source table from the DSV. It is better to remove the table, if using a named query in its place, so that you have no superfluous entities hanging around.

Viewing the ASSL

The best way to experience the power of ASSL is to select the DSV in Solution Explorer and display the code window (select View ➤Code). Figure shows the code for the DSV we modified in the previous section.

ASSL for a DSV

ASSL for a DSV

Given the readability of XML, the ASSL snippet should be understandable because it is composed of elements such as <DataSourceView>, <ID>, and <Name>. Each of these elements is fully documented in SQL Server Books Online, along with aptly named elements such as <DataSource>, <Cube>, <Dimension>, and <Measure>. Looking through the file for our named query, you can find the name DimCustomerMaster inside the hierarchy of a <Diagram> element, representing the designer view associated with the DSV:

<xs:element name="DimCustomerMaster"
msprop:QueryDefinition="SELECT CustomerKey, GeographyKey,
CustomerAlternateKey, Title, FirstName,

Like most XML formats, ASSL is simple to understand but lengthy to write. You can use SQL Server Management Studio to develop such code (File ➤New ➤Analysis Services XMLA Query). Clearly, this highly granular type of development is designed for a very narrow group of applications.

In all, the AdventureWorksDW DSV has more than 1,100 lines of XML in it. The cube developed for the Analysis Services project has more than 1,000 lines. The time it would take to work programmatically with raw XML at the ASSL level, including XML/A to pass commands to Analysis Services, is far greater than working with the graphical environments such as Management Studio and BIDS. Developers requiring a low level of granular access to Analysis Services and companies such as CA, which makes data-modeling software, are candidates for using ASSL and XML/A.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

SQL Server 2008 Topics