This is a pretty good resource for those people wanting to get started with the latest versions of the Microsoft BI stack.

Download details: SQL Server 2008 R2 Update for Developers Training Kit (January 2011 Update)

Sadly, out of the box, you can’t connect from BIDS 2008 (the SQL 2008 or 2008 R2 version) to TFS 2010. I guess this will come with Denali where hopefully the new Juneau Development Environment will be integrated with TFS 2010.

I managed to get SQL Server 2008 R2 BI development tools (BIDS 2008) to work with TFS 2010 by doing the following:

 1. Install BI Development Tools that come with SQL Server 2008 R2

2. Install VS2008 Team Explorer (for BIDS/VS2008 from MSFT available here)

3. Install VS2008 Service Pack 1 (available here)

4. Install  VS2008 SP1 Forward Compatibility Update for TFS 2010 (available here)

Once I had done that, I was able to connect to a TFS 2010 Project and successfully check VS2008 projects in and out of source control, raise bugs etc.

Have attached a few screen grabs showing the integration from BIDS and also from the Web Client Portal (basically Sharepoint for TFS) showing some of the out of the box dashboard stuff.

Note that when entering a TFS server to connect to in BIDS, a change from TFS 2008 means that you now need to fully qualify your TFS Project

When asked for which TFS Server to connect to, type http://VM2008BITFS:8080/TFS (assuming you use default ports etc)

Team Web Access in TFS 2010

Portal Dashboard in TFS 2010

BIDS check-in & source control access (similar to TFS 2008)

I received email confirmation today of my registration on the three-day deep-dive course on Analysis Services 2008 R2 and subsequent possibility of joining the SSAS Maestro Program. This is exciting as it’s going to be a real challenge to pass!

The new SSAS Maestro Program a three-day, deep-dive course on Analysis Services 2008 R2 gives architects and consultants the education and hands-on experience needed to deliver highest scalable OLAP solutions. Prepared and presented by top industry experts and the SQL Server Analysis Server team, this intensive course gives top SSAS professionals the education and hands-on experience needed to deliver highly complex and highly scalable OLAP solutions using Analysis Services 2008 R2. Today, there is increasing need for expertise to architect deliver and maintain mission critical OLAP deployments and the primary objective of this course is equip you with the latest SSAS best practices and case studies.

Agenda

Module 1: Analysis Services 2008 R2 and Large Cubes

· Overview and purpose of course

· Summary of modules

· Description of how course participation is evaluated

Module 2: Tools for Monitoring and Optimizing Analysis Services

· Introducing Analysis Services Monitoring

· Executing Queries and Operations with Ascmd

· Monitoring Queries with SQL Server Profiler

· Monitoring the Server with Performance Monitor

· Using the Resource Monitor Project

· Using BIDS Helper

· Using Other Monitoring Tools

Module 3: Internal Data Storage Structures

· Understanding Internal Data Storage Components

· Examining Data Structures for Attributes

· Examining Data Structures for Hierarchies

· Examining Data Structures for Partitions

· Optimizing Hardware for Random I/O

Module 4: Memory Management

· Switching from the Analysis Server Heap to the Windows Heap

· Tuning the Economical Model of Memory Management

· Running Analysis Services With Other Software

· Tuning Memory Options for Processing

· Preallocating Memory to the Analysis Server

Module 5: Thread Management

· Understanding the Trade-Offs in Thread Management

· Examining the Thread Pools

· Configuring the Number of Available Threads

· Handling Long-Running Queries

· Monitoring Thread Usage

Module 6: Designing Partitions

· Understanding Partitions

· Sizing Partitions

· Defining the Partition Slice

· Partitioning for Query Performance

· Partitioning for Processing Performance

· Partitioning for Discount Count Measures

· Using Solid State Drives for Discount Count Partitions

Module 7: Designing Aggregations

· Understanding Aggregations

· Considering Aggregations in Large Cubes

· Designing Aggregations

· Deciding for Each Attribute

· Tools and Tips

Module 8: High Performance ROLAP Partitions for Large Cubes

· Choosing ROLAP Instead of MOLAP

· Simplifying the Cube Structure

· Working With Aggregations

· Developing a Partitioning Strategy

· Examining the ROLAP Benchmarks

· Examining Processor Utilization

· Handling Skewed Data Distributions

· Reviewing Lessons Learned and Best Practices

Module 9: Large Cube Processing Strategies

· Understanding the Steps of Dimension Processing

· Choosing a Dimension Processing Option

· Examining Processing of Special Dimension Types

· Examining Processing and Storage Modes

· Understanding the Steps of Cube Processing

· Choosing a Cube Processing Option

· Optimizing Processing

Module 10: Advanced Dimension Design Best Practices

· Working With:

· Parent-Child Hierarchies

