Microsoft has just published an excerpt from my book “Applied Microsoft Analysis Services 2005” on TechNet. Learn how to integrate SSRS and SSAS to build smart reports using data mining and CLR stored procedures.
One of the most outstanding SQL Server 2005 feature is CLR stored procedures. A CLR stored procedure can encapsulate complex .NET code, process data, and pipe the results back to your report. The advantage of using CRL stored procedures is that from a reporting perspective things are very simple. The report just needs to call the CLR stored procedure, as it would call a regular SQL Server stored procedure.
One of my reporting requirements called for passing some XML metadata in the form of a serialized-to-XML object. The stored procedure had to deserialize the object before it can use it. In my case, the object used a custom XML serialization/deserialization by implementing IXmlSerializable interface. My first attempt to deserialize the object from the CLR stored procedure failed abysmally with the following exception:
Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information. —> System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
Initially, I couldn’t understand what was going on since I’ve given my assembly UNSAFE rights, which means that it should be able to do anything. Luckily, googling on this exception brought me to the Vineet’s post which hinted that for security and reliability reasons, SQL Server does not support loading assemblies dynamically. After some experimenting, I was able to fix the issue by following these steps:
1. Sgen the object assembly to produce a pre-generated XmlSerializers assembly.
2. Deploy both the object and XmlSerializers assembly to SQL Server with UNSAFE access. Strangely, I had to deploy also System.Drawing .NET assembly because SQL Server was asking for it.
3. In my CLR database project in VS.NET, add a reference to both the object assembly and the XmlSerializers assembly.
4. Inside the CLR stored procedure, instead of deserializing the object directly, I had to use the XML serializer from the XmlSerializers assembly.
[Microsoft.SqlServer.Server.SqlProcedure]
public static void csp_MyStoredProcedure(SqlXml metadata)
{
// Deserialize the object using the XmlSerializers serializer
FooSerializer serializer = new FooSerializer ();
Foo f = (Foo)serializer.Deserialize(metadata.CreateReader());
. . .
}
So, you need to go through a few development, deployment, and management hoops just to deserialize an object in a CLR stored procedure. Not sure if security is a good justification for this effort though. Moreover, reflecting on the XmlSerializers assembly revealed that it is just a thin wrapper on top of the object assembly. One would say that you never have too much secuirty although, in this case, it certainly feels that way. Are we getting paranoid with security?
Recently, I’ve come across a question on the RS public newsgroup about how to use the new RS 2005 XML extension to report off relative nodes in an XML document. The attached sample demonstrates this scenario. For example, given this document:
<bookstore xmlns=”http://example.books.com“>
<book genre=”autobiography” publicationdate=”1981″ ISBN=”1-861003-11-0″>
<title>The Autobiography of Benjamin Franklin</title>
<author>
<first-name>Benjamin</first-name>
<last-name>Franklin</last-name>
</author>
<date>09-06-1956</date>
<samplechapters>1 3 4</samplechapters>
<price alternative=”discount”>5.99</price>
<price>8.99</price>
</book>
<book genre=”autobiography” publicationdate=”1972″ ISBN=”0399105573″>
<title>The Moon’s a Balloon</title>
<author>
<first-name>David</first-name>
<last-name>Niven</last-name>
</author>
<date>09-06-1974</date>
<samplechapters>4 5</samplechapters>
<price alternative=”discount”>1.94</price>
<price>2.57</price>
</book>
</bookstore>
The following query fetches the book/title, book/author/first-name, and book/date elements, as well as book/@publication date.
bookstore/book {title, author{first-name}, @publicationdate, date}
In my opinion, it could have been nice if you were able to use XPATHs to bring the elements needed instead of this pseudo syntax that relies on curly brackets. Another potential area of improvement is supporting XML schemas to cast the column types automatically according to the defined schema types. As it stands, the XML extension returns all columns as of string date type. However, you can explicitly cast the column type to another type. For example, assuming that the publication attribute returns a valid date:
bookstore/book {title, author{first-name}, @publicationdate(date), date}
will cast the publicationdate column to a date data type.
Perhaps, you’ve heard that Excel 12 will support publishing and executing Excel spreadsheets on the server through a server-based technology (currently dubbed Excel Services). My first reaction when I’ve heard about this was “who on earth would ever want to do this?” However, after reading the David Gainer’s excellent blog (will anyone compile these great notes into a book?) I got my “Eureka” and I have to report a mind shift. David is a Group Program Manager for Microsoft Excel. It is always great to see Microsoft engineers actively sharing first-hand knowledge so we can get the news straight from the horse’s mouth.
Here is what I think about Excel Services based on the David’s notes (I still have to try out my hypothesis). I find the Excel Services model very similar to the Reporting Services architecture. The Excel user authors the spreadsheet using Excel. Then, she can publish the spreadsheet manually or programmatically to the server (through Web service interfaces). The Excel team took the easy way out and decided to leverage SharePoint as a server repository which in this case fulfills the role of the RS report catalog. A published spreadsheet can be viewed in the browser. A special renderer is used on the server side to render the spreadsheet in HTML (IE 6+ only), just like a RS report can be rendered in HTML. Although the spreadsheet is rendered read-only, some interactivity is supported, i.e. the end user can change the PivotTable filter. Behind the scenes, when the user interacts with the spreadsheet, Excel calls down to a Web service to refresh the data (assuming data is retrieved from a data source).
Similar to RS, the user can pass parameters to the published spreadsheet to control the results of the calculations. Finally, a spreadsheet (or portion of it) can be secured using the SharePoint Windows-based security. So, in a nutshell, we are going to get Excel-based Reporting Services.
Is this a good thing? I think so, at least for a couple of reasons:
1. You can integrate server-based spreadsheets into SharePoint-based dashboards.
2. More interestingly, you could publish PivotTable spreadsheets that source data from SSAS 2005 cubes and disseminate them across the enterprise.
The second scenario needs more clarification. The Excel author could manually or programmatically create a PivotTable report connected to an SSAS cube. Then, she could publish the spreadsheet to the server (think of it as a canned report). Once this is done, the end user could request the spreadsheet in the browser. The end-user will be able to interact with the report or open it locally in Excel 12 to gain access to all PivotTable features (subject to security restrictions). Unfortunately, Excel Services will not support full interactivity. For example, the users cannot add or remove PivotTable fields (what a bummer?). In addition, not all SSAS features will be supported, e.g. actions and server drillthrough are not supported.
Despite its limitations, I believe Excel Services will enable new integration scenarios especially in the area of financial reporting. This comes to no surprise considering the fact that one of the most common requirements for financial reporting I hear nowadays is “make it work like Excel” 🙂
SSAS 2005 can be configured for HTTP access, e.g. for Internet reporting. Here are two whitepapers with step-by-step instructions for configuring Windows Server 2003 and Windows XP for HTTP access with SSAS 2005.
There is an extremely nasty new exploit that targets a vulnerability in Windows Fax and Picture viewers (WMF). Usually, I don’t pay much attention to the latest on spyware since IE always asks you to confirm download. But this one is really bad. No IE warnings or security toolbar. If you havigate to a rogue website that uses the exploit, it will bypass the IE security settings. By the time resident anti-spyware and virus shields catch it, it is too late and your machine will be infected, as shown here. For the time being, the workaound is to disable the viewers:
regsvr32 /u shimgvw.dll
Also, do yourself a favor and, if you are not doing this on a regular basis, enable Windows XP System Restore and create a restore point.
Microsoft has isued the following advisory about the new threat.
Microsoft Security Advisory (912840)
Vulnerability in Graphics Rendering Engine Could Allow Remote Code Execution.
Microsoft is investigating new public reports of a vulnerability in Windows. Microsoft will continue to investigate the public reports to help provide additional guidance for customers.
Microsoft is aware of detailed exploit code that could allow an attacker to execute arbitrary code in the security context of the logged on user when visiting a Web site, which contains a specially crafted Windows Metafile (WMF) image. An attacker would have no way to force users to visit a malicious Web site. Instead, an attacker would have to persuade them to visit the Web site, typically by getting them to click a link that takes them to the attacker’s Web site.
Customers are encouraged to keep their antivirus software up to date. The Microsoft Windows AntiSpyware (Beta) can also help protect your system from spyware and other potentially unwanted software. We will continue to investigate these public reports.
Upon completion of this investigation, Microsoft will take the appropriate action to help protect our customers. This will include providing a security update through our monthly release process or providing an out-of-cycle security update, depending on customer needs.
Microsoft encourages users to exercise caution when they open e-mail and links in e-mail from untrusted sources. For more information about Safe Browsing, visit the Trustworthy Computing Web site.
We continue to encourage customers to follow our Protect Your PC guidance of enabling a firewall, applying software updates and installing antivirus software. Customers can learn more about these steps at the Protect Your PC Web site.
Customers who believe they may have been affected by this issue can contact Product Support Services. You can contact Product Support Services in the United States and Canada at no charge using the PC Safety line (1 866-PCSAFETY). Customers outside of the United States and Canada can locate the number for no-charge virus support by visiting the Microsoft Help and Support Web site.
Mitigating Factors:
· In a Web-based attack scenario, an attacker would have to host a Web site that contains a Web page that is used to exploit this vulnerability. An attacker would have no way to force users to visit a malicious Web site. Instead, an attacker would have to persuade them to visit the Web site, typically by getting them to click a link that takes them to the attacker’s Web site.
· An attacker who successfully exploited this vulnerability could gain the same user rights as the local user. Users whose accounts are configured to have fewer user rights on the system could be less impacted than users who operate with administrative user rights.
· By default, Internet Explorer on Windows Server 2003, on Windows Server 2003 Service Pack 1, on Windows Server 2003 with Service Pack 1 for Itanium-based Systems, and on Windows Server 2003 x64 Edition runs in a restricted mode that is known as Enhanced Security Configuration This mode mitigates this vulnerability where the e-mail vector is concerned although clicking on a link would still put users at risk. In Windows Server 2003, Microsoft Outlook Express uses plain text for reading and sending messages by default. When replying to an e-mail message that is sent in another format, the response is formatted in plain text. See the FAQ section of this vulnerability for more information about Internet Explorer Enhanced Security Configuration.
General Information
Purpose of Advisory: To provide customers with initial notification of the publicly disclosed and exploited vulnerability. For more information see the “Suggested Actions” section of the security advisory for more information.
Advisory Status: Under Investigation
Recommendation: Review the suggested actions and configure as appropriate.
References | Identification |
CVE Reference | |
CERT Reference | |
Microsoft Knowledge Base Article |
This advisory discusses the following software.
Related Software |
Microsoft Windows 2000 Service Pack 4 |
Microsoft Windows XP Service Pack 1 |
Microsoft Windows XP Service Pack 2 |
Microsoft Windows XP Professional x64 Edition |
Microsoft Windows Server 2003 |
Microsoft Windows Server 2003 for Itanium-based Systems |
Microsoft Windows Server 2003 Service Pack 1 |
Microsoft Windows Server 2003 with SP1 for Itanium-based Systems |
Microsoft Windows Server 2003 x64 Edition |
Microsoft Windows 98, Microsoft Windows 98 Second Edition (SE), and Microsoft Windows Millennium Edition (ME) |
Note Microsoft Windows Server 2003 Service Pack 1 and Microsoft Windows Server 2003 x64 Edition also refer to Microsoft Windows Server 2003 R2.
What is the scope of the advisory?
Microsoft is aware of a new vulnerability report affecting the Graphics Rendering Engine in Microsoft Windows. This vulnerability affects the software that is listed in the “Overview” section.
Is this a security vulnerability that requires Microsoft to issue a security update?
We are currently investigating the issue to determine the appropriate course of action for customers. We will include the fix for this issue in an upcoming security bulletin.
What causes the vulnerability?
A vulnerability in the way that specially crafted WMF images are handled could allow arbitrary code to be executed.
What might an attacker use the vulnerability to do?
An attacker who successfully exploited this vulnerability could take complete control of the affected system. In a Web-based attack scenario, an attacker would host a Web site that exploits this vulnerability. An attacker would have no way to force users to visit a malicious Web site. Instead, an attacker would have to persuade them to visit the Web site, typically by getting them to click a link that takes them to the attacker’s site. It could also be possible to display specially formed Web content by using banner advertisements or by using other methods to deliver Web content to affected systems.
How could an attacker exploit the vulnerability?
An attacker could host a malicious Web site that is designed to exploit this vulnerability through Internet Explorer and then persuade a user to view the Web site.
I am reading e-mail in plain text, does this help mitigate the vulnerability?
Yes. Reading e-mail in plain text does mitigate this vulnerability where the e-mail vector is concerned although clicking on a link would still put users at risk.
Note In Windows Server 2003, Microsoft Outlook Express uses plain text for reading and sending messages by default. When replying to an e-mail message that is sent in another format, the response is formatted in plain text.
I have software DEP enabled on my system, does this help mitigate the vulnerability?
Yes. Windows XP SP2 also includes software-enforced DEP that is designed to reduce exploits of exception handling mechanisms in Windows. By default software-enforced DEP applies to core operating system components and services. This vulnerability can be mitigated by enabling DEP for all programs on your computer.
For additional information about how to “Enable DEP for all programs on your computer”, see the product documentation.
Microsoft has tested the following workaround. While this workaround will not correct the underlying vulnerability, it will help block known attack vectors. When a workaround reduces functionality, it is identified in the following section.
To un-register Shimgvw.dll, follow these steps:
1. Click Start, click Run, type “regsvr32 -u %windir%\system32\shimgvw.dll” (without the quotation marks), and then click OK.
2. A dialog box appears to confirm that the un-registration process has succeeded. Click OK to close the dialog box.
Impact of Workaround: The Windows Picture and Fax Viewer will no longer be started when users click on a link to an image type that is associated with the Windows Picture and Fax Viewer.
To undo this change, re-register Shimgvw.dll by following the above steps. Replace the text in Step 1 with “regsvr32 %windir%\system32\shimgvw.dll” (without the quotation marks).
· Microsoft encourages users to exercise caution when they open e-mail and links in e-mail from untrusted sources. For more information about Safe Browsing, visit the Trustworthy Computing Web site.
· Customers in the U.S. and Canada who believe they may have been affected by this possible vulnerability can receive technical support from Microsoft Product Support Services at 1-866-PCSAFETY. There is no charge for support that is associated with security update issues or viruses.” International customers can receive support by using any of the methods that are listed at Security Help and Support for Home Users Web site.
· All customers should apply the most recent security updates released by Microsoft to help ensure that their systems are protected from attempted exploitation. Customers who have enabled Automatic Updates will automatically receive all Windows updates. For more information about security updates, visit the Microsoft Security Web site.
· Protect Your PC
We continue to encourage customers follow our Protect Your PC guidance of enabling a firewall, getting software updates and installing ant-virus software. Customers can learn more about these steps by visiting Protect Your PC Web site.
· For more information about staying safe on the Internet, customers can visit the Microsoft Security Home Page.
· Keep Windows Updated
All Windows users should apply the latest Microsoft security updates to help make sure that their computers are as protected as possible. If you are not sure whether your software is up to date, visit the Microsoft Update Web site, scan your computer for available updates, and install any high-priority updates that are offered to you. If you have Automatic Updates enabled, the updates are delivered to you when they are released, but you have to make sure you install them.
Resources:
· You can provide feedback by completing the form by visiting the following Web site.
· Customers in the U.S. and Canada can receive technical support from Microsoft Product Support Services. For more information about available support options, see the Microsoft Help and Support Web site.
· International customers can receive support from their local Microsoft subsidiaries. For more information about how to contact Microsoft for international support issues, visit the International Support Web site.
· The Microsoft TechNet Security Web site provides additional information about security in Microsoft products.
Disclaimer:
The information provided in this advisory is provided “as is” without warranty of any kind. Microsoft disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. In no event shall Microsoft Corporation or its suppliers be liable for any damages whatsoever including direct, indirect, incidental, consequential, loss of business profits or special damages, even if Microsoft Corporation or its suppliers have been advised of the possibility of such damages. Some states do not allow the exclusion or limitation of liability for consequential or incidental damages so the foregoing limitation may not apply.
Revisions:
· December 28, 2005: Advisory published
Thanks for Marco Russo’s blog, I’ve learned that Microsoft has released an updated version (version 1.5) of the Excel 2002/2003 Add-in for SQL Server Analysis Services. I discussed in details how version 1.0 could be used to generate ad hoc reports from SSAS cubes in my book Applied Microsoft Analysis Services 2005. Although version 1.0 was compatible with SSAS 2005, it was unaware of the new UDM features. For example, it couldn’t differntiate between attribute and multi-level hierarchies. The updated bits target both UDM and SSAS 2000 although not all of the new UDM features (e.g. drillthrough actions) are supported.
Easily overlooked, the version 1.5 of the Excel Add-in represents the first Microsoft OLAP browser that targets UDM. It is not perfect, but it is better than nothing. We can only expect the Excel/UDM integration to improve in Excel 12. So, I feel good about Excel analytics. Unfortunately, I don’t share the same feeling when it comes to integrating UDM with custom applications. In the past, Microsoft Office Web Components (OWC) has fulfilled the role of such a pluggable control. Unfortunately, its COM-based architecture has made it technologically obsolete. At this point, it is not clear what Microsoft plans for OWC and integrating UDM with custom applications.
In my opinion, it is optimistic to expect that all organization will embrace Excel as an OLAP browser. For example, if you develop BI applications, I doubt you will be willing to tell your customers that they need to launch Excel to whip out some cool interactive BI reports. So, I had a dream in which the MS Santa brings me a .NET-based control (let’s call it OlapViewer) which developers can use to integrate .NET applications with UDM. Similar to the ReportViewer VS.NET control, this control could ship with VS.NET. A successor to OWC, the OlapViewer would fully support UDM. It would allow developers to add business intelligence features to custom applications. For example, the developer could programmatically add measures and dimensions to the report. When the end user is done generating the report, she could save the report definition for a later retrieval. How about creating calculated members programmatically? Or, converting the OLAP report definition to a Reporting Services report? Intercepting and possibly changing the MDX query statement?
Alas, for now, this is only a dream. Hopefully, we will see it materialized sometimes next year either from Microsoft or third parties. “The future unknown is”, as the Jedi Master Yodda used to say.
Lately, I’ve run into a few “multi-dimensional” posts in the public SSRS newsgroup. It seems like more and more people target SSAS 2005 cubes as a reporting source. It looks like I am not the only one who have noticed this trend. Brian Welcker has recently commented about the SSRS-SSAS integration in his Fingerpops blog. Here are some notes based on my experiments with the MDX Query Designer.
Limitations
The promise of the MDX Query Designer (implemented by the SSAS team) is giving the report author a tool to produce easily SSAS-based reports without requiring MDX knowledge. Unfortunately, the MDX Query Designer and SSRS integration has left a substantial ground for improvement. For example, it should allow dropping levels of a parent-child dimension on rows to produce the report similar to this one.
I can produce this report in the SSAS cube browser (or another OLAP browser) in seconds. However, the MDX Query Designer doesn’t allow me to request dimension levels side-by-side. I understand that the SSRS can consume only flattened datasets but this doesn’t explain why the MDX Query Designer lacks behind the cube browser which uses OWC to display the results in a two-dimensional format as well. So, my SSAS-SSRS wish #1 is to see the MDX Query Designer support the same functionality as the SSAS cube browser.
Aggregates
There are two ways to request data from an SSAS cube – cellset (hierarchical) and rowset (two-dimensional). SSRS uses the second format which is, of course, more natural for relational reporting. SSRS is not an OLAP browser and it should be viewed as such. Unfortunately, as a result of flattening the data, the SSAS aggregates (All and level aggregates) are lost. You may wonder why on earth you may need the SSAS aggregates instead of re-calculating them using the SSRS Sum(), Avg(), etc. functions. Well, the short answer is that SSAS may go far beyond the standard aggregate functions. For example, the Account dimension in the report above uses custom operators to roll up account categories. For example, income categories add up while expenses subtract. This is why the Balance Sheet Total in the report is $0. Using account charts is a cornerstone of financial reporting. If you can’t bring the SSAS precious aggregates, you have no other choice but to re-invent SSAS with relational reporting which is something you should avoid.
The good news is that SSRS can ask SSAS for the “lost” aggregates. If this is the case, the Aggregates element of the MdxQuery element in the report RDL file won’t be empty, e.g.:
<Aggregates><Aggregate><Levels /></Aggregate></Aggregates>
Here is a trick to make SSRS ask for aggregates:
If all is well, the Aggregates element will be populated and the Aggregate function will return the SSAS aggregate value. However, the problem in the aggregate story is that:
For example, I attempted to produce the above report by manually creating the MDX statement. While I succeeded to some degree, I wasn’t able bring the aggregates. Neither I was able to produce the sample report with the groups I wanted.
In conclusion, SSAS and SSRS could be a winning combination for OLAP and relational reporting. I hope that future service packs and releases will improve the SSRS and SSAS integration and preserve the fidelity of the cube data.
This weekend, I was working on the second part of my Report Viewers article for DevX. As usual, it all started with an innocent idea which turned out into a whole-day affair. I wanted to demonstrate how to integrate the VS.NET 2005 web ReportViewer with a server report that takes a multi-value parameter. Of course, to save time to lay out the presentation layer, I decided to use absolute positioning for my web controls. Much to my surprise, the ReportViewer wouldn’t expand the multi-value parameter. It would helplessly repost the page each time I would click on a report parameter.
So, I got on an ambitious and arduous quest to find out what I am doing wrong. The most puzzling finding was that the Report Manager (which behind the scenes uses the web Report Viewer) would render the report just fine. This left me utterly perplexed and convinced that I am doing something terribly wrong. And I’ve started changing stuff around. I fixed a bunch of other issues, including some mysterious DCOM events that were logged in the Event Viewer, but the ReportViewer won’t budge.
Finally, I created a new page, dropped the ReportViewer, and configured at design time, and sure it rendered the report just fine. It turned out that there is a bug with the ReportViewer and multi-value parameters because they use absolute positioning as well. As far as I know, the only workaround for now is to change the hosting page layout to use flow positioning instead.
So, stay away from absolute positioning until this ugly bug is fixed.
At last, my book “Applied Microsoft Analysis Services 2005” is available for purchase from the retail outlets, including Amazon and Barnes & Noble. This completes the arduous and time-consuming book lifecycle which readers are probably unaware of. Here is how it goes… Once the author hands off the final manuscript to the publisher, the publisher sends the electronic copy to the printer. In the case of my book, it took one month for the printer to produce the book. Next, the printer ships the book to the distributor. Then, the distributor sends the books to the wholesalers (Ingram and Baker & Taylor).
Retailers, such as Amazon and B&N, get the books from the wholesalers. So, from the time the book is received from the distributor, it takes another 30 or so days until the books fill the channels. So, setting the book publication date on Amazon is nothing more than an educated guess about events that are completely out of the publisher’s control. This is only half of the story, of course. If the retailers don’t sell the book in six months, the retailers return the books to the distributor and the book is re-stocked. But this won’t happen to my book, right? 🙂
