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?

Direct download link

Have fun playing around,

Bas

Now let’s try to create a Profit&Loss report on Exact by:

• Adding AccountDetails.Amount to Values
• Adding AccountClass1.AccountClass1Name to Row Labels
• Adding AccountHeader.AccountAndDescr to Row Labels
• Adding AccountDetails.BookYear and .BookPeriod to Horizontal Slicers
• Adding AccountHeader.AccountAndDescr and AccountClass1.AccountClass1Name to Vertical Slicers
• Set filter on a year (in this case 2003)
• Set filter on BalanceOrProfitLoss to “Profit/Loss”

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:

• Delete all unnecessary columns
  update: if any left to filter, after already filtering the columns at import.
  
  

  =>

• Give meaningful names to tables and columns
  
  

  =>

• Apply the right data types and formats to the columns
  
  

  =>

• Hide ‘technical’ columns (for example ID fields to create the relations)
  Only hide them in the resulting Pivot Table in Excel, not in the PowerPivot window.
  
  

  =>

• Review the relations between the tables to see if automatic matching was correct
  
  

  =>

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.

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.

Importing data from Exact Globe into PowerPivot

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:

• general ledger is made up of a header (grtbk) and details (gbkmut)
  the general ledger accounts are categorized by another table (AccountClasses)
• debtors and creditors are found in one company table (cicmpy)
• cost centers are stored in one table (kstpl)

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.

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:

Crescent allows you, as an end-user, to take your (PowerPivot model and) data and:

• visualize your data in a very flexible, on-the-fly way for best interpretation
• create a story board of multiple reports/dashboards
• embed the story board in PowerPivot with live data and filter capabillities

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

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:

• FactSales::SalesAmount to Values
• DimStore::StoreName to Row Labels

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

To use the Azure Data Market data in Excel an/or PowerPivot, Microsoft has released two add-ins:

• Windows Azure Marketplace DataMarket Add-In for Excel (CTP1)
  imports market data into Excel
  
• Updated PowerPivot for Excel 2010
  imports market data into PowerPivot
  

I will make a blogpost soon to show some direct use.

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: