-
Timeout Quest
August 16, 2006 / No Comments »
I was on an interesting quest today. I had to troubleshoot why some long-running SSRS reports time out after a fixed period of time. An SSRS report can time out for at least three different reasons. First, the dataset query could have been set to time out (see DataSet Query tab). The default query timeout is 30 seconds. You can set the query timeout to 0 to prevent the query from timing out. Second, the entire report may be set to time out . By default (see the Site Settings page in the Report Manager), all reports are set to time out after 1,800 seconds (30 minutes) but the report timeout can be overridden at the report level by using the report execution properties. When the report timeout is up, the Report Server simply terminates the report. Finally, if your application calls down to the RS Web service to run...
-
Installing Reporting Services 2005 on Named Instance
August 2, 2006 / No Comments »
Here are the steps I’ve recently followed to install successfully SRRS 2005 on a named instance. Follow the steps in the “To Install Reporting Services Side-by-Side With an Earlier Version” section in BOL. Navigate to http://localhost/reportserver/. If you get access denied error to rsreportserver.config (or another folder/fiile), add the SSRS Windows Services account (e.g. NT AUTHORITY\SYSTEM) and the Web service (ASPNET) to the SQLServer2005ReportServerUser$<machine>$<instance> and SQLServer2005ReportingServicesWebServiceUser$<machine>$<instance> Windows groups respectively. Try to open Report Manager. If you get an error that the Report Server cannot open a connection to the database, go back to the Reporting Services Configuration tool. Switch to the Database Setup tab. Select the Server Name and Database Name (ReportServer) and click the Script button. In the Create Scripts dialog, select the Grant Rights Script option.Type in the Web service account (e.g. ASPNET) in the User Name field and select the Windows User Account checkbox. Click OK to...
-
UDM Many-to-many Relations and "AND" queries
July 31, 2006 / No Comments »
Recently I got an interesting question about querying UDM many-to-many relationships. For example, in my book I demonstrated how you can implement an UDM model (Bank cube) that has a many-to-many relationship between bank customers and their accounts. That's because a customer could have more than one account and an account can belong to more than one customer (a joint account). But what if you want to find only the joint accounts that are owned by any two customers? The following query returns the accounts owned by Bob and Alice: SELECT NON EMPTY {[Measures].[Balance]} ON COLUMNS, Intersect ( Exists([Account].[Account Number].[Account Number], [Customer].[Full Name].&[Bob], "Customer Account"), Exists([Account].[Account Number].[Account Number], [Customer].[Full Name].&[Alice], "Customer Account") ) ON ROWS FROM Bank The trick is to evaluate the sets over the hybrid "Customer Account" measure group which is actually a dimension table but plays a role of a measure group. The Exists function...
-
OWC to Bite the Dust
July 26, 2006 / No Comments »
Just learned from the David Gainer's post, that Microsoft has made a decision to retire Office Web Components (OWC) in favor of Excel Services. This is definitely sad news for me. While I agree that Excel Services could fulfill some of the integration scenarios mentioned in the comments for that post, I can think of at least two scenarios where this won't be the case: You need a cost effective and easy way to provide OLAP features in your applications. The Office Server pricing model has not been announced yet but it could be intimidating for small companies or application vendors. Not to mention that SharePoint requires Windows Server. You need to embed an OLAP browser in your Windows Forms application. Currently, I don't know of a way to embed an Excel spreadsheet on a Windows Form (VSTO, Excel Services, VBA, etc. don't support this). Not to mention that if browsing...
-
July Refresh of SQL Server 2005 BOL and Samples Released
July 23, 2006 / No Comments »
Download an updated version of the documentation and samples for Microsoft SQL Server 2005.
-
AdventureWorks DW Workspaces Templates Available for Microsoft Office Business Scorecard Manager
July 23, 2006 / No Comments »
These samplesThese samples will get you up and running with Microsoft Office Business Scorecard Manager 2005 utilizing the sample OLAP database provided with Microsoft SQL Server and Analysis Services 2005 called AdventureWorks DW. The purpose of the AdventureWorks DW database is to provide a sample database for exploring the possibilities of OLAP. will get you up and running with Microsoft Office Business Scorecard Manager 2005 utilizing the sample OLAP database provided with Microsoft SQL Server and Analysis Services 2005 called AdventureWorks DW.
-
Swapping the RS Catalog
July 18, 2006 / No Comments »
Sometimes, you may need to swap the RS catalog database. For example, you may need to clone the catalog database from a production machine to troubleshoot an issue. One way to do so is to replicate the report items (report definitions, data sources, images, etc.). Another, and perhaps faster, way is to copy the report catalog database and redirect the Report Server temporarily to the new catalog by using the handy Reporting Services Configuration Manager (new with RS 2005). The following steps worked for me to redirect my local SSRS instance (SP1+hotfix) to a different catalog (from SSRS 2005 RTM).Use the SQL Server Management Studio to create a new database, e.g. ReportServerTest. Create a new database ReportServerTestTemp to serve as the Report Server Temp database. Back up the production database you need to troubleshoot. Restore the production database to ReportServerTest. Make sure to rename the database files by flipping to the Options tab...
-
E-mail Unleashed
July 16, 2006 / No Comments »
Many, many apologies if you have received tons of e-mail from me (Prologika) during the weekend. The good news is that it seems like I’ve managed to find a way to send e-mail notifications from my forum. The bad news is that I didn’t notice that I had some 500 e-mails stuck in the queue which the e-mail job sent out. Oops!
-
UDM Data Islands
June 28, 2006 / No Comments »
As I mentioned in my TechEd Memoirs post, Microsoft is scaling back from the “super” cube approach in favor of more granular data “islands”. Since this took me by suprise, I approached the SSAS team for clarification. Here are the answers I managed to get: 1. What performance benefits could be realized by splitting UDM in data islands? At this point, we don't have detailed performance numbers we can share. But to give an indication - a cube that had around 15 measure groups, and a very large number of attributes (over 100 cube dimensions, many with more than 100 attributes) showed a performance improvement of about 6 fold when split into separate cubes (one per measure group), for queries that only referenced a single measure group. The main benefit came from the reduced cube space - the individual cubes only needed to include dimensions related to the measure group, and...
-
You’ve been Deadlocked
June 24, 2006 / No Comments »
If you’ve been using VS.NET 2005 for a while chances are that your debugging session could have been spectacularly crashing just when you thought you were so close finding that elusive critical bug. This situation may have manifested with the following exception: ContextSwitchDeadlock was detectedMessage: The CLR has been unable to transition from COM context <some nasty hex number> to COM context <another nasty hex number> for 60 seconds. The thread that owns the destination context/apartment is most likely either doing a non pumping wait or processing a very long running operation without pumping Windows messages. This situation generally has a negative performance impact and may even lead to the application becoming non responsive or memory usage accumulating continually over time. To avoid this problem, all single threaded apartment (STA) threads should use pumping wait primitives (such as CoWaitForMultipleHandles) and routinely pump messages during long running operations. Usually, you can’t...
Training
We offer onsite and online Business Intelligence classes! Contact us about in-person training for groups of five or more students.For more information or to register click here!
