Integrated BI and Modano Model

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.

M.

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.

Jun

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. 

Thanks

Sajid

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.

Jun

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?

Cheers

T

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.

Jun