Monthly Archives: September 2009

Utah Code Camp (Wrap up)

Another Utah Code Camp has come and gone.  I figured I would write up a quick review of the event both for all my readers benefit and for my own to keep a reminder of items I need to work on. 

The morning started out rather hectic with the doors not getting open on time.  Good idea to remember to always call your location and contacts the night before and make sure the times are right. 

Thankfully STG brought in bagels and coffee/OJ so everyone had something to snack on while we were going through registration and getting the doors opened. 

The sessions kicked off with a keynote from Dr Alistair Cockburn which I believe did an excellent job.  Unfortunately I had many other minor details about the camp that needed my attention so I wasn’t able to make the session but so far from the surveys and others response he did a great job. 

We made it through the sessions in the morning well without to many hiccups.  Once again we fell a little short on pizza for the lunch hour.  The lesson learned here is that for IT folk you have to triple your pizza order not double it. 

We discussed Primary key topics, clustered indexes, Olap vs Oltp , and DB architecture in our database roundtable.  I think most came out with some good questions answered and lots of good discussion was had by all.  Next code camp we are going to plan for a full SQL track to get lots more information out to the people. 

I was able to make it to Aaron Skonnard’s Session on Windows Azure and Cloud Computing.  I had not seen Aaron speak before I’m happy to say I was not disappointed he did a wonderful job of speaking and confirmed many of my suspicions about the cloud and what we can expect from it(future blog post). 

Of course my favorite part of the day is being able to give away stuff.  :)  We gave away a lot of software this time.  Well over 10K in software. 

All in all it was once again a great event and I’m already looking forward to the next one.  Thank you to all my excellent sponsors that made the camp possible.  A HUGE thank you to my presenters and to Craig, Nathan and Justin for helping me so much with organizing the event. 

Hope to see everyone at the next Code Camp!

Advertisements

“The Cube” Summary of Peter Myer’s User group presentation.

Peter Myers is in SLC this week to do some training. He was kind enough to approach the Local SQL PASS user group and offer to do a presentation. I helped out by organizing the location at New Horizons. This would be a good time to check for your Local SQL PASS Chapter if you don’t know where it is. Here’s a list!

Peter’s Presentation was “Developing a UDM with Analysis Services” UDM = Unified Dimension Model. This is a new name MS has given for Cubes in 2008. So don’t let the name fool you UDM =Cubes. Peter did a great overall presentation on Cubes showing us how to create them and enriching them with more features. Here’s some of the key points.

Reasons for Moving to OLAP Structures instead of OLTP

Performance,

Multiple Disparate Systems of data can be combined to one,

Interpretation /One Truth of the data can be controlled in one location.

DW is a continuing process never ending. Answering questions will always just yield more questions.

We all know that the more questions you answer the more questions the business has for you. So when someone says to you that the DW process will be done in 2-3 years it really won’t it will keep evolving. Much of the design and work to load the DW could be complete but more questions will keep coming.

Data Source Views

Allow for the abstraction of your data layer. It’s good to build the foundation properly and setup your DW database in the fact/dimension table schema that you have designed. When that’s not always possible or your given read only access to the data and you can’t change the underlying schema then you need to make those changes in Data source views. Here’s some key items you can do.

Create columns based on other columns.

Create tables based off queries and filter tables based off a query.

Create friendly names to make it easier to use in the cube.

Dimensions

Peter suggested creating your dimensions before allowing the cube wizard to create your dimensions for you. Typically I create my dimension tables in the underlying data structure and then allow the wizard to create the dimensions for me. I agree with Peter though that this will allow you to be more in control by creating the dimensions first and modifying them for your need and then just telling the wizard where to go to get the data. I like this approach much better and plan to move to it in the future.

It’s a good idea to change your Key columns and your Name columns in your dimensions to the data that you actually want. The wizard does not always get this right and should be something that you get the information you are looking for.

Limit your dimensions and don’t show the columns the end user doesn’t need.

Create hierarchies to help the end user navigate better.

SSAS 2008 incorporates best practices into it so if you see a blue squiggly line it’s making a suggestion that it wants’ you to change something to meet best practices.

Cubes

Peter really showed us the building blocks to building the Cube so when it came to actually building the Cube it took very little time and worked very well. I’ve been a firm believer in building your foundation first for your cubes so huge props to Peter for doing an excellent job in explaining it this way for everyone.

Excel

