Webcast: Do It Right: Best Practices for Analysis Services 2005 and 2008
Are you seeking practical, in-depth technical advice for building a BI solution using SSAS? Analysis Services changed tremendously with the advent of SQL Server 2005. It introduced a new way of building dimensions and cubes that required a new way of modeling the solution. This session examines the best practices for properly designing cubes for performance and usability. It discusses some high-level topics but also looks at advanced topics such as alternative approaches to many-to-many dimensions, SCOPE statements, aggregation design, scalability issues, processing techniques, server properties, and more. Craig Utley has been working with Microsoft’s BI products for 11 years and is a former Program Manager with the SQL Server Customer Advisory Team (SQLCAT), where he worked with some of the largest Analysis Services installations worldwide. In this session he brings his real-world experience with large, complex SSAS projects and presents best practices uncovered as companies deploy and use Analysis Services.
Presented by: Craig Utley
> Register Now
> Live date: 12/8/2010 at 12:00 Pacific
New SelectViews Show Posted
(and learn about our very own Cyber Monday specials!)
Devin Knight on the show today talking about dynamic security inside of Analysis Services. Also, hear from Rick Pleczko of Idera about some exciting capabilities and features that are now in production.
[Watch the Show Now]
Virtual Eduction in SSIS and SSRS
A while back Steve provided some great insight on how to grow your skills as an SQL Professional. Take one new topic at a time, and expand on what you already know. The reality we face every day is that SQL Server consists of a number of technologies that are quite extensive; it is a big challenge to remain on the top of all the different tools. So, here is an opportunity for you to bite off a piece of the BIG Elephant a small piece at a time.
SQL Server MVP Eric Johnson has put together a virtual training series on SQL Server Integration Services (data import, export and transformation tool) and SQL Server Replication. Both of these are BIG topics. Eric provides you with access to these tools for the entire month of December, and you also have an option to purchase a DVD of the course.
Find out more about the SQL Server Integrations Services Virtual Training Course or the SQL Server Replication Virtual Training Course. Don’t wait for someone else to help you extend your knowledge. Dig in now and be ready for those opportunities when they come.
Featured Article(s)
Tips for using SQL Server 2008 stored procedures
In this article, you can find some tips to use stored procedures in SQL Server 2008.
Featured Script
dba3_hr_Set_SubSets_Article
Creates stored procedure hr_Set_SubSets. Used in demonstrating a Maps and Sets logical design of a hierarchical system. Use w… (read more)
Multiple Indexes on a Single Table
Thought I’d share a little insight on SQL Server Indexes.
As many of you know, SQL Server is able to utilize more than one index for a single query. I got to wondering how that worked one day, and decided to try an experiment.
I took a table that had 8 distinct columns used as parameters for a query. I created an index on each of the 8 columns including the column value and the primary key.
Then I wrote a query that aliased the same table 8 times, each time filtering on one of each of the 8 columns. Basically, I was using the index as a covering index, and not really using the table at all. The 8 aliases to the table provided access to the 8 indexes. I joined each of the 8 aliased tables on the primary key resulting in a dynamic composite index using the intersection of the primary key of each covering index.
When I reviewed my query plan it was what I expected. Each of the indexes was used, once for each of my aliased tables. No surprise.
The interesting part was when I ran a normal query. I ran a query against the base table without forcing the use of any index by using all 8 parameters of my where clause against the original base table. When I showed the query plan it was identical to the plan I had forced with my first query. The optimizer chose to use the intersection of the multiple indexes rather than doing a table scan or a couple different indexes that did not cover the data completely.
Depending on the number of rows in your table, and the data distribution, creating multiple indexes on less columns may be more flexible and provide reasonable performance. Don’t go breaking up all of your composite indexes into multiple smaller indexes right away. This intersection method can have a lot more overhead than a well designed composite index. The benefit of the single indexes using intersection is the flexibility of their application.
Well, there’s some food for thought on this Thanksgiving day. Now you can enjoy it with your Thanksgiving dinner too.
Got any index tricks you would like to share. Send em to me and we’ll post them for all.
Cheers,
Ben