Create Data Model for a finance position

[Tarek78]

Hi Everyone,

I am seriously considering spending a lot of time building a data model in order to benefit from Power Pivot and DAX.

I work as a finance business partner and my role is to analyse data (monthly most of the time) through multiple scenarios (P&L by country, by account, by project, by channel, Actual figures vs Last Year vs budget).

I am struggling to build a data model that will help me automate and fullfill my role. I have various files :

- one extract from an ERP (such as Hyperion Smartview) that gives me the actuals for the current and previous with all the fields required (account, project name in each account, the department it belongs to etc.)
- one extract from the same ERP that will give me the actual cost/revenue per GL account
- one Excel file we I have all the budget


Can someone help me building a nice data model. What steps do I need to think of to build my model? How do I define the dimensions I need to use a table? People say that the STAR schema is the best with one fact table at the center. But what if I have multiple fact tables in the center (one for actuals, one for previous years, one for budget) would that still work?

Thanks for your help as I am a bit stuck

 

[Matt Allington]

Here is some reading for you, articles I written over the years.

Build a P&L With Power BI

A few weeks ago, Danielle Stein Fairhurst from Plum Solutions contacted me and asked if I would speak at her financial modelling meetup group (a topic of my choice related to Power BI). I decided to speak about creating a P&L using Power BI and DAX. I first learnt how [...]Read More »

exceleratorbi.com.au

Build a Power BI P&L in 5 Mins with InfoRiver

I mentioned in an earlier blog that I have been doing some work with the owner of the InfoRiver Power BI Custom visual. I have been testing, given UI feedback, and more recently I have produced some instructional videos. I wanted to share the first of these videos with you [...]Read More »

exceleratorbi.com.au

The Optimal Shape for Power BI Data

It is common for self taught Power Pivot users to get the shape of their data all wrong. This article describes the optimal way to do it.

exceleratorbi.com.au

Multiple Data Tables in Power Pivot

Learn how to combine multiple data tables in Power BI and/or Excel Power Pivot data model.

exceleratorbi.com.au

 

[Tarek78]

WoW impressive. I will read all your articles with attention and revert back to you. Which one I should read first you reckon?