Did you know Atlanta has a B/I User Group? This new group is a special extension to Atlanta MDF that focuses on providing monthly BI content and discussions for the hard working people of Atlanta. The first meeting of the B/I SIG of the Atlanta MDF is tonight, 6:30 pm at Matrix Resources in Perimeter. (Did I mention that Matrix is a wonderful sponsor of us SQL folks here in Atlanta?) The topic is going to be something I keep meaning to try out: PowerPivot. Come on ouy and help get this new group off to a great start.
Here are all the vital details for you:
Topic: Self-service B/I with PowerPivot Date: Monday, August 23, 2010 Speaker: Teo Lachev
I’ve got an oddball situation going on here (I know what you’re thinking: Par for the course). I’ve built out a pair of new Windows Server 2008 R2 \ SQL Server 2008 SP1 CU5 machines. Both are running great and returning data faster than anything we’ve ever seen before except for one small problem: One of the servers can’t see it’s own cubes. The cubes are there and everything. I can connect to them with BIDS to deploy updates, SSMS to process them, and I can even query the cubes and get results back in Excel. The server itself just can’t see them.
Here’s what it looks like if you’re logged into the machine directly:
And then here’s what it looks like if you connect to that same server from my desktop or any other server:
Another weird thing is that the other server that was built out the same way at the same time can see it’s own cubes just fine. It’s just this one server that can’t see it’s own cubes.
I’ve only heard of one other person who has seen this behavior so the whole point of this blog post is just to see if anyone else is running into this issue. If so please comment and we can get a connect issue filed if it’s prevalent enough reproduceible.
* Please Note: The names have been changed to protect the innocent servers under my corrupting influence control.
** Please take a look at the comments below:
*** As a work-around you can Right-Click on the SSMS icon and select > Run as Administrator to get SSMS to see it’s own cubes: “If you skip step in the setup where it asked you to add current user to AS server admin role, then you would need to run SSMS as administrator.”
*Please note that this post is a work in progress and I’m still waiting on more answers but I wanted to get the information out there in case anyone else is running into this issue too:
OK that title might be a little bit over the top for what I’m dealing with but if you were in my situation that’s the exact kind of article title that you have been looking for. I have been trying to migrate a set of cubes from Analysis Services 2000 to SQL Server Analysis Services 2008 and have run into a number of glitches but the most annoying of them was this one…
File system error: While attempting to read information from disk, a read error occurred for physical file: ?D:MSSQLMSAS10OLAPTempMSMDCacheRowset_1868_258c_nompm.tmp, logical file: . Errors in the OLAP storage engine: An error occurred while the ‘generated attribute’ attribute of the ‘MyDimension’ dimension from the ‘MyCubes’ database was being processed.
After downloading and installing Cumulative update package 5 for SQL Server 2008 Service Pack 1 I once again tried to Process and came up with the exact same error message.
Unfortunately, I’ve now installed CU5 for SP1 on SQL 2008 and I was still getting that same error message. Here’s what’s going on. According to the SQL CSS support person that I spoke to, this message is actually telling me that I’ve run out of space in the temp file that SSAS has created for my cube while I was trying to process it. My problems are (well, problesms as it relates to this cube migration, not all my problems in general ) is that A) it’s a really ugly way of telling me that and B) my understanding is this is only supposed to happen to string stores when the Key is a string. This dimension that I am working with has an int for a key, so apparently it’s also possible to happen for the Attribute Name, not just the Attribute Key.
As it turns out, I shouldn’t have run into this limitation in the first place though if settings had been kept like they were in AS2000. What had happened was that when I used the Cube Migration Wizard it had decided to process my dimensions “ByTable”. To correct this what I needed to do was go into every dimension that gets processed in the cube and set it to process “ByAttribute”.
After that I obviously had to build, deploy and process my cubes but with that change to each and every dimension in my cube I was finally able to start processing all the way through again. There are a few other issues that that I’ve run in to but I will keep this post short and cover those in other blogs. One thing that I wi ll note before I go though is that I have noticed a few people on MSDN running into this issue with string KeyColumns but in my case I was using an interger for my KeyColumn so you can get that error with an INT too.
“From a processing perspective, it is a best practice to assign a numeric source field to the KeyColumns property rather than a string property. Not only can this reduce processing time, in some scenarios it can also reduce the size of the dimension. This is especially true for attributes that have a large number of members, i.e., greater than 1 million members.”
You can read more about what this change means in the help files and get more info on increasing performance in the ‘performance guide’ (you’ll want to skip ahead to page 53).
Earlier this week I got the chance to do some SSAS work for the first time in a long time which made me very happy. My task was to migrate some cubes from Analysis Services 2000 to SSAS 2008. I was given a new Windows 2008 R2 Server with SQL Server 2008 SP1 (Slipstream installed) to work on and when I got started I got an error pretty much right away. I did a quick search on the internet and didn’t find much info on the error at all so I decided to cancel out and redo the steps so that I could document the problem. When I went to recreate the problem I found inadvertently kicked off the process from my local machine instead of the server like I had the time before and this time everything worked; no error. I knew right away what the problem was and was shocked that my search hadn’t turned up anything.
I received this error on the server but not on my local machine:
“ Source server: The Migration Wizard cannot continue because the DSO client is not installed on this computer. Close the current Migration Wizard, install the DSO client on the computer, and then try running the Migration Wizard again. ”
Like muscle memory I knew that I had forgotten to install the Backwards Compatibility kit onto the new server that I was working with. Now most of you may not get this error because you’ve already had to install this pack to migrate or work with DTS packages, but for those of you that may not have needed to touch a DTS package from inside of SQL Server Management Studio (SSMS) you will end up right where I was with this server.
And you’ll want to go down the page until you get to:
“Microsoft SQL Server 2005 Backward Compatibility Components
The SQL Server Backward Compatibility package includes the latest versions of the Data Transformation Services 2000 runtime (DTS), SQL Distributed Management Objects (SQL-DMO), Decision Support Objects (DSO), and SQL Virtual Device Interface (SQLVDI). These versions have been updated for compatibility with both SQL Server 2005 and SQL Server 2008 and include all fixes shipped through SQL Server 2000 Service Pack 4 (SP4) and SQL Server 2005 SP2.