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
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:
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
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"
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.
SQL Server 2008 Related Interview Questions
|SQL Server 2000 Interview Questions||MSBI Interview Questions|
|SQL Server 2008 Interview Questions||SQL Server 2005 Interview Questions|
|SSIS(SQL Server Integration Services) Interview Questions||SSRS(SQL Server Reporting Services) Interview Questions|
|Microsoft Entity Framework Interview Questions||LINQ Interview Questions|
|SQL Server Analysis Services (SSAS) Interview Questions||Sql Server Dba Interview Questions|
SQL Server 2008 Related Practice Tests
|SQL Server 2000 Practice Tests||MSBI Practice Tests|
|SQL Server 2008 Practice Tests||SQL Server 2005 Practice Tests|
|SSIS(SQL Server Integration Services) Practice Tests||SSRS(SQL Server Reporting Services) Practice Tests|
|Microsoft Entity Framework Practice Tests||LINQ Practice Tests|
Sql Server 2008 Tutorial
Sql Server 2008 Overview
Sql Server Installation And Configuration
Sql Server Encryption
Automation And Monitoring
Integrated Full-text Search
New Datatypes In Sql Server 2008
T-sql Enhancements For Developers
T-sql Enhancements For Dbas
Sql Server And Xml
Sql Server Xml And Xquery Support
Linq To Sql
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.