Integrated BI and Modano Model

8 posts / 0 new
Last post
Purwadi Nitimidjojo 1 X 2
Integrated BI and Modano Model

I am thinking of experimenting on building a model that leverages the use of Power Pivot, Power View and Modano by :

- loading detailed transation data from a database into Power Pivot

- building a data model and adding metrics using DAX formulas

- visualizing the data and metrics using Power View to generate business insights (that will be valuable when preparing projection assumptions)

- building a strategic planning model on the same Excel file (I may have to build the model first using Modano and then add the BI part on free form sheets)

I am wondering if anyone of you have tried this.  If you have, please share your experience.  What kind of challenges did you have to deal with?


Michael Hutchens A+ 189

Hi Purwadi,

I have not personally done anything like this, but I am excited to see how it would/could work.

From a technical perspective, your suggested approach of incorporating freeform sheets, and thereby creating hybrid modular workbook with freeform components, is definitely the right approach.

Keep us posted and let us know if you hit any road blocks along the way.


Jun Yan A+ 124

Hi Purwadi,

Interesting concept, we did something not quite as advanced and wholly within a Modular Workbook for a client.

The steps the client would take would be:

- We created a separate pivot workbook that unwinds transaction information into useable higher level categories. This workbook is wholly reuseable as the data set remains largely unchanged and is reused period on period with another data dump. If the data set is changing, but the fields required remain unchanged then I'm sure this can be macro easily enough.

- Then we have the modular budgeting & planning model, where we import the pivoted data into custom historical income statement modules using mapped files (need to be careful with the module building here). Within the modules we unwind the higher level categories that we can push into forecast modules, and then bring everything back up to revenue.

It's a little convoluted, but the benefit is the client can track the driver level data alongside their revenue level figures. This allows more flexibility when playing around with forecasts.

Obviously we don't provide Business Insights through Power View (or similar), but this is something the client can do with the pivoted data anyway, which can be fed through the model via import.

I would point out that we try and avoid using free form as much as we can, and just import where we need, but that's a preference to try file size manage (mapped imports are simple enough anyway).

Hope that helps.


Purwadi Nitimidjojo 1 X 2

Hi Jun Yan,

Thank you for sharing your experience.  I really appreciate it.

I tried Power Pivot, Power View  and Power BI Desktop with Modano.  I like Power BI Desktop much better as I can generate more insights from its better visuals.  I can use the insights for making more realistic projection assumptions.


Sajid Mafahir X 1

Hello Jun and Purwadi,

Thankful for your ideas. 

However, are there any recent updates in this subject? I'm trying to model using Modano but present it in Power BI instead of excel/pdf sharing. 

I would appreciate your thoughts on this. 



Jun Yan A+ 124

Hi Sajid,

This one is an interesting one.

This is absolutely something you can do, we do have instances where we've built Power Queries into a Modano file. We've done this where we need multi-dimensional P&L modelling (Business Units) where we don't necessarily want to have general ledgers repeat.

Power Query (Power BI) can readily read Modano files. If you can work your way around importing from an Excel file, as well as managing transformations, there's nothing stopping you from presenting in Power BI. We don't have an example of this to show you as we've somewhat steered away a little from Power BI, given we're able to build Power Queries into the Modano files.

Anyways, hopefully that gives you some food for thought.


Tarjei Kirkesaether A+ 58

Hi Jun,

May I ask how you're using the Power Query data in Modano? I've used it quite a alot recently but only found it useful with freeform formulas. It's working fine with a little bit of help along the way, even in mirrored multi entity models, but I'm curious if you have found a method that does not rely on freeform formulas?



Jun Yan A+ 124

Hey Tarjei,

Same method as you, we're building the Power Query tables into a freeform sheet and reading that from the Modano content. The only way to do that is using freeform formulas. It's not ideal, but the tables are quite structured, so it's actually not a big issues and scales well.

Same thing, we use the same methodology to manage multi-entity, multi-BU models, and it scales with the entities / BUs without any issues.