-
Scale-Out Querying with Analysis Services Article
August 1, 2007 / No Comments »
I've come across a good best practices article about scaling out SSAS by Denny Lee and Nicholas Dritsas. It describes how to set up a load-balanced scalable querying environment for Microsoft SQL Server 2005 Analysis Services so that you can handle a large number of concurrent queries to your Analysis Services servers.
-
July CTP of SQL Server 2008 Is Out
August 1, 2007 / No Comments »
July CTP of SQL Server 2008 (Katmai) is out. On the BI side of things: New SSRS Report Engine "Improvements represent the two major infrastructure changes for Reporting Services. Reporting Services enhances the processing engine and rendering extensions to enable new functionality, such as Tablix support, and scalability as well as remove the dependency on IIS. Additionally, new report designer and configuration tool are provided that improve usability and workflow for RS customers." Analysis Services Time Series This improvement adds a new time series forecasting algorithm (ARIMA: Auto Regressive Integrated Moving Average) to the data mining algorithm suite that provides more stable long term predictions.
-
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.
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.
