Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

The Baker's Dozen: 13 Miscellaneous C#, SQL, and Business Intelligence Development Tips : Page 5

Windows Communication Foundation is one of the most important features in the history of .NET. Not only does WCF provide a unified programming model for communications, it also promotes the use of interfaces and various recommended programming practices.


advertisement

Tip 10: MDX Named Sets in SSAS 2008

T-SQL developers making the transition to OLAP will often try to seek counterparts for stored procedures and user-defined functions. Some discover that OLAP/MDX calculated members server as a nice counterpart for scalar user-defined functions.

The counterpart for stored procedures and table-valued user-defined functions is a very different story. MDX provides named sets, which developers can use to define stored MDX expressions that return a set of information. Developers can refer to a stored MDX expression with an alias, as needed.

For example, suppose you want to create a named set that will return the 10 most profitable product categories from the AdventureWorks DW database and show the ranking number for each category.

 
Figure 11. Named Set: This MDX expressions uses TOPCOUNT to retrieve the 10 most profitable product categories.

Figure 11 shows an example of an MDX named set called [Top10ProductsByProfit].

Here's the MDX expression in Figure 11. Note that it uses the TOPCOUNT function to retrieve the 10 most profitable product categories:

TOPCOUNT( [Product].[Product Categories].[Product].Members, 10, [Measures].[Internet Gross Profit])

A developer can enter the code for the named set in Visual Studio for a project associated with the OLAP cube (as shown in Figure 11), or directly into the OLAP cube (e.g. in SSMS 2005).

Next, the following code (an MDX calculated member) calculates the ranking number for each product within the top 10 set. Note that the second parameter for the MDX RANK function is the actual named set alias from above.

RANK([Product]. [Product Categories].CurrentMember, [Top10ProductsByProfit])

A developer can then use the named set on the ROW axis of any MDX query, as follows:

 
Figure 12. Static Named Set: This static named set has no additional dimension slicing (correct results).


// Note the predefined named set and // calculated member SELECT {[Measures].[Internet Gross Profit], [Measures].[ProductProfittRank]} ON COLUMNS, [Top10ProductsByProfit] ON ROWS FROM [Adventure Works]

This produces the result set in Figure 12. So far, so good. However, the query above generated the top 10 products by profit for all geographies as an aggregate.

Now suppose an analyst wants to see only the top 10 selling products for the country of France:

 
Figure 13. Static Named Set for France: This query added additional dimension slicing, but produces incorrect results, as shown by the low gross profit figures.

SELECT {[Measures].[Internet Gross Profit], [Measures].[ProducProfittRank]} ON COLUMNS, [Top10ProductsByProfit] ON ROWS FROM [Adventure Works] WHERE [Customer].[Country].[France]

The preceding query generates the result set in Figure 13. However, while the product profit rank column appears "correct," the numbers for gross profit are not. In fact, a closer examination reveals that the result set contains the same 10 products that the query displayed in the first result set (for all geographies).

This example demonstrates a serious issue with permanently stored MDX named sets in Analysis Services 2005. In 2005, named sets are static; in other words, SSAS evaluates the MDX expression once, and ignores any subsequent dimension slicing that a user might perform. So essentially, SSAS never sees the slicing for the country of France. While the result set correctly shows the profit dollars for each product for France, the ranking and Top 10 logic are incorrect.

Some are unaware of this issue in SSAS 2005 (and may become aware of it only after someone points out incorrect numbers on a report). Others are aware, and use the only real workaround: Include the MDX named set expression "inline" in a query, thereby losing out on reusability.

One of the top enhancements in SQL Server Analysis Services 2008 is dynamic named sets. As implied from the name, dynamic named sets perform as developers expected all along. Figure 14 shows how you can define a named set as dynamic in the Visual Studio interface.

 
Figure 14. Defining a Dynamic Named Set: You can change a named set to be dynamic in SSAS 2008.
 
Figure 15. Correct Results: After defining a dynamic named set, the results for France are correct.

After executing the same code, Figure 15 shows the correct results, demonstrating that dynamic named sets will recognize any subsequent dimension slicing.

Tip 11: Reading OLAP Data with .NET

.NET developers may need to query OLAP data from within a .NET application. Fortunately, developers can use the same OleDbConnection, OleDbDataAdapter, and OleDbCommand objects that they use when querying relational data. Here's an example of querying an OLAP cube, using MDX as the command text:

// Reading data from an OLAP source OleDbConnection oConn = new OleDbConnection(); oConn.ConnectionString = "provider=msolap;Data " + "Source=localhost;" + "initial catalog=Adventure Works DW;"; OleDbCommand oCmd = new OleDbCommand(); oCmd.Connection = oConn; oCmd.CommandText = "SELECT [Internet Sales Amount] ON COLUMNS, "; oCmd.CommandText += " DESCENDANTS ( [Date].[Fiscal].[FY 2004], "; oCmd.CommandText += " [Date].[Fiscal].[Month] , "; oCmd.CommandText += " SELF_AND_BEFORE ) ON ROWS "; oCmd.CommandText += " FROM [Adventure Works] "; DataSet dsCube = new DataSet(); OleDbDataAdapter oDA = new OleDbDataAdapter(oCmd.CommandText,oConn); oDA.Fill(dsCube, "CubeData");

