Archive for the ‘SQL Analysis Services’ Category

FW: Custom Rounding and Truncation of Numbers in MDX

August 6, 2009 Leave a comment
Categories: SQL Analysis Services

SSAS 2008 Dimension Security Issue

July 29, 2009 2 comments
Today, I’m modifying the cube dimension security. After processing the cube successfully, everytime I try to connect to the cube, i get the following error message: The XXX attribute in the YYY dimension has generated dimension security expression that is not valid, shown as below.
After searching the Internet, I found Chris Webb wrote the following statement in his blog (!7B84B0F2C239489A!1563.entry):
"Another thing to note is that if you are using role-playing dimensions in your cube, you will need to set dimension security on the cube dimensions rather than the underlying database dimension. For example, in Adventure Works, if you go to the Date dimension in the list of database dimensions (in the dimensions drop down on the Dimension Data tab of the Roles editor, database dimensions are listed first, then cube dimensions second) and set dimension security say to allow access to one year in the Calendar Year attribute, you’ll get an error like "The ‘Calendar Year’ attribute on the ‘Ship Date’ dimension has a generated dimension security expression that is not valid". Setting the same permissions on each of the role-playing dimensions in the cube based on Date, ie Ship Date, Delivery Date and Date does not cause a problem. "
This is exactly the causse of my problem. I have to set the dimension security on cube dimension instead of database dimension to make it work.
Categories: SQL Analysis Services

FW: Talking about Dynamic sets

Nice article. It helped me to understand the dynamic sets much better.!5BB64CF526505D83!332.entry

Categories: SQL Analysis Services

FW: Talking about Implementing histograms in OLAP

Categories: SQL Analysis Services

FW: Excel 2010 – a personal sneak preview

 Frederik Vandeputte wrote a nice review of Excel 2010 as the front-end BI tool. You can find the article at:  

It looks like Excel 2010 supports SSAS 2008 Dynamic Named Sets and Writeback. Awsome!
I’m looking forward to its release.
Categories: SQL Analysis Services

Dynamic Named Sets in SQL Server Analysis Service 2008

Categories: SQL Analysis Services

SSAS 2008 Kerberos Issue with Windows Server 2008

May 7, 2009 1 comment

Here, here and here… After 2 days’ debugging and tracing, finally, I found this issue is a confirmed bug:  Errors may occur after configuring Analysis Services to use Kerberos authentication on Advanced Encryption Standard Aware Operating Systems (e.g. Vista, Windows 2008). Errors might look like one of the following:

·         The connection either timed out or was lost.

·         Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.

·         An existing connection was forcibly closed by the remote host


 I had the same issue with SSAS deployment. In BIDS, when I deploy the project using IP address, it is fine. However, when I try to use the server name, the error message “The connection either timed out or was lost” appears.

In addition, I had another extremely weird problem. When I use SSMS to connect to the SSAS server and browse one of the cubes, the following error happened. However, when I change the cube name from XXXX to XXXX1 (actually, change to anything), the cube works fine.


However, if I connect the SSAS sever by using its IP address instead of the server name, the cube is working as well. I suspect this issue is related to the Kerberos bug in Windows Server 2008.

P.S.  Here is the Software build:

Windows Server Enterprise 2008 with SP1 (Terminal Server, where I run BIDS and SSMS)

SQL Server 2008 Enterprise with SP1 (SSAS Server)

The best work around for now is to deploy SSAS onto a Windows 2003 Server. As Kerberos does not support IP Address, using Excel Services connecting to SSAS 2008 in SharePoint 2007 with Kerberos delegation is impossible at the moment. Microsoft has been aware of this issue and I hope they can release the fix ASAP.

Categories: SQL Analysis Services