· Unary Operators

· Many-to-Many Dimensions

· Referenced Dimensions

· Diamond Shaped Hierarchies

· Natural Hierarchies

· Junk Dimensions

· Degenerate Dimensions

· Reviewing Design Best Practices for the Source Dimension Table

· Handling Slowly Changing Dimensions

Module 11: Query Performance Monitoring and Tuning

· Identifying Query Problems

· Checking on Partitions and Aggregations

· Implementing a Cache Warming Strategy

· Switching to Subspace Computation

· Optimizing the IIF Function

· Removing Empty Tuples

· Examining Other MDX Optimization Strategies

· Using Analysis Services Features Instead of MDX

· Finding Alternatives to Session Cubes

· Moving Calculations to the Relational Engine

Module 12: Scale-Out Querying with Read-Only Databases

· Considering Reasons for Scale-Out Querying

· Examining the Options for Scaling Out

· Configuring the Architecture

· Balancing the Load Across Multiple Query Servers

· Updating the Analysis Services Database

· Copying the Database Files

· Optimizing the Storage System and I/O

SSAS Maestro Program Course Assessment

Note that this course is unlike many other Analysis Services courses. Attendees that satisfactorily pass the assessment will be endorsed by Microsoft, and subsequently promoted within the SQL Server community and recommended for suitable engagements by SQL CAT team.

Assessment will be based on:

• 100% attendance during the three-day course
• the consistent ability to contribute to, and at times lead, discussions
• the satisfactory completion of all labs, and
• successfully passing a comprehensive exam of a ~4000 word case study due 30 days after the course completion that demonstrates an applied deep understanding of the product and technology, and strong written communication skills

SSAS Maestro Program Benefits

Upon successful completion of this course, assessment and case study evaluation by industry experts, attendees will:

• Be a part of the elite group of SSAS Maestros
• Help Microsoft accounts teams deploy highly complex/highly large SSAS projects
• Have access to webcasts and Q&A sessions on the learnings some of the most complex SSAS implementations directly from SQLCAT and the Analysis Services team
• Be showcased on a special section within the Microsoft SQL Server Web page for the SSAS Maestros

Continuing on from Part 1 and Part 2, our final section is going to complete the customisation of our report.

Having created and populated our Paramaters and Lists Worksheets, let’s now turn our attention to the Report Worksheet. Here’s how we left it

With some very simple formula replacements and a bit of formatting, we can turn it in to this

Enter the formula in column B as per the picture below – given the way the parameterized formula is constructed, it should be possible to simply enter =CUBEVALUE(pCube,pDept,pMeasure,$A7,B$6) in cell B7 and copy down to B13 and across to column G.

For Row 6, enter the formulas in the cells as follows

B6 =CUBEMEMBER(pCube,mdxPer5)
C6 =CUBEMEMBER(pCube,mdxPer4)
D6 =CUBEMEMBER(pCube,mdxPer3)
E6 =CUBEMEMBER(pCube,mdxPer2)
F6 =CUBEMEMBER(pCube,mdxPer1)
G6 =CUBEMEMBER(pCube,mdxPerSeleted)

Column A’s formulas can be left as they are for now.

Now you should be able to change the Department, the Fiscal Year and the Month at will in the parameters sheet and the results will be shown in your report, including changing the report name and the column headings (B:G) to correctly reflect the prior six months to the reporting date.

I also feel that workbooks constructed this way are so much easier to audit.

By replacing hard coded cell values with sensibly named named ranges, it makes it easier to see that cell B7 for example refers to the cells immediately above and to the left of it (which are obviously something to do with a date period and an account or group of accounts), and that it’s value is also determined by the variables pCube, pDept and pMeasure. These can easily be located (and the current values of them can be viewed) in the workbook using the Name Manager utility that can be launched from the formulas tab in the Excel Ribbon.

Let’s move on to Ken’s request for changes to the P&L. We haven’t looked at column A yet. Let’s move some things around so that the P&L makes more sense and shorten the formulas in A7 to A13 as follows

Now if we look at the report, we can see that it makes more sense as a P&L… Gross Sales, less Returns, Adjustments & Discounts leaves us with Net Sales, less Cost Of Sales = Gross Margin, less operating Expenses gives us our Net Profit. Hey it’s starting to make sense. But Ken’s still not happy – he needed more detail than just a single line of “Operating Expenses”.

In your Report Worksheet, insert 6 new rows between Gross Margin and Operating Expenses. Also insert a row between Column A and Column B. Now we need to go and find  what the accounts are that make up the Operating Expenses – we do this by either using SQL Server Management Studio and browsing the cube or if you don’t have access to the SSMS tools, we can also do it by constructing a quick PivotTable in Excel and finding them out by converting to formulas.

