Category Archives: Cubes

A History of Business Intelligence

A short post for a Friday!

I’m finishing up my first 2 weeks at my new job and new position.  I don’t think I’ve been in SSMS for more than 3 hours in the last 2 weeks.    This is a big change for me I’ve spent the better part of 12 years in the trenches with SQL server either Administering, Architecting or querying SQL Server.  I’ve spent much of my time reviewing Business Intelligence tools that are out on the market.  I’m working on some blog posts on things to look for and some of my impressions of these tools but until those are complete enjoy this short post and this very well done You tube video about Business Intelligence.  It’s not slanted any one way in my opinion and it doesn’t feel like its’ trying to sell a specific product.  Take 10 minutes when you have time and check it out.

http://www.youtube.com/watch?v=_1y5jBESLPE&feature=related

“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!