Happy New Year!
Introduction to Business Intelligence Articles at TechNet
Even BI novices can learn what goes into creating a BI solution, from planning and designing a data mart to preparing data. An Adventure Works example illustrates the first steps a company can take to ensure that its BI solution supports the strategic decisions it needs to make.
Business Intelligence: Building a Data Foundation for a BI Solution
August 2009 - Derek Comingore
See how you can design and build a data mart that uses SQL Server 2008 Integration Services to perform the extract, transform and load (ETL) processes for the Adventure Works BI solution introduced in "Planning Your First Microsoft BI Solution."
Business Intelligence:Building Your First Cube
November 2009
There's lots more to your data than you can see at first glance. Learn what a cube can do for you -- and how to build one. We show you how.
Best Practices Conference
Life as an independent consultant is interesting and challenging, but it gets a little lonely in the sense that you don't spend a lot of time with your industry peers. That's why I enjoy getting out to conferences each year. I always run into people I know and admire and get to pick their brains on some nagging issue I'm battling at the time and I always get to meet new people who are doing interesting things. And of course I get to share what I know, too. This year I'm going to the Best Practices Conference for the first time, but I recognize most of the speakers' names so there will be many familiar faces and I feel privileged to be included with them.
I'm presenting the following two topics --
BI316, Optimizing Your MDX Queries
Retrieving data from an Analysis Services cube is supposed to be fast, right? Then why are your queries running so slowly? Many factors can contribute to poor query performance. If you have applied best practices design principles to cube and dimension design, the next step is to evaluate the design of your MDX queries. In this session, you first learn how to know whether your query or the cube needs to be optimized. Then you learn the principles of optimal MDX query design in Analysis Services 2005 and Analysis Services 2008, and how to modify your queries to deliver data faster.
BI337, Designing Analysis Services Cubes for Performance
Building a cube is simple. After all, you have a lot of wizards at your disposal to do the development work. But building a cube that delivers data quickly requires you to make additional changes to the database design. Come to this session to learn what steps you need to take in the development process to ensure the cube is designed for optimal performance, whether you're using Analysis Services 2005 or Analysis Services 2008. You learn not only how to properly design dimensions, aggregates, and partitions, but why these design principles improve performance.
If you're planning to be there, be sure to stop by and say hello!
Microsoft SQL Server 2008 Reporting Services Step by Step - What's New?
My latest book effort, Microsoft SQL Server 2008 Reporting Services Step by Step, was released a couple of months ago. It’s always a nice feeling to see the finished work in print, especially after having some distance from the writing and editing process. Microsoft Press asked me to call out a few things that distinguished this book from its predecessors (Microsoft SQL Server 2005 Reporting Services Step by Step and Microsoft SQL Server 2000 Reporting Services Step by Step) and so many more things came to mind than they were looking for to put in the Microsoft Press blog about the book.
First, this was a solo effort, well – relatively solo. I should say instead that it was the first time I worked on an entire book without my good friend Reed Jacobsen who has been so incredibly helpful in the earlier books and is my co-author in (Microsoft SQL Server 2005 Analysis Services Step by Step). I missed his direct guidance throughout this project, but his voice was always in my head so he is an indirect influence still. Hopefully, I have done justice to all he has taught me. To flip things around, it was my turn to teach someone about the process of authoring a step-by-step book. My contributing author, Erika Bakse, helped a great deal in translating many of my thoughts about exercises to perform into the actual steps that became part of the book. While she may be new to the publishing business, she is certainly not new to me – she’s my daughter! She groans when I explain how she came to work with me because apparently we see it differently (how unusual is that?), so suffice it to say that my version of the story is on the acknowledgments page of the book. Regardless of the reason, I was glad to have her work with me on the book and am still glad to have her help on various BI engagements.
Second, this book was a complete rewrite. Any similarities to the previous Reporting Services books are structural only, stemming from the fact that I’ve been working with and teaching Reporting Services in the classroom for many years. Since 2002, I think? Yes, that is before Reporting Services 2000 released but I was privileged at the time to participate in some projects that gave me exposure to the product long before RTM. I’ve had similar opportunities with SSRS 2005 and SSRS 2008. At any rate, I tend to think about the progression of topics the same way, although features and functions have changed. Now the difference between the 2000 and the 2005 product was not huge, so the 2005 Step by Step book was an update to the 2000 book with revisions made and new content added where appropriate. But the changes in SSRS 2008 were considerable and warranted a fresh start on the entire book. In the years since the 2000 book, I have taught many more classes and developed many more reports which gave me some different experiences and perspectives to draw upon and to incorporate into the book.
There are a lot of great new features in SSRS 2008, all of which are covered in the book. I have some projects underway now where I wish I could be using the Tablix data region which would simplify things considerably. In one particular case, I need matrix behavior on columns, but table behavior on rows. That is, I want to group rows, but I want the groups to stack one on top of the other in a single column, but I want to have column groups expand dynamically. (Add to the scenario the fact that I have to use an MDX query and things get really interesting!) The Tablix can handle this design quite easily and is a crowd pleaser in demos to people familiar with early versions of SSRS. I guess I promised a blog about this once upon a time in my SQLSkills blog, so I will work that in soon. Of course, I cover the Tablix in depth in the book! Along with other great features like the new chart controls, the new report designer features, and more…
Configuring Reporting Services Security Correctly
If you find an error in this document, or want to share a Reporting Services security-related error that you've come across, please shoot me an email. I'd love to hear from you and your help in keeping this document up-to-date with known errors would be a great service to the Reporting Services community.
Teaming up with Kimberly Tripp and the gang at SQLSkills
Connecting at Connections
 |
