*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. I searched online and saw that there is a KB that describes this same type of error message: http://support.microsoft.com/kb/970184/ … And went on to say ” The fix for this issue was first released in Cumulative Update 2 for SQL Server 2008 Service Pack 1”. 970315 (http://support.microsoft.com/kb/970315/ ) 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).
November, 2009:
Using PowerShell to Archive Twitter Feeds–Preserving the wisdom of Paul Randal
If you didn’t make it out to the PASS Summit then you missed out on a lot of great content. If you’re not on twitter and following some of SQL Server’s best and or rowdiest than you’re missing a lot. And if you didn’t follow Paul Randal while he was tweeting from the session he and Kimberly L. Tripp presented then you missed a great refersher course on SQL fundamentals that are critical to your success in this field. The challenge of dealing with great content burried in tweets is figuring out how to capture that information in a way you can reference it later. I took a queue from of Bruce Payette’s book Windows PowerShell in Action and decided I’d try tackling this problem. I started by visiting the PaulRandal twitter page. If you just wanted to download his last 20 tweets you could just use the RSS feed: http://twitter.com/statuses/user_timeline/36394357.rss . How do you get the others? I got around the 20 tweet limitation by favorite-ing Paul’s tweets starting at the beginning of the conference and then working my way toward the current date: Every time I selected 20 of Paul’s tweets as a favorite I just ran this PowerShell script and appended it to a file that I was storing them in. ([xml] (new-object net.webclient).DownloadString(“http://twitter.com/favorites/76699854.rss”)).rss.channel.item | format-table -autosize description, pubDate, link >> PaulRandal.txt After that I used SSIS to load them in a SQL db and help me clear out a few of the tweets that would be of little use without the other half of the conversation and what not. I did learn a couple of interesting PowerShell quirks during this process. The first is that your output is truncated based on your available screen size. I began writing this script while running the PowerShell command in a 1280 x 1024 screen. When my output was restricted I took a chance and tried running it on my laptop where the resolution is ste to 1920 x 1200. Running the download at a higher resolution was good enough to solve problem. (I haven’t yet found out why PowerShell kept trying to truncate in the first place. If you have ideas on this feel free to use the comments.) The second was seeing the real differenece between the “>>” and “>” operators. By using a double “>>” instead of a single “>” I was able to append my output instead of completely replace it every time I ran my script. Next time I’ll figure out how to pump it straight into a table so that I can skip the SSIS step. Finally here’s what I ended up with: Handle Tweet TweetDate TweetLink HashTags PaulRandal: And we’re on – pretty packed room here in our pre-con – just shy of 100. #sqlpass 11/2/09 4:38 PM http://twitter.com/PaulRandal/statuses/5366919942 #sqlpass PaulRandal: #sqlpass Let’s make the hashtag for our indexing precon #ktprecon – I’ll watch both. 11/2/09 4:56 PM http://twitter.com/PaulRandal/statuses/5367318129 #sqlpass #ktprecon PaulRandal: Question from #sqlpass: Ever a use for heaps? A: Not really. E.g. incoming clickstream temp storage 11/2/09 4:58 PM http://twitter.com/PaulRandal/statuses/5367371468 #sqlpass: PaulRandal: #sqlpass Either that or Kimberly’s jokes really *are* bad, and I’m just laughing because I have to (being married to her) 11/2/09 5:01 PM http://twitter.com/PaulRandal/statuses/5367439309 #sqlpass PaulRandal: #ktprecon #sqlpass Q: why do SELECT COUNT(*) of a heap with fwded records generate extra IOs? 11/2/09 5:14 PM http://twitter.com/PaulRandal/statuses/5367732804 #ktprecon #sqlpass PaulRandal: #ktprecon #sqlpass A: because the Storage Engine will only process fwded records when it finds them from a fwding record 11/2/09 5:14 PM http://twitter.com/PaulRandal/statuses/5367744740 #ktprecon #sqlpass PaulRandal: @DBA_hole #ktprecon #sqlpass Yes (unless you specifically ask for a nonclustered primary key) 11/2/09 5:19 PM http://twitter.com/PaulRandal/statuses/5367850801 #ktprecon #ktprecon PaulRandal: @DBA_hole #ktprecon #sqlpass But not a drop/recreate. It’s a create of clustered index + drop of heap. Semantic, but important difference. 11/2/09 5:20 PM http://twitter.com/PaulRandal/statuses/5367869544 #ktprecon #ktprecon PaulRandal: #ktprecon #sqlpass Our wonderful attendees… http://pic.gd/b17455 11/2/09 5:24 PM http://twitter.com/PaulRandal/statuses/5367969054 #ktprecon #sqlpass PaulRandal: #ktprecon #sqlpass Kimberly in mid-sentence… isn’t she cute? http://pic.gd/ba2662 11/2/09 5:26 PM http://twitter.com/PaulRandal/statuses/5368012672 #ktprecon #sqlpass PaulRandal: #ktprecon #sqlpass Ola Hallengren’s *wonderful* db maintenance script: http://bit.ly/3R5OUv 11/2/09 5:35 PM http://twitter.com/PaulRandal/statuses/5368219637 #ktprecon #sqlpass PaulRandal: @sqlinsaneo They’re not real seagulls – they’re MS-controlled robots #sqlpass 11/2/09 5:37 PM http://twitter.com/PaulRandal/statuses/5368256080 #sqlpass PaulRandal: @buckwoody I’m trying to avoid looking at you – too embarrassed after what happened to us last night in that phone booth #sqlpass 11/2/09 5:42 PM http://twitter.com/PaulRandal/statuses/5368367982 #sqlpass PaulRandal: #ktprecon #sqlpass Alternative to shrink: http://bit.ly/43PQI 11/2/09 5:54 PM http://twitter.com/PaulRandal/statuses/5368638484 #ktprecon #sqlpass PaulRandal: @DBA_hole #ktprecon #sqlpass Depends on your I/O subsystem, filegroup layout, HA/DR strategy… may be more mgmt probs than gains 11/2/09 6:04 PM http://twitter.com/PaulRandal/statuses/5368853068 #ktprecon #ktprecon PaulRandal: #sqlpass Please refill the coffee in the speaker room (and have someone check regularly…) thanks! 11/2/09 6:38 PM http://twitter.com/PaulRandal/statuses/5369614171 #sqlpass PaulRandal: @DonKirkham #sqlpass backup/restore does not reclaim space (or do anything else to the db). Only way to reclaim is some form of shrink. 11/2/09 6:41 PM http://twitter.com/PaulRandal/statuses/5369662274 #sqlpass PaulRandal: @DonKirkham See http://bit.ly/43PQI for an alternative for you. 11/2/09 6:51 PM http://twitter.com/PaulRandal/statuses/5369891151 NULL PaulRandal: @ktegels Because an existing filegroup has data files – a FS filegroup is a pointer to the root of the NTFS data container 11/2/09 6:52 PM http://twitter.com/PaulRandal/statuses/5369909736 NULL PaulRandal: #ktprecon #sqlpass How expensive are page splits in terms of transaction log? http://bit.ly/MqUgU 11/2/09 7:03 PM http://twitter.com/PaulRandal/statuses/5370157274 #ktprecon #sqlpass PaulRandal: #ktprecon #sqlpass Do page splits ever roll back? No: script to prove it: http://bit.ly/2IgMM3 11/2/09 7:04 PM http://twitter.com/PaulRandal/statuses/5370177946 #ktprecon #sqlpass PaulRandal: #ktprecon #sqlpass Demo scripts (and companion content for the internals book chapter) are at http://bit.ly/42zJ4c 11/2/09 7:10 PM http://twitter.com/PaulRandal/statuses/5370304102 #ktprecon #sqlpass PaulRandal: Audience perspective: RT @joewebb: Live from #ktprecon at #sqlpass. http://twitpic.com/o13um 11/2/09 7:12 PM http://twitter.com/PaulRandal/statuses/5370346695 #ktprecon #sqlpass PaulRandal: #ktprecon #sqlpass Kimberly dissing DBCC commands while I’m on stage next to her? Not a smart move! 11/2/09 7:17 PM http://twitter.com/PaulRandal/statuses/5370471853 #ktprecon #sqlpass PaulRandal: #ktprecon #sqlpass OH from Kimberly: “The key points are…. really just the key points.” Very astute observation there dear 11/2/09 7:45 PM http://twitter.com/PaulRandal/statuses/5371086548 #ktprecon #sqlpass PaulRandal: @ChiragRoy #ktprecon #sqlpass It depends Wait until she talks about indexing strategies this afternoon. 11/2/09 7:48 PM http://twitter.com/PaulRandal/statuses/5371143932 #ktprecon PaulRandal: @AndyLeonard #sqlpass #ktprecon She’ll never see it – she doesn’t tweet much – I’m safe 11/2/09 7:49 PM http://twitter.com/PaulRandal/statuses/5371159601 #sqlpass #ktprecon PaulRandal: #sqlpass People.. the answer is always “it depends”. Apart from if the question is ‘should auto-shrink be turned on?’. 11/2/09 7:58 PM http://twitter.com/PaulRandal/statuses/5371365926 #sqlpass PaulRandal: @datachick My other favorite answer is “12″ and then watch the mystified expressions. #sqlpass 11/2/09 8:01 PM http://twitter.com/PaulRandal/statuses/5371421542 #sqlpass PaulRandal: #sqlpass L U N C H T I M E! #ktprecon We’ll be back to cover more incredible indexing info (and on-stage (verbal) spousal abuse! 11/2/09 8:03 PM http://twitter.com/PaulRandal/statuses/5371476694 #sqlpass #ktprecon PaulRandal: @DonKirkham Ah – no solution for that – maybe a 3rd-party backup/restore tool? 11/2/09 8:52 PM http://twitter.com/PaulRandal/statuses/5372567849 NULL PaulRandal: #ktprecon #sqlpass Once more into the breach, dear friends, once more…. back after lunch with the KT Indexing Show 11/2/09 9:05 PM http://twitter.com/PaulRandal/statuses/5372866600 #ktprecon #sqlpass PaulRandal: #ktprecon #sqlpass Don’t INCLUDE LOB columns in NC indexes. It creates a whole extra copy of the LOB data. Not good. 11/2/09 9:10 PM http://twitter.com/PaulRandal/statuses/5372989895 #ktprecon #sqlpass PaulRandal: #ktprecon #sqlpass And don’t put an index on every column. One of the worst things you can do. 11/2/09 9:13 PM http://twitter.com/PaulRandal/statuses/5373037864 #ktprecon #sqlpass PaulRandal: @SQLCraftsman Oh yeah. Confusing unto and into always gets me into unto-ward trouble. 11/2/09 9:16 PM http://twitter.com/PaulRandal/statuses/5373123933 NULL PaulRandal: #ktprecon #sqlpass What is a ‘seekable’ index? An index with keys that can be used in a left-based subset to match a search argument. 11/2/09 9:23 PM http://twitter.com/PaulRandal/statuses/5373277154 #ktprecon #sqlpass PaulRandal: #ktprecon @sqlpass What order should the NC index key columns be in? It depends… 11/2/09 9:24 PM http://twitter.com/PaulRandal/statuses/5373306090 #ktprecon PaulRandal: #ktprecon #sqlpass So far Kimberly’s answered “It depends” 15 times… 11/2/09 9:26 PM http://twitter.com/PaulRandal/statuses/5373359667 #ktprecon #sqlpass PaulRandal: #sqlpass Q I get asked: query perf dropping, what happened? If it slowly drops, fragmentation. If it drops off a cliff, stats. Commonly. 11/2/09 9:35 PM http://twitter.com/PaulRandal/statuses/5373562535 #sqlpass PaulRandal: @ChiragRoy #ktprecon #sqlpass Parts of database must all be at the same point in time as primary filegroup… no problem. 11/2/09 10:08 PM http://twitter.com/PaulRandal/statuses/5374338657 #ktprecon PaulRandal: #sqlpass Q: How to make system gen’d stats persist after a restart? A: they are always persisted in the primary FG. 11/2/09 10:10 PM http://twitter.com/PaulRandal/statuses/5374383621 #sqlpass PaulRandal: #ktprecon #sqlpass How are auto-gen’d stats names generated? http://bit.ly/14nc0w 11/2/09 10:18 PM http://twitter.com/PaulRandal/statuses/5374593689 #ktprecon #sqlpass PaulRandal: #ktprecon #sqlpass Stats are *always* at the table level. Partitioning has *no* benefit as far as stats are concerned. Even in SS2008. 11/2/09 10:53 PM http://twitter.com/PaulRandal/statuses/5375430211 #ktprecon #sqlpass PaulRandal: #ktprecon #sqlpass 2008 filtered stats != poor-man’s partition-level stats 11/2/09 10:54 PM http://twitter.com/PaulRandal/statuses/5375440275 #ktprecon #sqlpass PaulRandal: Apparently my #sqlpass worth today is limited to tweeting from #ktprecon, and holding her used teabags. Very humbling 11/2/09 11:05 PM http://twitter.com/PaulRandal/statuses/5375710177 #sqlpass #ktprecon PaulRandal: @plitwin #sqlpass #ktprecon She’ll get the same treatment in my Friday post-con – it goes both ways. 11/2/09 11:09 PM http://twitter.com/PaulRandal/statuses/5375810255 #sqlpass #sqlpass PaulRandal: #ktprecon #sqlpass Filtered indexes and filtered stats might become seriously out-of-date http://bit.ly/1knEE2 11/2/09 11:41 PM http://twitter.com/PaulRandal/statuses/5376600563 #ktprecon #sqlpass PaulRandal: #sqlpass One of the joys of co-presenting is watching Kimberly trying to draw on-screen using ZoomIt 11/2/09 11:49 PM http://twitter.com/PaulRandal/statuses/5376778832 #sqlpass PaulRandal: #ktprecon #sqlpass Does order of columns in WHERE clause affect index choice? No. (unless you’ve got, say, 50 conditions) 11/2/09 11:50 PM http://twitter.com/PaulRandal/statuses/5376814447 #ktprecon #sqlpass PaulRandal: #ktprecon #sqlpass I love hearing UNION described as “set, set squish” 11/3/09 12:10 AM http://twitter.com/PaulRandal/statuses/5377285038 #ktprecon #sqlpass PaulRandal: #ktprecon #sqlpass [...]
Migrating to SSAS cubes error
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. Here’s the article to install the Backwards Compatibility: http://msdn.microsoft.com/en-us/library/ms143755.aspx 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. X86 Package (SQLServer2005_BC.msi) X64 Package (SQLServer2005_BC_x64.msi) IA64 Package (SQLServer2005_BC_ia64.msi)” And here’s a screenshot of what installing this component looks like: So, Lesson learned. Use a checklist when installing new servers even if you think you have it all memorized.
Second Time Zone in Outlook Calendar
I wanted to take a minute today to try and give everyone that is going to the PASS Summit a helping hand with being in a different Time Zone next week. As nice as it would be if we could all focus in on just the PASS Summit, some of us might still have some commitments back home and maybe even some people back at the home office that for some strange reason think that setting up a conference call might be necessary. This should help you and you might even want to show it to a few people around the office (then again you might not.) Here are the steps: Step #0: Open Outlook Step #1: Go to Calendar and hover over open space and right-click Step #2: After Right-Clicking select “Change Time Zone” Step #3: When you get the Pop-Up Step #4: Now See the results And that’s all there is to it