Someone asked me if we could create a running total of working days per month that could easily be placed in a Date dimension, so I cooked up this CTE

WITH   mycte as
        (
        select cast('2000-01-01' as datetime) DateValue,
        case when datepart(dw, cast('2000-01-01' as datetime) -1) < 6 then 1 else 0 end IS_WEEKEND
        union all
        select DateValue + 1, 
        case when datepart(dw, DateValue) < 6 then 1 else 0 end IS_WEEKEND
        from    mycte   
        where   DateValue + 1 < '2050-12-31'
        )
select DateValue as CAL_DATE
,       datename(dw, DateValue) CAL_DAY
,       datepart(dw, DateValue-1) CAL_DAY_IN_WEEK
,       datepart(dd, DateValue) CAL_DAY_IN_MONTH
,       datepart(dy, DateValue) CAL_DAY_OF_YEAR
,       datepart(ww, DateValue) CAL_WEEK
,       datename(mm, DateValue) CAL_MONTH
,       substring(datename(mm, DateValue), 1, 3) CAL_SHORT_MONTH
,       datepart(mm, DateValue) CAL_MONTH_IN_YEAR
,       datepart(qq, DateValue) CAL_QUARTER
,       case when datepart(mm, DateValue) < 7 then 1 else 2 end CAL_HALF_YEAR
,       datepart(yy, DateValue) CAL_YEAR
,        IS_WEEKEND
,        (SELECT sum(IS_WEEKEND) FROM mycte 
              WHERE 
              DateValue <= m.datevalue 
              and datepart(yy, DateValue) = datepart(yy, m.DateValue) 
              and datename(mm, DateValue) = datename(mm, m.DateValue) 
         ) AS WORKING_DAY_NO

FROM    mycte m

OPTION  (MAXRECURSION 0)

The results look like this

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