Let’s say Ken wanted the following subdivisions of Operating Expenses

Labor Expenses

Travel Expenses

Telephone and Utilities

Depreciation

Commissions

And wanted the remainder grouped under a heading “Other Expenses”, then here’s the formulas for how I would do it.

Note that cell C18 is basically Total Operating Expenses, less the itemised Operating Expenses that Ken wanted to see.

The end result of the report looks like the picture below – a fully parameter driven Excel Report with customisations that wouldn’t be possible within a standard Pivot Table without some customisation within the Analysis Services cube. Often this isn’t possible in the short term so I hope this servers as a small introduction to the power of the new CUBE functions in Excel, coupled with some standard functionality (lists, names etc) that not everyone is aware of.

Hope this helps

This post carries on where we left off in Part 1, with a pre-built Excel Pivot Table report that is connected to your Analysis Services AdventureWorks cube.

Let’s open up our original Excel file. You should have a report like the one below. It is already a pretty useful report, but the requirements could easily be difficult to achieve with a Pivot Table.

Let’s say for example, the Financial Controller (we’ll call him Ken) gives you the following requirements.

1. Make the report so that I can pick a Period and the report automatically shows me P&L data from the 6 months prior to the selected period.

2. I want to be able to choose a Department and only show data for it.

3. I don’t like the P&L as it stands at the moment as it doesn’t give me enough detail but the rest of the report is fine. I want the line for Operating Expenses to be itemised by a more detailed level, i.e. Labour, Travel, Marketing..

So apart from thinking “cheers Ken”, you think that the answer could be simple – Get the BI team to amend the “Summary P&L” to provide more detail for Operating Expenses. BI Team say that they can’t do that as it’s already used in 300 reports that can’t be changed. So you ask them to create a new Set including the extra information and call it “Ken’s P&L”. BI Team says yes they can do that, but they are busy with Phase 3 of the BI roll-out, so how does 3 months time sound? OK you get the picture – Ken wants this next week, but the BI team can’t deliver for 3 months.

You also cant add the detail to your rows – from your knowledge of the cube, you know that the detail exists in Level4 of your Accounts dimension (see left) because the rows already contain an OLAP “named set” based on the accounts dimension and therefore can’t have another set of members based upon that dimension. Try it and see – you’ll get an error like this

You’re not as stuffed as you think, because within Excel, you have the power to customise the report to Kens liking.

Convert To Formulas

The first thing we can do is to is to convert the whole report to OLAP Formulas. This makes the whole report now formula driven (Using CUBEMEMBER and CUBEVALUE functions) and the Pivot Table no longer exists.

Select OLAP Tools>Convert To Formulas from the Pivot Table Options menu.

NOTE: DO NOT check the box that says “Convert Report Filters” = we need them to drive Ken’s request for being able to select a Financial Period and Department. (this is where you could code this using neat Excel stuff & VBA, but why bother when you can drive it using the existing filters?). The result will be as per the screenshot below. Note how you can still select Departments & Fiscal Years.

To see all the new formulas at a glance, a neat Excel Keyboard shortcut is CTRL+`- This allows you to view FORMULAS as opposed to their RESULTS in an Excel sheets (see below).

Note how the row and column headings have changed to CUBEMEMBER functions and the Values to CUBEVALUE functions.

Note also that the CUBEVALUE functions Member_Expression parameters actually refer to Excel Cells ($B$2, $A$54 etc) – this is really useful to note – They are actually referring to Member_Expressions being generated by the CUBEMEMBER functions in the rows and columns AND Member_Expressions generated by the old Pivot Table report filter value cells (in B1 and B2 of the worksheet). This gives us our first clue as to how we can use this to make the report fully dynamic. Press CTRL+` to return to the values view.

Amend Report

OK, the next thing we need to do is to move the selection Parameters to a new sheet. Then we can keep the inputs separate from the report itself.

Select a cell within the Pivot Table filter area (in my example Cell A1) and in the Pivot Table and select the option Move PivotTable. Move it to a New Sheet and rename that sheet “Parameters”.

Rename the sheet containing the data as “Report”.

In the Report Worksheet, delete the data columns to the right of June (i.e. columns H:N) as Financial Controller “Ken” said he only wanted a report containing the six months data up to and including the period selected in the report parameter.

Lastly, set up a new Worksheet called “Lists”.

Setting up Parameters, Defined Names and some simple MDX

Cells A1:B2 in your Parameters sheet should now contain the old Pivot Table filters for Fiscal Year and Department. Set up 9 more parameters in the sheet as per the picture below.

I have placed in column D the names you should call your cells in the equivalent rows in column B (for example, B1 should be named pYear, B8 should be named mdxPerSeletced etc). To name a cell, select the cell or range you want to name, select the formulas tab from the ribbon UI and click Define Name