SQL Server Magazine Connections is coming again to Las Vegas in November and I'm happy to be speaking there again. Not only is it a great opportunity to meet new people and reconnect with old friends, it's a treat to be able to go to "work" in my home town. Oddly enough, I don't do any work in Vegas, so driving anywhere other than to the airport (or of course to local establishments for social reasons, I really do leave the house on occasion!) is a strange experience. But one that I look forward to next month! Stop by and say hello if you're attending. Here are the topics I'll be covering: |
Configuring Reporting Services Security Correctly Understanding the relationship between Reporting Services and ASP.NET, IIS, the database, and Windows security is vital to establishing the appropriate security policy for your environment and configuring the report server correctly. This session starts with a review of the security architecture of Reporting Services in native and SharePoint integrated mode. This session also includes a series of practical examples that show you how to set up IIS authentication, service accounts, and Reporting Services data sources for various deployment scenarios, including single server or multiple server environments. You’ll see the Reporting Services security architecture. You’ll understand the dependencies between Reporting Services, ASP.NET, IIS, database, and Windows security layers. You’ll learn how to configure IIS, service accounts, and data source credentials to support security requirements. Creating Reports with Reporting Services 2005 and Analysis Services 2005 With the introduction of Reporting Services 2005, you now have a Query Designer to generate the MDX required to retrieve data from a cube as a great first step, but you can extend this query to satisfy more complex reporting requirements. In this session, you’ll learn several tips and tricks about working with Analysis Services 2005 as a source for your reports, including how to customize an MDX query, how to handle aggregate values, and how to implement cascading parameterized MDX queries. You’ll understand the benefits and limitations of the MDX Query Designer. You’ll learn how to modify an MDX query to produce specific results. And you’ll learn how to override default parameter behavior to create cascading parameters. Data Mining for the Rest of Us Don’t worry if you don’t hold a PhD in data mining or even if you didn’t take statistics in college. You can still use and, better yet, understand data mining now that Data Mining Add-Ins for Office 2007 is available. Whether you regularly analyze data now or provide technical support for those who do, it’s time to learn how take business intelligence to the next level in your organization. This session will show you specific examples for exploring common data sets, such as sales and financial data, to find the hidden information in your business. You’ll understand the technical architecture requirements for the Data Mining Add-Ins. You’ll learn how to prepare data for data mining. You’ll learn how to apply data mining techniques to specific business problems. |
Data Mining for the Rest of Us
Searching BI Data in MOSS
Because most people that I've talked to are not implementing Kerberos yet (although I highly recommend you reconsider it as it makes security implementation easier!), I've written these instructions under the assumption that NTLM authentication is being used and hard-coded credentials in Excel and/or SSRS are being used to authenticate the user for the reports' data sources.
This particular example I've developed assumes one Analysis Services source - for Excel reporting - and one SQL Server source - for SSRS, but you shouldn't assume this means that you can't use SQL Server as a source for Excel reports or SSAS as a source for SSRS. You just need to create the Business Data Catalog (BDC) as appropriate for your source and associate it with Web part pages as an action. The key difference between the two sources for the BDC is how you pass parameter values to the Web part page.
Here are thumbnails linked to the full images...
First, the results of a search with the two links that I show later highlighted here in red:
Link to full-size image
Here is the link to an Excel Web part page:
Link to full-size image
And finally the link to an SSRS Web part page:
Link to full-size image
Showing Server Aggregations in Reporting Services
Based on verbal feedback I got at the SQL Server Connections following my session, "Creating Reports with Reporting Services 2005 and Analysis Services 2005," one of the most popular topics I covered was the use of server aggregations in a report. My friend Reed Jacobson touched on this on the Hitachi Consulting BI Blog, but he didn't spell out the details there. Books Online doesn't provide many clues either, nor can I find anyone else writing about the topic - my apologies if I missed something somewhere. Consequently, I thought it would be helpful to explain what the Show Aggregates button does in the MDX query designer and how to get server aggregates from the cube.
Why Use Server Aggregates?
First - why would you care about getting cube aggregates? Can't you just use the Reporting Services aggregate functions (sum, min, max, count, etc.) to do the necessary calculations? Yes and no. The built-in aggregate functions in SSRS work just fine if your measures are all additive. But you run into a problem with semi-additive measures - like inventory counts or general ledger balances that add up nicely across products or accounts, respectively, but not across time. That is, you don't add the inventory you had on the last day of January to the count you had on the last day of February (and any days in between if you track inventory more frequently) to determine your total inventory for March 1.
You also have a problem with non-additive measures, like ratios, because you can't add up the ratios for each member of a dimension to determine the total for the dimension. For example, if you calculate a gross margin percent for each product sold as (Sales - Cost)/Sales, the gross margin percent calculation for all products is not the sum of each product's individual gross margin. You must calculate total sales, subtract total costs, and then divide the result by total sales to get the correct value.
In both examples, the correct answer is obtained only by using server aggregations. After all, one of the key reasons to use a cube is to get aggregated values computed correctly no matter what type of measure it is: additive, semi-additive, or non-additive.
How to Define the Report Layout to Use Server Aggregates
The Show Server Aggregates button on the Data tab - using the MDX Query designer - is tempting, but it doesn't appear to do anything. It's job is simply to show you what server aggregates are available to the report, but it doesn't tell SSRS to get them. Instead, all the work is done in the report layout which seems a bit counterintuitive to me. After all, the purpose of a dataset is to get data and the purpose of layout is to define where that data goes. In this case, the layout affects the MDX query in the dataset - an interesting way to go about it, but workable nonetheless.
The secret to accessing server aggregates is to use the SSRS Aggregate function in the report layout and to define a grouping in the data region. So, using the AdventureWorksDW sample cube that ships with SSRS, let's start with a simply query with the following attributes: Sales Channel, Date.Calendar Year, Sales Territory Group. Add the Gross Profit Margin measure. Now add a table to the report layout and drag Sales Territory Group from the Datasets window to the detail row in the first column and drag Gross Profit Margin to the detail row in the second column and also in the table footer row. By default, the Sum aggregate function is applied, which will yield an incorrect result. But that's okay for now. Let's look at the MDX at this point. Switch back to the Data tab and click the Design Mode button to see the following query:
SELECT NON EMPTY { [Measures].[Gross Profit Margin] } ON COLUMNS, NON EMPTY { ([Sales Channel].[Sales Channel].[Sales Channel].ALLMEMBERS * [Date].[Calendar Year].[Calendar Year].ALLMEMBERS * [Sales Territory].[Sales Territory Group].[Sales Territory Group].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
The query returns values for each combination of Sales Channel, Calendar Year, and Sales Territory. In other words, the All member for each dimension is excluded from the query - no server aggregations are returned.
Now let's get some aggregate action going. Switch to the Layout tab, and insert two groups into the table. In the first group, set the expression as =Fields!Sale_Channel.Value and set the second group's expression as =Fields!Calendar_Year.Value. For this demonstration, I like to keep the layout simple, so keep only the group header or the group footer selected, but not both. Be sure to add the fields to the respective rows in column one to show the data in the report. Now drag Gross Profit Margin into the second column of each group row.
Does adding groups change the MDX query? Let's see. Switch back to the Data tab. Nope - nothing's changed. Switch back to the Layout tab for the final step. Change the Sum function to Aggregate in the group rows and the table footer row such that the expression now reads as follows: =Aggregate(Fields!Gross_Profit_Margin.Value). You might want to set formatting styles on the group rows to make them easier to identify and set the formatting on the gross profit margin column to p2 to make the values easier to read. When you're ready, preview the report. If the final row in the report shows 11.43%, then you know the correct value is being retrieved from the cube.
If you were to use SQL Server Profiler to capture the query, you'd see a different MDX query than we see in the dataset. Switch back to Data and you can see the query hasn't changed. Even if you click the Design Mode button to toggle back to the graphical query designer and then click it again to return to the generic query designer, you won't see a change here. It all happens under the covers - the grouping and the Aggregate function tell Reporting Services that the MDX needs to be altered to get the aggregates from the cube.
Does this mean you can only see what's happening if you use SQL Server Profiler? No. Now let's try the Show Aggregages button. You need to toggle to the graphical query designer if it's not visible on the Data tab. Then click the Show Aggregates button. The aggregates appear as shown here:
The null values really represent the All member for the attribute represented in that column. So the very first line should read Internet | All Periods | All Sales Territories | 0.4114... if the name of the All member were retrieved. If you now switch to the generic query designer (click the Design Mode button), you can see the MDX query has changed:
SELECT NON EMPTY { [Measures].[Gross Profit Margin] } ON COLUMNS, NON EMPTY {[Sales Channel].[Sales Channel].[Sales Channel].ALLMEMBERS * {[Date].[Calendar Year].[All Periods]} * {[Sales Territory].[Sales Territory Group].[All Sales Territories]}, [Sales Channel].[Sales Channel].[Sales Channel].ALLMEMBERS * [Date].[Calendar Year].[Calendar Year].ALLMEMBERS * {[Sales Territory].[Sales Territory Group].[All Sales Territories]}, ([Sales Channel].[Sales Channel].[Sales Channel].ALLMEMBERS * [Date].[Calendar Year].[Calendar Year].ALLMEMBERS * [Sales Territory].[Sales Territory Group].[Sales Territory Group].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
The query now includes the All members - All Periods, All Sales Territories - for the attributes that are used in the grouping of the table. You don't need to follow these steps to get the query right in the dataset before you deploy. Reporting Services knows what to do when it sees the grouping and the Aggregate function. However, as the report developer, you may want to see the query generated to retrieve server aggregates and make modifications to suit your needs more specifically.
