Data model in Power BI and Dimension Set ID

(0) Share

Report

Posted on by gerrysulp

Hi,

I've been struggling with a topic any Dynamics Business Central + Power BI experts would probably laught about. Have googeled for different solutions but I'm not too experienced in Power BI to follow the description.

Can somebody link a simple tutorial or explain by steps how can I separate in Power BI data model Business Central datatable 'Dimension Set Entry' in a way I could use all dimensions separately as I can in Business Central? The need is to create financial data reports (actual costs vs. budget by dimensions) based on Business Central ledgers to be filtered by each dimension separately. I'm running on BC version 19, soon to be 20.

Really appreciate for help

All responses (8)

Answers (0)

gerrysulp 5 on at

Like (0)

Report

RE: Data model in Power BI and Dimension Set ID

Hi, Guy McKenzie

I followed the process but could you please explain the how can I achieve one-to-may relationship on Dimension_Set_ID between pivoted DimensionSetEntries and any other table with Dimension_Set_ID?

Pivoted table have many Dimension_Set_ID's..

Added my current data model to visualize:

Thanks
Guy McKenzie 1,330 on at

Like (0)

Report

RE: Data model in Power BI and Dimension Set ID

Some interesting stuff there.

The first solution is the same approach I have shown here but extended further to make a unique table for each dimension.

There can be some advantages to doing this as you will only have joins to a dimension where there is a value. If you leave the full table, you will get a join in all cases but a specific dimension may have no value. Does this matter? Sometimes, sometimes not. Key thing to consider is are you going to re-use this in lots of reporting and are you going to add more dimension to BC at a later date. If reusing their approach makes sense. If adding dimensions later, you need to manually add a new table into Power Bi.

The second approach seems to be using a function to split the dimensions into unique tables which seems neat. I will look at that later in more detail. However, he is limiting himself to only shortcut dimensions.

The key to both their approaches is that they have established a consistent methodology as a foundation for all their reporting. If you have a one off small report, you can be quicker keeping things more simple. If you are building a reporting suite and want a solid foundation, a structured approach is always beneficial.
gerrysulp 5 on at

Like (0)

Report

RE: Data model in Power BI and Dimension Set ID

I think I'd like to build universal reusable dimension split model I've seen somewhere suggested, so that I guess, does not merge tables but use relationships through 'dimension set' as link.

Found this:

www.linkedin.com/.../dealing-dimensions-hooking-power-bi-up-microsoft-marghi-cpa-cga

And this:

braincanvas.io/.../

What do you think of that solution?

I just don't understand the point from how to split out my Dimension Value table into individual queries per Dimension.
Guy McKenzie 1,330 on at

Like (0)

Report

RE: Data model in Power BI and Dimension Set ID

Now that you have the above table. You simply need to merge your GL query with this query using Dimension set ID as the table join.

That will give you one big table of GL entries with each dimension shown in its own column to filter by.

Alternatively, you don’t actually need to merge the tables, you can use this new table to filter gl data by just building the join.
gerrysulp 5 on at

Like (0)

Report

RE: Data model in Power BI and Dimension Set ID

Thank you Guy McKenzie ! Makes sense and did all that by your guidance.

My next and probably the last obstacle is how to create relationship between current 'dimension set id' and general ledger transactions with 'dimension set id'. This is many to many relationship at the moment.

I've found some hints that I need to split out Dimension Value table into individual queries per Dimension, but how's that done and does relationship work that way later on?
Guy McKenzie 1,330 on at

Like (1)

Report

RE: Data model in Power BI and Dimension Set ID

I have used the web services connector to query the Dimensionsetentries.

Remove the columns not needed to leave dim set id, the dimension identifier and the dimension value identifier. I have retained The description columns rather than the code columns as I am thinking end user. Under different circumstances, I may keep the codes rather than descriptions

Select the column that contains your dimension code or name (not the value). Then select the transform menu and Pivot column. Choose the options shown below.

The value column is the dimension value code or name.

You now have a table of dimension set id as row identifiers, dimension names as columns and dimension values under each column.

You can remove null values with replace.

Worth noting that this approach shows all dimensions as columns regardless of what data we join the table too. If you only want relevant dimesnions to show, you'd be best off filtering them earlier in your data extraction so that only required ones appear.
gerrysulp 5 on at

Like (0)

Report

RE: Data model in Power BI and Dimension Set ID

If you don't mind to post screenshot by steps I'm really thankful. I generally understand what your solution is but I'm not too experienced with Power BI to be sure to follow fully.

Thank you
Guy McKenzie 1,330 on at

Like (1)

Report

RE: Data model in Power BI and Dimension Set ID

Hi, I tend to answer on my phone so don’t have screenshots but this can be done.

You need to have a unique query connected to the Dimension Set table. This will list each dimension value within the table as a unique row. This means you have several rows per Dim set id.

What you want to do is convert this table to one where each dim set Id is a single row, each dimension name is a column and the column value is the dimension set value.

To do this, you need to use power pivot.

once you have this pivoted table in your query, you can merge with other tables as required using the sim set id as the table join. You can then filter by dim values.

If you are unable to work through power pivot, I’ll add screen shots later when at my PC.