Now we have our ranges named (don’t worry about having no values in B4:B6 and B8:B13 for now), swith to your “Lists” Worksheet and create a table in A1:D13 as per the first picture below. In columns A & B place the values as per the picture 1, in columns C & D place the formulas as per the second picture below. Finally, Name your table (Range A2:D13 as lkpMDXPeriod and Range A2:A13 as lMonths)

Having entered the formulas correctly, you should get the results as seen in picture 1. Now move back to Worksheet “Parameters” and enter the formulas and values (values for cell B6) shown in the picture below.

For cell B4 we do something different. Select the cell, then navigate to the Data Menu tab and select “Data Validation” – set the validation settings to be derived from a List, and specify in the source box, the formula =lMonths

The pPeriod (B6) parameter should now be limited (via a dropdown list when the cell is selected) to the moths of the year (January to December).

So Now we have our Pivot Table “decoupled”, we have a “Lists” worksheet and a “Parameters worksheet set up and in the next post we will move on to formatting our report and satisfying “Ken”, our somewhat fictitious Financial Controller.  Along the way, without knowing it you have also constructed some MDX (the multidimensional expression language of SQL Server Analysis Services) which is something that often scares alot of people. More on MDX in Part 3.

Even though linking Excel Reports to SQL Server Analysis Services (SSAS) cubes via Pivot Tables is now a relatively easy thing to do post Excel 2007, many people do not realise that you don’t have to be limited to Pivot Table/Pivot Chart reporting. As an ex-accountant, I know from experience that the reports required are often much more complex than can be readily delivered using a Pivot Table in Excel. In this series of posts, I am going to show you how you can build much richer, customised reports in Excel using the new Cube functions that were introduced with Excel 2007.

This post assumes that the user has a pretty good grasp of Excel concepts such as Named Ranges, Conditional Formatting. Whilst some of the steps in this post could be enhanced via the use of VBA, this is outside the scope of these articles.

If you are wondering what Cube functions are, they can be found under the “More Functions” section of the Formulas tab of the Excel UI (see picture on the left).

The key functions we are going to be covering are

CUBEMEMBER (returns a member/tuple from the cube, taking a Connection parameter, a Member_Expression parameter and an optional Caption parameter)

CUBEVALUE (returns a Measure value from the cube, taking a Connection parameter and a Member_Expression parameter)

The Excel junkies amongst you will already see that the Connection parameter is common to both functions and therefore is an ideal candidate for holding in an Excel Named Range for a number of reasons. Firstly, it makes it easier to construct the formulas and secondly, in many BI deployments you will have different environments (Development, UAT, Production etc). By storing the Connection parameter in one place in the workbook, it makes it relatively easy to “point” the report at the different environments by simply changing one value within the Excel Workbook (as opposed to hundreds of Excel formulas).

Prerequisites

I have assumed that people wishing to develop this type of report have access to the following tools

• SQL Server Management Studio tools (and the ability to connect to an instance of SQL Server Analysis Services). The developer editions of SQL Server are usually free with Technet & MSDN subscriptions but can be purchased separately for around $50. For this post I am using SQL Server 2008 R2, however the approach used should work for all SQL Server versions > SQL Server 2005. You can also obtain 180 day evaluation editions here.
• The AdventureWorks Sample Databases & Cubes (the sample databases can be downloaded here). Note that the AdventureWorks cube will need to be deployed & processed on your development SSAS server before it can be browsed via SSMS or Excel.
• Excel 2007 or Excel 2010
• An enquiring mind

Build a basic report as a starting point

Let’s start by connecting to our OLAP Cube and building a fairly straightforward Pivot Table report. As you get more familiar with the Cube functions this probably won’t be necessary but we’ll start with a full walk-through to cater for everyone

Let’s open up Excel and create our base OLAP Cube report using a Pivot Table as shown in the picture to the left.

Follow the OLAP connection wizard as per below to set up your cube connection and select “Pivot Table Report” after clicking the finish button in step 3 below.

You should now have a blank Pivot Table report ready to populate that looks something like this

Create the Pivot Table report as per the screenshot below –

Columns = Month of Year

Rows = Summary P&L (fount under Accounts>Sets)

Filter = Date.Fiscal Year and Departments

For Part 1 of this series of posts, that’s where we will leave it. Save your Excel Workbook as OLAP PT Report 1 and we’ll carry on in the next post.

This is more of a placeholder for me for all the new information around the new Business Intelligence Semantic model (BISM) announced at this year’s SQLPass.

The model itself looks like this

Here are the best links I have found on the subjects and BI roadmap for the Microsoft stack in general:

• SSAS Team Blog Post
• Derek Comingmore’s SQLMag Blog Post
• Marco Russo’s SQLBI Blog Post