-
Empty Affairs
July 13, 2007 / No Comments »
If you have a cube with large dimensions you may have come across the following error: The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples. The culprit is the NON EMPY (or NonEmpty), as mentioned in the Chris Webb's blog and Mosha Passumansky's blog. It turns out that NonEmpty simply gives up when the number of tupples in the cross-join members exceeds 4GB. The problem is all Microsoft SSAS clients (Excel, Reporting Services, Report Builder, etc.) are blissfully unaware of the trap to come and would happily cross-join dimension members as you add more dimensions to the report. Take for example the following (simplified) query produced by the Report Designer when the report shows the customer name and its accounts: SELECT NON EMPTY { [Measures].[Sales] } ON COLUMNS, NONEMPTY { ([Customer].[Customer].[Customer].ALLMEMBERS * [Account].[Customer Account Number].[Customer Account Number].ALLMEMBERS)} ON ROWS FROM <some cube> WHERE...
-
SQL Server 2008 (Katmai) to Launch February 28th 2008
July 12, 2007 / No Comments »
From the latest MS press release "In anticipation for the most significant Microsoft enterprise event in the next year, Turner announced that Windows Server 2008, Visual Studio 2008 and Microsoft SQL Server 2008 will launch together at an event in Los Angeles on Feb. 27, 2008, kicking off hundreds of launch events around the world." Whoa, I didn't expect Katmai so soon. Well, Euan Garden is telling us that a marketing launch doesn't mean all that much after all [;)]. It will probably be a few more months before Katmai RTMs.
-
SSRS Setup Woes
July 12, 2007 / 1 Comment »
I was setting up Reporting Services 2005 today on a clean Windows XP machine and I came across the infamous error: The report server has encountered a configuration error. See the report server log files for more information. (rsServerConfigurationError). Access to the path 'c:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer\RSReportServer.config' is denied. Here is how I fixed this horrible problem. First, I open the Computer Manager and verified that the following two groups exist: SQLServer2005ReportingServicesWebServiceUser$<machinename>$MSSQLSERVER and SQLServer2005ReportServerUser$<machinename>$MSSQLSERVER. Next, I used the Windows Explorer to verify that these groups have read ACL permissions to the Report Server folder (C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer) by right-clicking on the this folder and checking the Security tab. Finally, I verified that the following users have been assigned to these groups. SQLServer2005ReportingServicesWebServiceUser$<machinename>$MSSQLSERVER should have the ASP.NET account assigned to it. In Windows XP, the default ASPNET account is <MachineName>\ASPNET. In Windows Server 2003 and Vista, this should...
-
How to install SQL Server 2005 Reporting Services on a Windows Vista-based computer
July 4, 2007 / No Comments »
I spent a couple of hours today trying to get SSRS 2005 working on Vista. I was getting the infamous IIS 500 error when browsing the Report Manager (http://localhost/reports) and Report Server (http://localhost/reportserver) virtual roots. I triple-verified the Brian Welcker's recommendations and the Vista considerations in the SQL Server 2005 readme file. I couldn't figure what's going on especially given the fact that I installed SSRS on my home Vista machine with no problems. The only difference this time was that I performed file-only install of SSRS because I wanted to use an existing report catalog. In a moment of a Google eureka, I came across the How to install SQL Server 2005 Reporting Services on a Windows Vista-based computer KB article. It turned out that the Reporting Services Configuration Utility put the IIS applications in the default application pool which in IIS 7.0 is running in Integrated Managed Pipeline...
-
Analysis Services Query Performance Top 10 Best Practices
June 25, 2007 / No Comments »
I came across this little gem which I haven't noticed so far. A nice summary of the 10 things you should do to optimize the UDM query performance. The best one is kept for last - scale out when you can no longer scale up. So true, if I could only convince customers to do so J
-
Protect UDM with Dimension Data Security
June 25, 2007 / No Comments »
SQL Magazine published the first part of my article Protect UDM with Dimension Data Security in its July issue. This article explains the fundamentals of dimension data security. I am working on making the article available for public access (as of now, it requires subscriber-level access). The article code can be downloaded from the publisher's website and from my website. I am currently writing the second part (tentatively named Protect UDM with Dynamic Dimension Security) whose focus is dynamic dimension security. It will present two implementation approaches for implementing dynamic dimension security which are harvested from a real-life project: factless fact table and integrating with external security service.
-
Trust Relationships
June 11, 2007 / No Comments »
If you use VS.NET deployment to deploy your SSAS project from one domain to another, you may get the following obscure error message on deploy. The trust relationship between the primary domain and the trusted domain failed. This may happen even if your workstation and the deploy server are on the same domain. The most likely reason for this error is that you have added Windows groups or users from the old domain as members to SSAS role definitions. To fix the error clear the role membership list.
-
Aggregate Rows and SP2
June 10, 2007 / 2 Comments »
Back to my When (null) is not Null post, there is a undocumented breaking change in SQL Server 2005 SP2 which you should be aware of. In response to the customer demand, SP2 treats aggregate rows as detail rows. As a result, if the report doesn’t use the Aggregate() function, the report will show all rows. For those using the Aggregate function, there will be no change.
-
TechEd 2007 US Memoirs
June 10, 2007 / No Comments »
I am back from an one-week vacation to Clearwater, Florida followed by TechEd 2007 US in Orlando. Overall, nothing earth – shattering on the BI arena from TechEd 2007. Product groups are between releases so most of the content was SQL Server 2005-based with a few sneak previews of some of the good stuff to come in Katmai. My presentation attracted a fairly large crowd. Almost 500 folks joined my presentation "Applied Microsoft Reporting Services 2005" which was scheduled for the first breakout slot after the keynote on Monday. Thank you! I've made the source code and slides available for download on my website. Courtesy to Marco Russo, I've also uploaded a couple of photos to capture the moment of glory. Now that I can open my mouth about some of the cool features coming up in the Katmai release of Reporting Services that were officially announced, here are the...
-
A Couple of Report Builder – UDM Hacks
May 25, 2007 / No Comments »
If you target the SSRS Report Builder as an UDM client you may find the following two workarounds (aka hacks) useful. Both hacks require manually updating the SMDL definition. Alas, so much about model auto-generation L. Multi-grain Relationships Don't Appear If a dimension joins a measure group at a higher grain the corresponding Report Builder role is excluded from the model. Steps to repro: Open the Adventure Works SSAS project in the BIDS cube designer and switch to the Dimension Usage tab. Note that the Date dimension joins the Sales Targets measure group at the Calendar Quarter level (not the Date level). That's because the FactSalesQuota (the fact table Sales Targets is based on) stores the sales quotas at the quarter level in AW. Generate a report model from a data source pointing to the Adventure Works SSAS database Open the Report Builder and connect to the Adventure Works cube....
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!
Books
Learn Power BI at your own pace with our latest book Applied Microsoft Power BI. Targeting information workers, data analysts, pros, and developers, It is designed as an easy-to-follow guide for learning how to implement BI solutions spanning the entire personal-team-organizational BI spectrum.