Additionally, developers may want to "discover" the entire dimension model of an OLAP cube. Using the Microsoft Analysis Services ADOMD Client, a developer can open an AdomdConnection and then iterate through the Cubes, Dimensions, Hierarchies, and Levels collections:

// Using the Microsoft Analysis Services ADOMD Client AdomdConnection oConn = new AdomdConnection ("Data Source=localhost;" + "initial catalog=Adventure Works DW"); oConn.Open(); DataTable dtCubeInfo = new DataTable(); dtCubeInfo.Columns.Add("Cube",typeof(System.String)); dtCubeInfo.Columns.Add("Dimension",typeof(System.String)); dtCubeInfo.Columns.Add("Hierarchy",typeof(System.String)); dtCubeInfo.Columns.Add("Level",typeof(System.String)); foreach (CubeDef oCube in oConn.Cubes) foreach (Dimension oDimension in oCube.Dimensions) foreach (Hierarchy oHierarchy in oDimension.Hierarchies) foreach (Level oLevel in oHierarchy.Levels) dtCubeInfo.Rows.Add( oCube.Name, oDimension.Name, oHierarchy.Name, oLevel.Name);

Developers can use this approach to construct custom query/browser-like interfaces for the OLAP dimension hierarchy.

Tip 12: Searching Dynamically in SQL Server without Using Dynamic T-SQL

Not only are stored procedures often preferable, they are also often necessary. Many companies lock down tables in SQL Server (or whatever back-end database applies) so that stored procedures are the only access to data.

Critics of stored procedures claim that stored procedures aren't as flexible as other approaches, such as dynamic SQL. Take, for example, a search screen for an end-user customer representative—the screen prompts the user for name, address, city, state, zip, etc. The end user may need to search on a full name and a partial address, or a name that starts with "SMI" and an address that ends in "AVE," etc. (Presumably, the screen might have a small pull-down beside each field, with options for equals, begins with, ends with, and contains.")

Again, those advocating dynamic SQL will construct the query based on the fields the user filled in, and feel that stored procedures aren't as flexible. The code here challenges that opinion, using a stored procedure that:

  • Accepts all the possible lookup options as parameters
  • Sets any NULL parameters (i.e. fields that the user left blank) to '%' (the SQL Server wildcard character)
  • Uses a series of LIKE @ColumnParameter statements in the WHERE clause. Any non-specified parameters are simply compared to themselves (which SQL Server optimizes)
Here's the stored procedure code:

CREATE PROCEDURE [dbo].[GetCustomers] @LastName varchar(50) = null, @FirstName varchar(50) = null, @Address varchar(50) = null, @City varchar(50) = null, @State varchar(2) = null, @Zip varchar(13) = null AS BEGIN SET NOCOUNT ON ; SET @LastName = isnull(@LastName,'%') SET @FirstName = isnull(@FirstName,'%') SET @Address = ISNULL(@Address,'%') SET @City = isnull(@City, '%') SET @State = isnull(@State,'%') SET @Zip = isnull(@Zip,'%') SELECT * FROM Customers WHERE (LastName LIKE @LastName) AND (FirstName LIKE @FirstName) AND (Address LIKE @Address) AND (City LIKE @City) AND (State LIKE @State) AND (Zip LIKE @Zip) END

All the application must do is place the SQL Server wild card character (%) at the beginning and/or end of any search string, based on how the user wanted to apply the search for that column. Here are some examples:

-- Last Name starts with Smith, -- lives in NY EXEC [dbo].[GetCustomers] @lastname = 'SMITH%', @STATE = 'NY' -- First Name is Laura, -- lives on an AVE, in PA EXEC [dbo].[GetCustomers]

 
Figure 16. PPS Web Service Methods: The figure shows some of the methods available to developers through the PPS web service URL.
@FIRSTNAME = 'LAURA', @ADDRESS = '%AVE%', @STATE = 'PA'
Assuming you have SQL indexes on all the columns (names, address, etc.), the execution plan demonstrates that the query costs reside in index seeks and key lookups. I've used this approach for customer databases with nearly a million rows, and the searches are usually instantaneous.

Tip 13: The PerformancePoint Server Web Service API

Microsoft PerformancePoint Server 2007 is one of the more exciting products for Business Intelligence. While PPS 2007 is a version 1.0 product, Microsoft will surely enhance it to position it firmly as a capable performance monitoring, scorecarding, and planning application.

While the user base for PPS 2007 is primarily OLAP developers and power users, .NET developers can take advantage of the PPS 2007 web service to create/access scorecards and dashboards programmatically. Figure 16 shows some of the available methods in the PPS web service, accessed via the PPS web service URL (http://localhost: 40000/WebService/pmservice.asmx):

You can find the entire source code for this article on my web site. Check my blog for any follow-up tips, additional resources, and notes on Baker's Dozen articles.



Kevin S. Goff is the founder and principal consultant of Common Ground Solutions, a consulting group that provides custom web and desktop software solutions in .NET, Visual FoxPro, SQL Server, and Crystal Reports. Kevin has been building software applications for 17 years. He has received several awards from the U.S. Department of Agriculture for systems automation. He has also received special citations from Fortune 500 companies for solutions that yielded six-figure returns on investment. He has worked in such industries as insurance, accounting, public health, real estate, publishing, advertising, manufacturing, finance, consumer packaged goods, and trade promotion. In addition, Kevin provides many forms of custom training.
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap