SSAS Training – Denali

My SSAS training from Pragmatic Works actually completed on Wednesday. However they added an extra optional day to cover some of the new features coming soon in Denali. Brad was replaced by Roger Doherty from Microsoft. Roger started us off with an overview of Denali, showing the timeline of SQL back from the beginning, way back in the Sybase days, to the present and beyond.

Brian took over and we saw what was new for DBA’s The big feature here is “Always On”, a way to keep your servers up. It’s kind of like a combination of mirroring and transaction log shipping. But it’s more flexible – you can use it for all your databases in on shot, not one at a time. you can have up to 4 secondary servers. Compression and encryption are build in. It integrates right into Management Studio through availability groups, and it works with both filestreaming and replication. Best of all, you can read off of and backup the secondary server. And you can use multi subnets, great for setting up in a remote location.

Another advantage of using Always On is the use of contained database. When you contain a database it removes all dependencies on other databases. You won’t need to create logins inside the master database anymore; each database will have its own accounts. No more copying logins to the failover server. You set the instance to allow containment, then on each user database set the containment type to partial. Partial and none are the only options for now. There are a few gotchas, though. You won’t be able to use linked servers or fully qualified names to other databases inside the instance. And you’ll still need msdb for Agent jobs.

There’s a new type of index, called columnstore. This allows indexing on a column level rather than a row level. It’s a non-clustered index, but you’re limited to 1 per table. And it’s read only; you shouldn’t put it on table with lots of inserts. It’s meant more for large tables inside a data warehouse. But it can be disabled and rebuilt during data loads. However it will take about 1 1/2 as long as a regular index to build. But the columnstore index is so efficient that you can eliminate other indexes. Brian showed some demos and the difference in the time it took for a query to run with and without the new index was huge – 9 minutes down to seconds. It will use less IO but it will increase the buffer count.

SSIS wasn’t left out, there’s new features and improvements here too. First of all, Undo now works. Copying a task and pasting it puts the object where you click, not somewhere at the bottom. You still deploy projects, but that’s changed. You can’t deploy individual packages through BIDS anymore. Deployed projects don’t go to msdb anymore, they go into a new database. You create folders inside the database for the packages and permissions can be handled on each level. There’s now a versioning option where you can rollback to a previous release. And there’s now reports showing execution history.

I’ve left out a lot. There’s the new Tabular module in SSAS. There’s the file tables improvement to fliestreaming. There’s the new THROW keyword to replace the RAISERROR. I haven’t mentioned data quality services yet, or “Juneau”, or “Crescent”. Frankly I’m still absorbing it all. There was a ton I learned in the last three days and I’m still a bit overwhelmed. But I plan on coming back to the new features in the next week or two, creating my own demos to share.

Until then. 

SSAS Training – Day 3

Today’s the last day of my SSAS workshop, hosted by Pragmatic Works and very ably presented by Brian Knight, Brad Schacht, and Tom Duclos. This is a bonus day, it wasn’t on the original agenda when the workshop was first offered. We’ll be looking at some of the new features of Denali. Since CTP3 was just released a few weeks ago the timing is perfect.

Yesterday was just an awesome day. Seeing what you can do with your cube after it’s build is an eye popping experience. My background is as T-SQL developer, then as a DBA. As Brian and Brad walked through data mining and the different ways to present the data, I couldn’t help but think as to how I can do the same tasks today, in my present environment. More and more of my company’s projects are looking at data mining whether they realize it or not. With what I’ve learned in the last few days I feel I can go back to work on Monday (well, more like the next Monday since I’ll be on vacation next week) and demo what SSAS can really do, especially paired with SSRS, SharePoint and Excel.

We began the day by going over data mining. Now I realize that data mining is a complex subject, and there’s more to learn than what I saw yesterday. But it seems to me that the key to data mining is knowing what questions to ask. If you know what you want to see on your reports then it’s just a matter of knowing where the data is and where to get it, how you want to slice the data, then going out and building your data mining models.

One thing Brian talked about was how you don’t even need a cube to build your models. All you really need is a data source and a data source view pointing to your data. You can easily create a view on your existing SQL databases to prepare your data, then use that to prototype your models. Everyone can use the time series algorithm to predict what future values will be, no matter what industry you’re in.

Security was covered too. You can create roles inside the SSAS database to ensure that users only see the data that you want them to see. Then you add users to the appropriate role. Roles seem to be based more on the database level than the server level so I’m not sure if roles can cross over databases. But I’ll look into that on my own time.

We also looked at backups and recovery of SSAS databases, and how it’s handled by XMLA. It’s easy to script out tasks to perform the backups, then schedule an Agent job to run it. Restores are no different. You can modify the XMLA script for any needed changes, such as renaming the SSAS database.

We looked at partitioning and the difference between MOLAP, ROLAP, and HOLAP, the benefits and drawbacks of each and when to use them. Also looked at the difference between the different MOLAP options and proactive caching. One thing I learned is that handling partitions is usually done during the ETL process, that SSIS has operators for those tasks.

The last sessions covered the presentation layers, different ways for users to view the data from the cube or the data mining models. We saw that SSRS is terrible at writing the MDX needed to query the cube, but it’s easy to just paste your own into the report and you’re good to go. Unfortunately the formatting you applied inside the cube has to be redone in SSRS; hopefully Microsoft will fix that soon. Deploying the reports to SharePoint is as easy as deploying to a report server, you just need to configure SSRS to use SharePoint mode and you’re good to go. The reports can be used as part of a SharePoint dashboard. Right now I’m envisioning a report to show performance counter data that’s refreshed every minute and is displayed prominently on a second monitor. Realistic? Maybe.

But what I loved most is the integration between Excel and SSAS. Setting up a spreadsheet to connect to a cube, then creating a pivot report and chart can literally be done in seconds. Adding slicers (new in Excel 2010) allows filtering even more. The KPI’s you set up inside the cube come across, icons and all. The chart and data are automatically hardwired so any changes you make to the data appear on the chart. Then you publish the spreadsheet to SharePoint and let users view and manipulate the spreadsheet in a web browser.

This is just some impressive stuff. And I haven’t even mentioned Power Pivot yet!

I had to leave about an hour early yesterday because of a prior commitment so I missed out on what was looking to be a great time. Brian and Brad were going to divide the room into three sections. Each section would be given a list of requirements, such as you would get back at your office. Then each team had about an hour to build a cube from scratch, using the techniques learned in class. The first team to complete were to be rewarded with a copy of Knight’s Microsoft Business Intelligence 24-Hour Trainer.       

I realize that I’m glossing over most of the subjects here, but this post was meant to be more of a recap that an in depth review of SSAS. I’ll put together another post soon that will go into more detail. It’ll make a good cheatsheet for when I take my 70-448 exam.  

SSAS Training- Day 2

Today is day 2 of my SSAS training with Brian Knight, Brad Schacht, and Tom Duclos of Pragmatic Works. I think I should give a recap of day 1 first.

The venue is very comfortable. We’re in the Microsoft Chicago office, taking up most of their large meeting rooms. There’s enough seating so we’re not all crammed in with no room to take notes or work on the exercises. Someone, probably Microsoft, has supplied enough powerstrips so everyone who brings a laptop can plug in. A laptop is optional; before the class began Pragmatic Works sent a link to exercise files for both SQL 2005 and 2008 so we can also work on them outside of class. Coffee and donuts are waiting in the morning and lunch is provided at noon. Microsoft also provided WiFi access, letting me keep up with work during breaks.

Yesterdays session was 8 hours and breaks were well placed throughout so I never felt the need to take my own. The session itself was very interactive, not just someone going over the official Microsoft curriculum. Brian and Brad are the presenters. They work together as a team, with one taking the lead for a topic and the other asking leading questions to help steer the discussion. They don’t do the demos themselves, volunteers from the audience come up and walk through each part with Brian and Brad providing the steps. Those with laptops can follow along, and personal help is available to anyone who gets stuck. Questions from the class are answered fully, with explanations and demoed when possible. I believe there was only one question Brian couldn’t answer immediately. The entire day was very engaging; I never felt bored or overwhelmed at any time.

The only glitch in the day was during the morning sessions. The microphones Brian and Brad used kept cutting out intermittently. But when that happened they would repeat any points so everyone could hear, and a better microphone was provided for the afternoon topics.     

As far as the actual content, we began with a basic discussion of a data warehouse, explaining what fact and dimension tables are. Brian asked me a little about my company, then proceeded to show how to choose the proper dimensions and facts based on my company’s business. The method he used was very simple; using a whiteboard choose how you want to see your data sliced and what facts you’re looking at. I didn’t realize designing a star schema was that easy.

Next were walkthroughs in designing a cube, starting with building a data source and a data source view, then building a cube on top of that. Then it was about modifying the cube after the wizards were finished; giving friendly names to tables, how to use a format string for measures, adding a new measure or dimension. I didn’t know before that the format string was dependent on the client used to browse the cube; Excel and SharePoint will display the format while SSRS does not. I also didn’t know how to refresh a data source view after changes to the underlying database. Who knew it was just a button? But the refresh comes with a warning; it won’t work with tables based on a query, you still need to modify those manually.

We ended the day going over MDX, what the basics are and how they’re used in creating KPI’s and calculations. Simple things, like the difference between .member (shows all) and .children (doesn’t show all). I’ve created simple calculations before in BIDS. Yesterday I saw how to see the code behind for those calculations. We also got to see a quick demo of how to build a calculation using Pragmatic Works BI xPress tool, an awesome looking tool I’m going to have to play with later.      

Today we’re going to go over partitioning, data mining, and administration. It’s going to be another interesting day.

SSAS Training – Day 1

So today I’m finally going to attend a Pragmatic Works training class on SSAS. I’ve been waiting on this since last February, when I first registered. The class was originally scheduled for the end of May but it was rescheduled until today and tomorrow.And they added a third day to show off the new features of Denali. Perfect timing, since CTP3 for Denali was just released a few weeks ago.

I’ve heard nothing but good things about Pragmatic Works. They’ve been presenters at different SQL events I’ve attended over the years. They sponsor free webcasts every month that are very informative. Even if you don’t have an interest in SSAS, check them out. Most (but not all) of their webcasts are on Business Intelligence and are available on demand.

In the next few days I hope to really get a handle on SSAS. I’ve been studying for Microsoft’s 70-448 BI exam, but that only gets me the basics. My company keeps bringing up data warehousing and I want to be prepared if and when that happens. I’ve already committed myself to building and demoing a full BI application, and I’d like to do it with our data instead of the AdventureWorks tutorials I’ve been working through. And while I’m still on track to take the exam in the next few months, I know that there’s still a lot more to learn afterwards. This week will be a big jump forward.

Configuring a VMWare clone for SSRS disaster recovery

Our environment contains two instances on SQL 2008 that host Reporting Services. They are not in a scale-out architecture, rather each SSRS instance points to two separate data centers. However the reports on both instances are identical.

Both instances are being hosted as virtual servers using VMWare. Recently we began testing a disaster recovery scenario where one server would become unavailable for some reason. In this case the plan is to make a clone of the remaining SSRS server and reconfigure it to be the unavailable server.

From a SQL standpoint our tasks are fairly simple; we just need to insure that the backup of a single user database (small file, about 30 MB) is available from anywhere on the network as well as the encryption key from Reporting Services. The report catalog and Agent jobs are identical on both servers and don’t need to be restored.

These are the steps we took to configure the VM clone as the unavailable SSRS server. I just copied from the document I wrote for our internal use so some of it is pretty basic. But I hope it will help you if you need to perform something similar.

1. Verify that the drive configurations are correct and that all databases are present and in their correct locations.

2. Open the SQL Server Configuration Manager and select SQL Server Network Configuration > Protocols for <Instance Name>. Right click TCP/IP and chose the IP Addresses tab. Look for the IP of the old server (it’s probably under IP1) and change the IP address to the new IP. Close the Configuration Manager.