I’ve used pivot tables in excel for many years so most of this was review for me but as always you should keep learning new things and I once again learned something new here. I learned that about the formula for =CubeMember() in excel. I was not aware of this before allowing you to place cube data just about anywhere in excel. This will be great for some specific reports that we have that are manually updated right now. I can place a sheet on the work book that has the cube data and just hide the sheet and reference it with this throughout the report that they want. Great added functionality.

Overall Peter not only knew his stuff but was an Excellent presenter. I really have a hard decision to make now on what pre-con I want to attend. 🙂

I’m planning some follow up blog posts on a few other key items he mentioned. Attribute hierarchies, properties of dimensions and more on using excel and CubeMember()

Thanks again Peter!

When a Second just isn’t fast enough

So I find myself performance tuning queries and statements all the time for SQL Server.  Many times I run into easy statements that are missing an index or calling a function 20 million times and causing problems.  These queries typically move from 20 seconds to 1-2 seconds very easily with some quick changes.  My problem this week was a query that took  1.5 seconds.  I needed to get this to run under 1 second consistently. I knew could get a little more out of the query by looking at the execution plan but I also knew I would have to consider the Architecture and the application itself to really get it the way I wanted it. 

I was surprised to find something in the execution plan that helped me out more than I expected.  The Key Lookup (In 2000 they are called Bookmark Lookups).  These show up in the execution plan and look like this. 

image

You will usually find these in your execution plans along with an Index seek/scan.  But what really do they mean?  SQL Server has found your data in the index but some of the values you have asked for in the query are not located in the index pages and it must go and lookup the data.  This will take a little more time as SQL Server goes and fetches your data. The funny thing about this query is that for the Execution plan it only put cost at 10% on this particular lookup.  Which seemed odd to me because when I looked at the IO STATS ( Set STATISTICS IO ON) it seemed like many of my reads were to pull back this data.  I decided I would ignore the Cost %  and place a covering index on the fields that are getting pulled back. I changed the index that it was using in the seek to add columns in my select statement as INCLUDE columns in my index.  This made a larger improvement than just the 10% that it had estimated.  It removed about 500ms of time from the query.  Which is a lot when your trying to go from 1.5 seconds to <1 second. 

Of course you do have a trade off here. By adding the columns to the index I’ve made my index larger and take up more space so don’t go around just adding all your fields into the INCLUDE of a index.  You need to evaluate and find out if the trade off is worth it.  In this case it is.  I’m also making changes to the application so that it doesn’t request columns it doesn’t need then I can remove them from the INCLUDE and save myself some space. 

If there is one thing I’ve learned from performance tuning and reading execution plans is never take them at face value.  They are a tool that was created to help you make better decisions.  They are not always right so make sure to do your homework and really test your procedures and break them down to find the real bottlenecks. 

Happy Performance Tuning

24 Hours of PASS

Ok so I can be honest  and say I did not sign up for 24 hours of pass until the day before it started.  This wasn’t because I was not interested just to busy with many other things.  I figured I would make a few sessions and learn something.  Boy was I wrong (this happens a lot for me). 

Once I found that I could also watch SQL Rockstar(Thom Larock) live while the sessions were going on I was hooked.  The side conversations and the talk going on in the chat was just like being at PASS.  It made me want it to be November right now!  This made the event for me and really added to it. 

But did I actually learn something?  Why yes I did.  I still only made a few sessions because of my schedule but the few I made it to were great.   I attended Steve Jones session on Blogging.  This is something I didn’t want to miss as you can see from my Blog I need help.  I am taking his advice and going to set aside time for blogging and start getting blogs in a “que” ready to go.  So I’ll start simple on my goal and stick with once a week at least.  We’ll see how I do. 

The other session that left me with good information was Brian Knight’s session on “Loading a Data Warehouse”.  Brian is one of those amazing technical people that is very smart and an excellent teacher, I love attending his sessions whenever I get the chance.  I have always been hesitant about using the built in lookup tools and dimensions tools in SSIS and preferred to use my own stored procedures and set based methods.  This is usually based around  performance as Brian mentioned in his talk performance is bad when it has to do it row by row. But for the first time I’ve seen by a BI person he gave some set based alternatives using SSIS.  Using these techniques will help me in a current DW I’m designing.  I may also *try* and use the Slowly changing Dimension in SSIS but I have a feeling doing it in T-SQL and using Merge will just be easier for me.  🙂

So would I attend something like this again.  Absolutely 100%.  Am I extremely stoked to get to PASS in November Absolutely 100%! 

A great big thank you to everyone at PASS and all the speakers and moderators for giving their time and volunteering for this.  Thank you also to Rick Heiges who started the idea and championed it through the process.  Once again Rick I think I owe you a Bacardi and diet coke for this one.  :)