Back in July, 2009, it was a usual hot summer day in the Big Apple, but inside the NYC Microsoft Headquarters, was a very cool and awesome presentation going on, brought to us by SQL MVP, Principal of Pragmatic Works, co-founder of SSC.com (and in his own words, amateur surgeon), Brian Knight. We’ve read much on the topic since it was introduced in SQL Server 2005 (read my article Analysis Services, The Year of BI), but nobody delivers the goods quite like Brian. An introduction to SQL Server Analysis Service (SSAS), Brian gives us the benefits of using SSAS, explains dimensions and hierarchies, cube design and measures, as well as usage based optimization (UBO). With some great live demos, he walks us through creating a cube, building a user interface for developers, as well as how to browse the cube in Excel.
In this article, I will cover the topics and materials used in Brian’s exclusive SSAS overview, and the general nature of the user group experience. Although I will discuss some of technical content that was presented, I will not drill down in great detail. Instead I will give a high-level overview, and direct you to some useful and insightful resources to find out more. I will define most of the technical terms in this article, as a matter of course. Here, I will also discuss the theme of this jam-packed NYC SQL User group that brought dozens of SQL Server professionals out on a Thursday evening. This wasn’t a SQL Saturday, but Live from New York….It’s Brian Knight!
Jumping right into the presentation, why use SSAS? The top reasons are that you can build high-level aggregated reports and dashboards in seconds, users can generate their own reports with drag and drop capability, and, you have the ability to centralize items such as key performance indicators (KPIs). Brian gives us real-world examples and practical use throughout his presentation.
He then goes on to discuss the client server architecture of SSAS and lists the applications and protocols used to connect to the Analysis Server. These applications include Excel, BIDS (Business Intelligence Development Studio), SSMS (SQL Server Management Studio), and Profiler.
Here’s a visual look at the three-tier client server architecture of SSAS:
Let’s talk a minute about using Excel as a frontend, and using it to view SSAS as a cube.
What I have found with Excel 2003 is that is works well without the special additions, but it lacks features such as the drill through ability that a lot of users find helpful. This is supposedly addressed by additions you can download and install, but we tested it, the data was never what it was in the cube and it was difficult to create the pivot table in the new tools.
Excel 2007 is significantly better with its OLAP interface and also has the highly desirable drill through capability. You can refresh the entire pivot tables in a single work book at once instead of having to go through one at a time, the formats set in the cube are now automatic, as opposed to constantly having to be formatted appropriately, and you can even create local cubes that can be used when disconnected from the network. Finally the increased columns mean you can freely put dimensions on either axis without running out of room.
Bottom line: if you are using Excel to access your OLAP data and can upgrade to Excel 2007, do so. If you can't, I don't recommend the Microsoft tools--just use Excel 2003 to the extent that the normal program allows it.
As he moves on to the next slide, we are given an overview and definition of the key objects contained in an instance of SSAS. Describing to the audience on what Analysis Services Cubes are, he further breaks down its composition made up of measures and measure groups, dimensions and hierarchies.
A measure is the value you are measuring, or, a fact. A fact table contains foreign keys that join it to the dimension tables and the numeric/transactional values (i.e. measures) that we want to aggregate. (summarize, average, etc.). Measures grouped together are called measure groups. All together, they make up a cube used to analyze the data.
Furthermore, a hierarchy is how you want to organize the members of your dimension. Brian’s example of the dimension called “geography” will consist of the organization of its levels that would be city, state, and country.
One slide representation of the dimension model shows the attributes of the dimension. Since SQL Server 2005 Analysis Services uses attribute-based dimensions, every attribute in a dimension is automatically treated as a standalone hierarchy.
After discussion and review of dimensions, hierarchies, and attributes, we moved on to the first demo, where Brian walked us through creating a datasource, datasource view, build a simple cube and finally, how to browse the cube in BIDS (Business Intelligence Developer Studio).
As we know, a data source is when we setup a connection via a connection string that defines, in our case here, how Analysis Services will connect to the physical data store via OLEDB provider or .NET Framework. Our data source can be a SQL Server database or another platform database such as Oracle or DB2.
The data source view contains the logical model of the schema used by database objects, including cubes, dimensions, etc. We can integrate data from multiple data sources within the cube. As a good reference and overview of Data Source Views in Analysis Services, see this article by clicking on the link at DatabaseJournal.com. It also has additional links to documentation on building cubes as well.
For first-time testing and foray into building cubes, you can install and deploy the sample BI database, AdventureWorksDW. Before you can browse the cube, you’ll need to build it first! You will need to setup a BIDS project to create and deploy a cube. To build an SSAS cube in BIDS, you need to select File àNew Project, and select the Analysis Services Projects template in Visual Studio. For SQL Server 2008, you can find the sample BIDS project located at C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks 2008 Analysis Services Project. Here is an excellent step-by-step reference with graphic representations on building and browsing the cube in sql server 2008:
SQL Server 2008 – How To Build and Deploy AdventureWorks OLAP Cubes
Following the demo on creating the data source, data source view, and creating and browsing the cube, Brian moves on to discuss User Defined Hierarchies and reasons to create them. A User-Defined Hierarchy is a named selection of members, all from the same dimension, and provides navigation paths in a cube. The optimization of these paths provide for optimal performance at processing time. They are “attribute-aware”, and enable ad-hoc navigation. User-Defined Hierarchies are an easy way of displaying members you always analyze in a report.
In the next demo, based on the above information, we are shown how to create a hierarchy, how to deal with problematic keys, learn about attribute relationships, and browsing the cube in Excel. (See above discussion on using Excel.)
Brian then continues to teach us about Analysis Services Aggregations, which he defines for us as “A subtotal of partition data based on a set of attributes from each dimension” Aggregations improve query response time by preparing the answers before the questions are asked, and therefore are precalculated summaries of data.
For example, when a data warehouse fact table contains hundreds of thousands of rows, a query requesting the weekly sales totals for a particular product line can take a long time to answer if all the rows in the fact table have to be scanned and summed at query time to compute the answer. However, the response can be almost immediate if the summarization data to answer this query has been precalculated. This precalculation of summary data occurs during processing and is the foundation for the rapid response times of OLAP technology. The slide graphic below provides a visual on aggregations:
Figure 2 **From the presentation “Intro to SSAS” by Brian Knight
To shore up his knowledge sharing on Aggregates, his next and final demo walked us through creating an aggregate, and finally shows us usage based optimization (UBO). We learn how to use theUsage-Based Optimization Wizard in SSAS. The wizard gives us a quick way to create aggregations to improve cube processing performance. There is a lot more on this tool that is out of scope of this article. For in-depth information on UBO see Optimizing Microsoft SQL Server Analysis Services: Optimization Tools: Usage-Based Optimization Wizard.
That pretty much wrapped up this fine seminar on SSAS and Brian’s whirlwind trip to the Big Apple – NYC. By the time this article is published, Brian has visited several cities and presented his exclusive Intro to SSAS in assorted venues.
Another useful article written by another author, SSAS an Introduction, is also a very good primer on this subject.
Back in Jacksonville, Florida, Brian is now focused on bringing Business Intelligence to the community and companies large and small, as well as helping and training people on better understanding and mining their companies’ data. We best know Brian as co-founder of this fantastic and invaluable site, SQLServerCentral.com. His company, Pragmatic Works, offers training, SSIS software solutions, and BI expertise. Recently, Brian and his colleagues launched the BI Developer Network (BIDN), a free resource that contains the best information about bringing business intelligence into your company. Check it out at http://www.bidn.com .
And, definitely make it your business to attend a SQL User Group in or near your city – you never know who may show up!
--------------------------------------------------------------------------------------------------------------
Pearl Knowledge Solutions, Inc., is now offering quality DBA managed services and remote 24x7x365 monitoring from its network of skilled DBA experts! To inquire about our services, and obtain a FREE SQL Server report, contact us at sqlcentric@pearlknows.com.
Quality DBA Services from the folks who brought you:
When SQL is the Center of Your Universe!
Coming Soon! We are getting ready to release our SQLCentric SQLOS live Performance Dashboard! Our dashboard will be available as a stand-alone solution, or fully integrated with SQLCentric. See all critical performance measures and vital stats from one web console in real-time! Feel free to contact us for more info.