3. Start the SQL service for the instance. Leave the Agent, Browser, and Reporting Server services off. Turn them off if they’re running.

4. Open Management Studio and log into the new server. Use .\InstanceName as that may be the only way you will be able to connect until after you change the name.

5. Run tests against the data to verify that you have the right databases. Confirm the server name is still the old name


6. Change the name of the SQL server. NOTE: You can only change the computer name, not the name of the instance. Also note that the host name of the full instance name is limited to 16 characters including the (\): so 1234567890123DR\Reporting is legal whereas 1234567890123-DR\Reporting is not.

 1: USE master;
 2: GO
 4: EXEC sp_dropserver 'OldServerName\InstanceName';
 5: GO
 7: EXEC sp_addserver 'NewServerName\InstanceName', local;
 8: GO

7. Turn off the SQL service. Restart just the SQL service and connect via SSMS. Verify that you can connect. Run SELECT @@SERVERNAME again to verify the server name is correct inside the database.

8. Start the Reporting Server services and open the Reporting Services Configuration Manager. Connect to the local instance.


9. Go the Databases in the left frame. Click Change Database, then select Choose an existing report server database and click Next.

10. Enter the new Database Server name (the new name of the instance) and click Next.


11. Choose “ReportServer” from the drop down list for Report Server Database then click Next. Continue to click next until you finish the wizard.


12. Back on the main screen of the Reporting Services Configuration Manager select Encryption Keys and then click Restore.


13. Point to the location of the encryption key, enter the password, then click OK.

14. Open RSReportServer.config (the default path is C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services). Change the URL path from the old server to the new server. Use the new IP and not the server name.

After this the Report Catalog is ready to go. All I have to do is restore the small user database and reset any needed logins and we’re good to go.

Renaming a SQL instance gotcha

Just a quick post about an issue I ran across this morning. As part of a disaster recovery test I have to rename a SQL instance. Not the instance, but rather the machine name. This is a SQL 2008 Standard Edition named instance

The machine name is 1234567890123-DR. The old instance was called 1234567890123\Reporting, so I wanted to change it to 1234567890123-DR\Reporting. Simple enough, I wrote an earlier post about it here.

However when I renamed it, I couldn’t connect through SSMS by name. When I did connect and looked at @@SERVERNAME I saw 1234567890123-DR\Reporting, which is what it should be. But looking at the host name in the SQL Server Configuration Manager I saw 1234567890123-D as the host name. The last R was being truncated.

So I renamed the SQL server again, this time to match the host name. And after stopping and restarting the service I could now connect to 1234567890123-D\Reporting, and that’s what @@SERVERNAME returned.

What happened? The name of the computer is16 characters, and \Reporting fits the limit (also 16 characters) for a named instance (See Books On-Line). But the last character was clearly being cut off.

I’m guessing because I haven’t Bingled it yet, but I think that the slash between the machine host name and the name of the instance is counted as part of the host name. So when I renamed the server and it looked correct inside SQL, SQL itself didn’t like it and cut the last R off without warning me.

I don’t think it will affect my DR testing, but it is something to be aware for the future.

A poor man’s monitoring if a SQL instance is running

A recent power failure in one of our data centers exposed a flaw in the way we configured alerts on our SQL servers. We have alerts configured to send emails for all critical and fatal events, plus a few other business related events. However, the email account used by SQL was also affected by the blackout and none of the emails were send until the next morning.

We have two data centers set up, with a number of SQL database servers as well as an Exchange server in each. So we were able to come up with a simple method of monitoring if a SQL instance was still running; set up an email account on each Exchange server and configure the SQL email profiles to use the account on the opposite server and use that account to send emails in cases where alert conditions are met.

However this only goes so far. Any power outage could also affect the network connectivity as well as the servers. So we took one more step.

On a SQL server in one location, we set up a job to run a query against a SQL server in the second location. If the query executes with no problems then the second SQL server is running. If the query fails then the job sends us an email. We set this up so all SQL server instances are being queried by a server in the opposite data center. This way we can at least tell if there is a connection issue in the case and then take appropriate steps.