Latest Enterprise DNA Initiatives

Dynamic summary table

Hi Team,

I have scenario…

Orders are raised in the 1st ERP system that is EA, then pulled to second ERP system X3 after that sales invoices are raised in x3.

We want to match the discounts and charges which are checked at EA sales order time with invoice discounts, charges etc. At EA Sales Order we have boolean flag for charges, remakes and showroom.

We have to reversal check. I know the correct discount and charge from invoice. But when I am calculating the discount then its not calculating correctly because of Joins.

Relation between EA salesorder and X3 orders is with OrderID, and x3 order and x3 sales with Document number and last invoice number. But not a active relationship. I have used USERELATIONSHIP then also result is not correct.

I tried making a summary table but when I join them I get error of circular dependency.

Can you please suggest , how can I get the correct result of measures Discounts, Remake and showroom. I have attached the test PBIX with data .


Correct amount of discount for 2021 is -6163, for customer 148099
Mapping is

Test_Sales.pbix (2.0 MB)

Hi @Seema,

Thanks for posting your question. You have so many many-many relationships which are not ideal. This model makes calculations very tricky to understand. Is there a way you can simplify relationships? For example, why can’t you have unique CustomerID is x3 customer. Reason is that all your relationships are many-many.

Kind Regards,
Hafiz

2 Likes

Hi @hafizsultan,
Thanks for pointing it out. I have corrected the relationship between master and transactionsTest_Sales.pbix (1.9 MB) tables. Uploading the latest pbix file with updated table relationship.

Thanks,
Seema

Hi @Seema,

Thanks for correcting your model and now it seems a better model. However, due to changes in column structure/data types, your measures are not working when I open your file. Can you please fix these minor issues?

Hi @hafizsultan,
Sorry, missed out the check.
Please find the updated Pbix file. Test_Sales.pbix (1.9 MB)

Thanks,

Seema

Hi @Seema,

Thanks for sending me the file. Unfortunately, relationships in your model are not working as intended. You need to build with slight variation. In current setup, filter flows from EA SalesOrder to X3 Orders but it does not flow from X3 Orders to Sales table. How about you elminate EA SalesOrder by bringing its columns to X3 Orders etc? I believe many-to-many will work fine if it is an active relationship in this case.

1 Like

i thought many to many relationship isn’t a good idea

Hi @Keith,

Yes, 100% agreed and many-to-many should be avoided if possible. Ideal model is star-schema but in this case, I am not sure how Seema can avoid many-to-many(may be by bridge table.)

Hi @hafizsultan, Thanks for the suggestion let me try and let you know.

Hi @Seema,

No worries. If for some reason you are unable to create data model, please send through your sample excel files and I can try to create simple data model.

Kind Regards,
Hafiz

I’ve been following this thread out of interest, and hope you don’t mind if I chime in. I completely agree with @hafizsultan’s recommendation to merge the EA Sales Order and X3 tables.

In addition, I think you can similarly simplify the X3 Customers and the EA Customers Table down into one.

Once you’ve done that, there are three possible options I see for resolving the many-to-many relationship:

  1. use a bridge table as previously suggested
  2. use a virtual relationship via TREATAS
  3. denormalize the sales and order tables down into one combined fact table

I think once the model is simplified, evaluating these three options to determine the best one will be easier.

I hope this is helpful.

– Brian

1 Like

Thanks @BrianJ for your valuable input. Really appreciated !!

I hope @Seema is able to simplify model to get required analytics work :slight_smile:

Kind Regards,
Hafiz

1 Like

Hi @Seema, did the response provided by the users and experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

Hi @hafizsultan and @BrianJ,

Thanks for your valuable inputs.
As suggested I got the Charge, remake and showroom in X3 salesorder with lookup. Its just I am not able o make active relationship between x3 salesorder and x3 sales. I tried with a bridge table also. Its the calculation which is not correct. I will email the updated pbix file shortly.
Another problem which I can think is date filters. I am comparing sales(invoice) discounts with order discounts. Sales is done March and order is done in Dec’2020 in one of the test case which I am checking.

Hi @Seema, we’ve noticed that no response has been received from you since the 15th of April. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

Hi @Seema, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!