Webcast: Do It Right: Best Practices for Analysis Services 2005 and 2008 – Tomorrow
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
Featured White Paper(s)
VIDEO WHITEPAPER – How to Avoid Downtime – VIDEO WHITEPAPER
In this Experts & Insights video, we take a close-up look at the data protection challenges and solutions surrounding downtim… (read more)
Featured Script
listdatabaseinfo.SQL
Returns name, type, rows, number of indexes and other key information about the database… (read more)
How to Write a Stored Procedure
The first thing I do when writing a stored procedures is ask myself the question, "How am I going to test this procedure?" The answer has to be pretty simple or the procedure is too complicated.
For example, sometimes you have a need to call a stored procedure with different parameters. Perhaps you have three variables, and you want to use any combination of those variables..let’s say CustomerID, Purchase Date, Phone Number. How are you going to test all the permutations of this procedure? A boolean truth table looks something like
| Customer | Date | Phone |
|---|---|---|
| 0 | 0 | 0 |
| 1 | 0 | 0 |
| 0 | 1 | 0 |
| 0 | 0 | 1 |
| 1 | 1 | 0 |
| 1 | 0 | 1 |
| 1 | 1 | 1 |
That’s a lot of tests to write if the procedure is going to be used with all of the possible permutations. However, I often find stored procedures written so that the three parameters may be used but they are mutually exclusive. Using this example, a customer may be provided but never in conjunction with a Date or Phone. The same applies to Date and Phone when provided.
In this case, you really simplify your stored procedure code and the tests needed by making three different stored procedures. You reduced your tests from 7 down to 3 with parameters. I suppose you could have a fourth procedure without parameters if that is really a valid user requirement.
If the procedure had a lot of work performed that you do not want to duplicate and maintain, then that code can be embedded in a shared Procedure, User defined function, User Defined Table Function and or view depending on the nature of the shared code. Now you can test those aspects of code that are actually shared with each of the different search criteria without having to meld your testing requirements.
The point is that you can use a more modular approach in set based languages allowing you to simplify your code, increase the opportunity for reuse, and simplify the testing of the code as well.
Share your tips for writing code in SQL Server with the rest of our readers by sending your comments to btaylor@sswug.org.
Cheers,
Ben