MS Business Cetnral Data Model

[DataHero]

Hi,

Im new to MSBC and wanted to ask for experiences with MSBC data in Power BI.

I feel, its very difficult to build a proper data model based on MSBC data.

Does somebody know what the central table is to build a sales report in PBI Desktop?
I cant find a table, containing enough columns to join Sales, SalesInvoiceLines and CreditMemos.

I thought it would be ItemLedgerEntries or ValueEntries, but I cant build a uniquie relationship to the other tables.

Thanks for any help!
Kind regards,
DataHero

[Sahir_Maharaj · Accepted solution]

Hello @DataHero,

In terms of building a sales report in Power BI, the central table will depend on your specific reporting requirements. Generally, the Sales Header and Sales Line tables are the primary tables for building sales reports in MSBC.

To join Sales, Sales Invoice Lines, and Credit Memos, you can use the Sales Header and Sales Line tables. The Sales Header table contains information about the sales order, including the sales order number, customer, salesperson, and other relevant data. The Sales Line table contains information about the individual items sold, including the item number, quantity, price, and other details.

To link the Sales Header and Sales Line tables to the other tables in your data model, you will need to create relationships between them based on common fields such as Sales Order Number or Item Number. You may also need to include other tables such as Customers, Items, and Vendors, depending on the specific reporting requirements. As for the Item Ledger Entries and Value Entries tables, they contain information about the inventory movements and financial transactions, respectively. While they can be useful for certain reporting scenarios, they may not be necessary for building a sales report.

Hope this helps.

[Idrissshatila]

Hello @DataHero ,

I work in analyzing Bc data, so if you want to analyse sales data then take the value entries table, there's plenty of columns that would help you get the detailed result you want.

so the document type will help you get the sales invoice related data and the credit memo data, you could filter with this column to get each transactions.

If I answered your question, please mark my post as solution so it would appeare to others, Appreciate your Kudos 👍

Follow me on Linkedin

[DataHero]

Hi @Idrissshatila 

thank you for your answer. I understand what you mean.
Despite I can build the described relationsship between ValueEntries and credit memo,
this will be a many to many relation. This is not useful of course.

Further more, I have the same problem to build unique relations for SalesInvoice and SalesLines tables.

Kind regards,
DataHero

[Idrissshatila]

Hello @DataHero ,

You don't need to get the credit memo table because value entries table has everything including credit memos, so you get get credit memo values within the value entries table just by filtering document type to credit memo.

If I answered your question, please mark my post as solution so it would appeare to others, Appreciate your Kudos 👍

Follow me on Linkedin