I’ve got a weird problem importing an Excel linked table into PowerPivot. The dutch dates field (of type short date) seems to be interpreted as an US date during import in PowerPivot, but presented in dutch format again.
Orginial linked table:
After import in PowerPivot (look at the 1-12-2010 converted to 12-1-2010):
I already checked regional settings, but they are on dutch.
Anybody a clue?
After a few requests to post the PowerPivot worksheet used in the Exact Globe integration blogposts (thanks Ronald, you were the first) I have uploaded the worksheet file I’m using. You can find it on the right under “Links”: “Exact PowerPivot Sample.xlsx”. I will keep it updated after every blogpost.
Have fun playing around,
Bas
After having modelled all the Exact data the PowerPivot Data Model looks like this:
Now let’s try to create a Profit&Loss report on Exact by:
You will get a similar result as shown down below:
I personally hate to see the turnover (“Omzet” in dutch) to be negative. So to change that I add another calculated column in AccountDetails called RealAmount and hide the original Amount:
And now add the RealAmount into the Excel PivotTable so the result looks like:
To make the Exact PowerPivot model more intuitive for end users and easier to navigate I want to reshape the data model. To do this I need to:
 |
=> |
 |
 |
=> |
 |
 |
=> |
 |
 |
=> |
 |
 |
=> |
 |
You will see that the end result is much easier to grasp then the original:
 |
=> |
 |
Now I have to repeat the same steps on all other tables.
While we are doing quite some exciting Business Intelligence things for our customers we don’t really give the good example as in ‘dogfooding’ what we preach. Or what we refer to in Holland “The shoemaker’s children are walking bare feet”. I have the feeling this doesn’t make any sense in English.
Although we are 40 employees in size, even then information management can get complex. And because the management team is partly responsible for some insights they are also still partly active in the field, causing some reports to be available less frequent then you want them to be. So, time to make a change!
I was already getting excited by the unlimited possibilities you would get by being able to shape just the financial system to gain new insights. We are using Exact Globe 2003 which is the most popular financial system in The Netherlands. So the next couple of days I will show importing, shaping and visualizing data from Exact.
When first opening up the database of Exact the amount of tables is overwhelming and one has to determine which tables to use. Luckily I have found the database documentation here:
Exact Globe-Synergy Database Documentation Startpage.
In summary: to get an overview of the general ledger, debtors, creditors and costcenters I found out that I had to use:
Now I can import the Exact database (typically “999” for the demo database) using the import SQL method using the PowerPivot add-in (see also earlier blogpost here).
update: for each table use the “Preview & Filter” button to only select the necessary columns:
After pressing finished you’ll see:
To just get an impression of the data I quickly addes some fields to the slicers (year and month) and the pivot table rows (cost center) and values (amount).
In the next blogpost I will be modelling the data to make it more intuitive for the end user to use and navigate.
Today when I attended the PowerPivot BI workshop in Schiphol by Marco Russo and Alberto Ferrari somebody pointed out an easy way of getting the last date of the previous month using DAX. It seems that:
If you use the DATE() function and use 0 as the day it returns the date of the last day in the previous month.
For example:
Gives you:
Yesterday I watched the SQLPass keynote and was very impressed with Amir Netz’s story about letting your data tell your business story with a new webbased, self service tool codenamed “Crescent”.
Crescent allows you, as an end-user, to take your (PowerPivot model and) data and:
Please take 5 minutes of your time to watch a part of the keynote:
(the whole keynote is available here: http://www.cmcgc.com/media/stream/301110/Gen1.wmv)
or watch the teaser trailer of Microsoft here: http://blogs.msdn.com/b/bi/archive/2010/11/09/data-visualization-done-right-project-crescent.aspx
After getting all the data into PowerPivot and have relations defined we want to build some dashboards (BI applications if you will) to visualize the data in such a way it will be interpreted in the right way.
I used the Contoso demo database here to get some data and want to find out the top 10 best performing store last year based on sales amount.
To do this, we either use one of the PivotTable options in the PowerPivot window:
Or we go back to Excel and use the same option on the PowerPivot ribbon:
In this case we will create a regular PivotTable on the existing sheet:
Let’s drag some fields to it:
But we immediately see that there are also online stores in between the results:
To filter these Microsoft has brought a new feature to Excel 2010 called Slicers. In this case we want to filter on the channels. To do this just drag the DimChannel::ChannelName to the slicer field:
And click on the Store value in the slicer:
And the result will be immediately filtered over all the FactSales of 3.4 million rows:
Now do the same thing for Year to filter on 2009:
Now we just ask for the TOP 10:
And we sort the results from big to small:
And here is the end result 
Yesterday Microsoft had an important announcement at the PDC 2010 for PowerPivot users: Windows Azure Marketplace Data Market, which will be an online datafeed market for usage in applications or BI solutions like PowerPivot! This makes our life a lot easier in discovering and combining our Excel/PowerPivot data with public or subscription based sources like house pricing per region, or crime rates, or income, etc
To use the Azure Data Market data in Excel an/or PowerPivot, Microsoft has released two add-ins:
I will make a blogpost soon to show some direct use.
To show how easy it is to import data in PowerPivot I will take you through the process:
After installing PowerPivot in Excel 2010 we received an additional ribbon with the name “PowerPivot”:
From there we can start the PowerPivot Window by clicking the icon (see above).
We can see below that there are multiple possibilities to import data into PowerPivot, varying from database sources to RSS/Atom feeds to Reports. I can see you thinking….why reports?! Well, reports are already a known (and certified/proven) source of data to end users. In a lot of cases this will be a good start for data to mix it up with other internal or external / outside data. In quite some ad-hoc information requests the end user merely wants to have a few other fields added to his/her report.
In this case we will go for the Microsoft SQL Server option and import a few Adventure Works sample database tables:
After pressing close in the second screen we can see that PowerPivot imported all the data. Refreshing this data in Excel is only possible manually. Later we will see that once uploaded to SharePoint we also have the ability to automatically refresh the data.
On the design ribbon we can also check which relationships between the tables were automatically detected by PowerPivot. PowerPivot uses different methods to search for relationships including searching in the data itself. For more information on this topic see this blogpost on MSDN or this blogpost from Kasper.
We won’t add calculated columns at this moment, but want to go straight into analyzing the data in a pivot table using the Home ribbon and then the PivotTable option:
From there we get the known, good ol’ Excel user interface:
Followed by a pivottable shown in Excel.
We now quickly added SalesOrderHeader.SubTotal to the Values box and Customer.Title to the Row Labels box to see that there are more men buying bike thingies then females:
