For those who missed it, the launch date of SharePoint was announced. The launch will be done by President of the Microsoft Business Division Stephen Elop as he introduces Office and SharePoint 2010 on May 12, 2010 at 11 a.m. EST.
You can join him live at: http://sharepoint.microsoft.com/businessproductivity/proof/pages/2010-launch-events.aspx#fbid=JadoeNy2pBe
I just can’t wait to get PowerPivot to production !
Last weekend i was at the dutch SQLZaterdag (SQLSaturday) where I attendend a session from Hans Geurtsen who showed us the new features of Reporting services 2008 R2. Among others he showed us the new Group Domain Scope property to align columns, i just had to find out how this works. I knew of a excelent blog post by Sean Boon where he used the Domain scope property to align the axis of a sparkleline.
Ok what is the problem, lets say we have a few tablixes with sales per month, not every year has sales for all months, this would result in the following report:
What we would like to do is align the months so each month will be shown at each tablix, i designed the report to place the Tablix within a list.
I named the parent list “Maintablix”, the Group Domain Scope needs to have a scope to sync the axes against. You cannot just add tablixes and sync them, you need to have one parent tablix to sync your child tablixes against. When you create a report like this you can add the parent tablix name to the DomainScope property to your column properties of the child tablix:
this will result in the following report, as you can see the columns are now aligned:
This is a pretty nice feature that could prove very handy, but what I really would like to see to sync axis over multiple tablixes, i couldn’t get that to work and i don’t think that is possible.
A few months ago i was nominated for “Dutch BI consultant of the year” at the BI dutch special interest group http://www.bidutch.nl/. The criteria was the sharing of knowledge, I was nominated because of this blog and my upcoming sessions at SQLPass and SQLZaterdag. The results were determined by an online poll so the voting was open for everyone.
Yesterday at the BI Dutch self service event the results were published. I won by 2 votes 
Check out the results at bidutch. I would like to thank everyone for voting for me (I love social media) and Johan van der Kooij for nominating me. A great honor to be chosen. The other consultants are really very good, i even have Peter van Tils books at home.
So onward to more blogging and speaking ! Thanks everyone !
I recently found a great PowerPivot feature to make the published PowerPivot sheet “view only” for a end user.
When you have write permissions for a PowerPivot sheet you can open the sheet in Excel and use all the measures and columns and use everything available in the sheet by using the “Open in Excel” button:
Now the great news, you can contain these permission to view only, documents can only be viewed sever side:
When you open the PowerPivot sheet in the browser you see the Open in Excel button is gone:
When we open the PowerPivot gallery as a default document library you will not be able to download the Excel sheet, it will render as html when you download it. I think this will be a very usefull permission !
Last week i made a blog post about the power of the “Add business intelligence” wizard to add the current year to date. I decided to extend the script to add 2 new values with Previous YTD and current period last year.
All I did was copy and paste the automaticly created script and change the PeriodsToDate to get 12 months before [Date].[Dates].CurrentMember (last year) using Parallelperiod and give the measure a new name. This results in the following script:
Create Member
CurrentCube.[Date].[Date Calculations].[Previous YTD]
As "NA" ;
Scope(
{
[Measures].[Sales Amount]
}
) ;
// Year to Date
(
[Date].[Date Berekening].[Previous YTD] ,
[Date].[Calendar Year].[Calendar Year].Members,
[Date].[Calendar Month].Members
)
=
Aggregate(
{ [Date].[Date Berekening].[Current Periode] }
*
PeriodsToDate(
[Date].[Dates].[Calendar Year],
Parallelperiod(
[Date].[Dates].[Calendar Month],
12,
[Date].[Dates].CurrentMember
)
)
) ;
End Scope ;As second member i created a current period last year:
Create Member
CurrentCube.[Date].[Date Calculations].[Current period last year]
As "NA" ;
Scope(
{
[Measures].[Sales Amount]
}
) ;
// Year to Date
(
[Date].[Date Berekening].[Current period last year] ,
[Date].[Calendar Year].[Calendar Year].Members,
[Date].[Calendar Month].Members
)
=
Aggregate(
{ [Date].[Date Berekening].[Current Periode] }
*
Parallelperiod(
[Date].[Dates].[Calendar Month],
12,
[Date].[Dates].CurrentMember
)
) ;
End Scope ;As you can see here we have removed the periodstodate and kept only Parallelperiod to get 12 months before [Date].[Dates].CurrentMember (last year).
This way you can add your own time intelligent functions very easy.
Having played with Data mining in combination with PowerPivot in a previous blog post, i wanted to do data mining at the company i’m currently consulting at, they use Excel 2007 in combination with SSAS. In excel 2007 we do not have the power of the flattened pivot table. So i had to think of a way to convert the pivot table to a regular excel table. And i found a way, since i have converted all my excel version to 2010 i had to find an older image with Excel 2007 to make this post. I have used the dutch Excel version in the screenshots, i hope you can follow it.
Let’s say i want to data mine on the Adventure works cube. First step is creating a regular pivottable containing all the values you want:
Next we want to put all rows into a regular column, right mouse click on the pivot table, and select Pivot table options, Go to the display tab and select classical pivot table layout. Press ok.
This will result in all rows being put in a column:
Next we remove all totals since we don’t need this in this data mining example, go to design, subtotals, do not show subtotals.
Ok we have made our data ready to be converted to a regular table, we can do this in 2 ways. Copy all cells and paste them in a new sheet or convert to formula’s and use that.
I copied and pasted the cell to a new worksheet, used the keep only values as paste option. This results in the following table:
the problem here is the blank values in the excel sheet, to fix this i found a solution on the web at Mr Excel.
Start by selecting all the columns we want to fill the blanks of, go to the home tab and select “find and select”, and click “Go To special”. Here we can select blanks and click ok.
This will result in all the blanks being selected. Type =, press the up arrow and press Ctrl+Enter (that’s hold down Ctrl and press the Enter key). All the empty cells will be filled with the value of one row above. Exactly what we wanted.
Now convert it into a table (Ctrl – L) and you are a ready to unleash the data mining beast:
I’ve been using SSAS in combination with Reporting Services for a few years now but i have always found it very cumbersome to deal with YTD and other time intelligence functions in combination with SSRS. A few days ago i read this whitepaper: Translating Cognos PowerPlay Transformer Relative Date Functionality into SQL Server Analysis Services. In this paper they use the “Add business intelligence” wizard to add time intelligent members to the time dimension, I’ve seen the button but never tested it out.
This whitepaper made me do some testing. To do so i’ve created a cube based on the adventureworks database. I’ve created 2 dimension, and used the sales amount in the fact table.
In the date dimension I’ve created a hierarchy:
Now for the magic bit:
We run the Business Intelligence wizard to add a time intelligence functions
We can pick multiple calculations to be created
You have to make sure you pick the date hierarchy so the date calculation can determine the relationship between the different levels.
Last step is picking the measure we want to use the functions with
Now we can click finish, the time intelligent functions will be implemented. This is done by adding a calculated member to the time dimension as we can see here:
This will add a attribute with 2 members to my date dimension, one which shows the measure value for the current month selected and member that shows the year to date to the month selected:
we now can use this member in a query, in for example a SSRS report:
As you can see we now have the sales amount of the selected month from the selected month and the sales amount of the current month year to date. Putting this in a tablix makes it into the following report:
This is great stuff, makes YTD very easy to implement !
I don’t know how i could have missed this great wizard for all this time. I’ll use it for sure very soon at the next project.
I recently had a question concerning reporting services, they wanted to compare sales of a month to the average sales per month like in the report i reproduced in the report below:
This is not easy in reporting services 2008 and before. To solve this problem we need to divide the total sales by the number of months, which you can obtain by using the CountRows function. But then you have a problem with the country’s. It would be solvable, but using a lot of tricks.
Suddenly i remembered a blog post from Robert Bruckner which mentions the new features of SQL Server 2008 R2:
Aggregates of Aggregates
This enables report authors to nest RDL aggregate expressions inside other RDL aggregate expressions with unlimited nesting levels. For example, the expression =Avg(Sum(Sales, “Month”), “Year”) would compute the average total monthly sales
This could solve my problem very easy using the new expression:
=avg(Sum(Fields!Internet_Sales_Amount.Value),”Calendar_Year”)
This will give us the avg of the total sum of sales per month per year. We then compare it to each other and determine the percentage.
Using the aggregates of aggregates functions could make my life a lot easier in my daily work, just like the Lookup function i talked about before.
This screencast will show you how we create a dashboard on a SSAS cube in PerformancePoint 2010 using a filter with time intelligent functions:
A few important points:
Pretty though demo to do 10 minutes, please don’t hesitate to ask me questions here.
Big thanks to these blog posts:
http://consultingblogs.emc.com/davidfrancis/archive/2007/12/02/PerformancePoint-Time-Intelligence-_2D00_-BI-for-the-masses-Part-1.aspx
http://blogs.msdn.com/performancepoint/archive/2009/12/14/configuring-data-source-time-settings-for-time-intelligence.aspx
http://blogs.msdn.com/performancepoint/archive/2010/01/21/time-intelligence-formula-quick-reference.aspx
And Dan English to point out to me i have to use the Hierarchy to add multiple Time Member Associations at the Ms forum:
http://social.msdn.microsoft.com/Forums/en-US/sharepoint2010general/thread/5f7f3e9b-1648-48c7-8cdf-8e3fb211b0ae?prof=required&ppud=4
The SQL Server Data Mining team is working to extend the power and ease of use of SQL Server Data Mining to the Cloud. You can use this add-in to run datamining against a service in the cloud, you don’t need a SSAS instance installed ! Check it out on this page: http://www.sqlserverdatamining.com/cloud/
Here is what the add in looks like in Excel 2007:
So you can just start using datamining with PowerPivot as we have seen in my previous screencast but then without having SSAS installed on your network.
The guys from the data mining team even build an online data mining tool, so you don’t even have Excel installed, here you can load data from CSV files or from
