Archive for month: March, 2007
Roll Your Own UDM Schema Generator
Documenting the UDM schema is a common requirement but to the best of my knowledge neither Microsoft nor third-party vendors provide schema generators. While you are waiting for a commercial tool to fill in the gap, you can roll out your own schema generators. Thanks to the Analysis Management Objects (AMO) library, this task is remarkably simple to implement. For example, here is a .NET code snippet that builds upon the DisplayObjectNames sample (included in the SSAS samples). The code loops through all SSAS databases on the server, traverses the dimension attribute hierarchies and measures and displays their names and source columns.
foreach (Database db in server.Databases) {
Console.WriteLine(“{0}”, db.Name);
// Display Dimensions
Console.WriteLine(“\tDimensions:”);
foreach (Dimension dimension in db.Dimensions)
{
Console.WriteLine(“\t\t{0}”, dimension.Name);
// Display attributes
Console.WriteLine(“\t\t\tAttributes:”);
foreach (DimensionAttribute a in dimension.Attributes)
Console.WriteLine(“\t\t\t\t Name: {0}Source: {1}”, a.Name.PadRight(50), GetKeyCollection(a.KeyColumns));
}
// Display Cubes
Console.WriteLine(“\tCubes:”);
foreach (Cube cube in db.Cubes) {
Console.WriteLine(“\t\t{0}”, cube.Name);
// Display Measure Groups
Console.WriteLine(“\tMeasure groups:”);
foreach (MeasureGroup mg in cube.MeasureGroups)
{
Console.WriteLine(“\t\t{0}”, mg.Name);
// Display attributes
Console.WriteLine(“\t\t\tMeasures:”);
foreach (Measure m in mg.Measures)
Console.WriteLine(“\t\t\t\t Name: {0}Source: {1}”, m.Name.PadRight(50), m.Source.ToString());
}
}
}
Get More Out of SQL Server Reporting Services Charts
One must-read SSRS resource which I missed was the excellent article “Get More Out of SQL Server Reporting Services Charts” by Robert Bruckner from the Reporting Services dev team. Now I have something to point folks to for how-to chart-related questions.
MVP Summit Is Over
MVP Summit is over and I am back in Atlanta. I had great time meeting other MVPs, MS employees, and learning about the future in MS terms. I posted pictures from the event. Some interesting statistics given to us by Microsoft:
The MVP Summit is the largest event held at the Microsoft campus
- 1889 attendees were registered to attend
- 88 countries will be represented (based on registrations)…including for the first time: Algeria, Andorra, Bahrain, Bangladesh, Bolivia, Cyprus, El Salvador, Gibraltar, Honduras, Latvia, Morocco, Nepal, Oman, Rwanda, Tunisia, UAE, Uruguay and Vietnam
- 533 Sessions were delivered
- 956 Microsoft employees pre-registered as speakers/attendees
- 10 Microsoft Executives and 4 Technical Fellows
Next stop – TechEd USA 2007. See you in Orlando.
2007 MVP Global Summit
Joining some 2,000 other MVPs from all around the world, I am heading tomorrow for Seattle to participate in the annual MVP Global Summit. It’s time to shake hands, take pictures, put names to faces, and soak up as much as possible about what’s coming up in Katmai (the next SQL Server release). Unfortunately, the last part is under strict NDA so I won’t be able to talk about it (not until the first public CTP is out that is).
I will also present a spotlight session for the Reporting Services group during which I will share my feedback about the product harvested from a real-life project. Looking forward to meeting these guys at last! Hey, BillG will be also kind enough to talk to the MVP Community and this will be one of his last public appearances as a Microsoft Chairman. A sort of a farewell party for Bill. I will back on Friday with lots of pictures I hope.
Report Builder and Text-based Calculated Members
Although not a common practice, UDM supports defining text-based calculated members. For example, here is an useless but valid text-based calculated member:
Create Member CurrentCube.[Measures].[Hello] As “Hello”;
Much to my surprise, a text-based member won’t show up in Report Builder. That’s because the Report Builder defaults the member’s data type to float and the text is simply “lost” when the report is run. How does the Report Builder determine the field data type with UDM as a data source. With regular measures, it’s simple: the Report Builder derives the field type from the DSV data type. It seems that for calculated members, however, the Report Builder always defaults the data type to float.
While there may other workarounds to resolve this issue, one way is to “correct” the Report Builder invalid assumption about the member data type. Since one would typically auto-generate the Report Builder model when UDM is used as a data source, the ultimate hack is to change the model directly. To do so, go to the model properties in the Report Manager and click the Edit button to save the model as a file. Then, find the text-based member (attribute) by name and change its type from float to string. Then, back to the Report Manager, click the Update button to update the model from the file. As a result, the report will show the member text values correctly.
SQL Server 2005 SP2 Refresh
The initial release of SQL Server 2005 Service Pack 2 (SP2) contained an issue that caused maintenance plan cleanup tasks to remove data before the specified cleanup interval. Install this update to correct the interpretation of existing cleanup task intervals, and to avoid this issue in new cleanup tasks. After you install this item, you may have to restart your computer.
If you already installed the SQL 2005 SP2 released on Feb. 19th, 2007:
- Install the General Distribution Release (GDR) on top of that build to fix the problem. No un-installation of the old SP2 is needed.
- GDR download location:
http://www.microsoft.com/downloads/details.aspx?FamilyId=e2c358a1-ecc4-4c49-8f65-daa6b7800eec&displaylang=en
If you have not upgraded to SQL 2005 SP2 official release:
- Microsoft has released the refreshed SQL 2005 SP2 at Microsoft Download Center last night (March 5th, 2007):
- http://www.microsoft.com/downloads/details.aspx?FamilyID=d07219b2-1e23-49c8-8f0c-63fa18f26d3a&DisplayLang=en
- This SP2 (9.00.3042.00) includes the fix for the database maintenance job problem Microsoft has identified on the SP2 build we released on Feb. 19th, 2007.
- http://www.microsoft.com/downloads/details.aspx?FamilyID=d07219b2-1e23-49c8-8f0c-63fa18f26d3a&DisplayLang=en
The updated SP2 doesn’t fix the RS black preview issue for which a separate hotfix will be available at later time.
Calculated Member as Regular Measure
One of my favorite modeling tricks when I need an UDM calculated measure is implementing it as a regular measure by creating a named calculated column in DSV. I set the expression of the named calculation column to NULL to minimize storage. This approach has a couple of advantages:
- It allows you to set a default aggregation function, e.g. SUM(). In comparison, a calculated member (created with CREATE MEMBER) cannot have a default aggregated function. This could be useful if you need to perform a calculation only at the leaf members of a dimension and then sum the results up.
- You can scope across several measures. For some obscure reason, the SCOPE operator doesn’t support calculated members. For example, the following statement will trigger a “The Measures dimension is used multiple times” (or similar) error on deploy if Member 1 and Member 2 are calculated members:
SCOPE
(
[Date].[Date].[Date].Members,
{
[Measures].Member1,
[Measures].Member2
}
);
but it will work if they are regular measures. If you want to use calculated members you can get away by changing the statement to:
SCOPE
(
[Date].[Date].[Date].Members
);
{
[Measures].Member1,
[Measures].Member2
} = <some assignment>
)
but this may have some side effects, e.g. if you can’t format this scope because everything that intersects [Date].[Date].[Date].Members will be formatted that way as well.
One potential gotcha that bit me when implementing a calculated member as a regular measure is that DSV defaults the named calculation to Integer data type (because its expression is NULL) and the Integer type propagates to the regular measure. As a result, if your calculated expression is a ratio, the results may be rounded in some clients (e.g. Report Builder) although the actual query returns the correct results. To correct this, open DSV source and change the data type of the named calculation in DSV to <xs:decimal> (you can’t change it in design mode).
Presenting at TechEd 2007
Microsoft has just let me know that my proposal for a TechEd 2007 breakout session has been approved. I’ve been honored to present Microsoft SQL Server Reporting Services for four consecutive years at TechEd. This time, I will share practical lessons and tips harvested from report-enabling a real-life application. Here is the formal session description (not yet on the TechEd website):
One of the most prominent feature of Microsoft Reporting Services is its open and extensible architecture that allows developers to report-enable easily their custom applications. Join this “from-the-trenches” session to learn how we leveraged Microsoft Reporting Services 2005 and SQL Server 2005 to add ad-hoc reporting features to our line-of-business application. Topics covered include:
- Understanding the importance of the Report Definition Language (RDL)
- Building an RDL object model to abstract access to RDL
- Integrating the application middle tier with the Report Server web service
- Implementing flexible data retrieval with SQL Server 2005 CLR stored procedures
- Using the VS.NET 2005 Report Viewer control for report viewing
- Caching reports for better performance and historical analysis
- Securing access to the report catalog with Windows and basic security
- Extending reports with custom code
See you in Orlando!
Follow Us
Prologika is a boutique consulting firm that specializes in Business Intelligence consulting and training. Our mission is to help organizations make sense of data by applying effectively BI technologies.
