Solved

Power BI

  • 6 December 2021
  • 13 replies
  • 316 views
A
Rank
Userlevel 1
Badge

Hi everyone. Quick one please. Can Power BI reports be done across multiple tenants

 

icon

Best answer by ChandraM 6 December 2021, 20:07

View original
Andre Aysen

13 replies

A
Rank
Userlevel 1
Badge

That is to say one report looking at 2 or more tenants

Andre Aysen
C
Rank
Userlevel 7
Badge +9

Hi @andre89 Try configuring External Applications in both the tenants.

Thanks

Chandra M
C
Rank
Userlevel 7
Badge +9

HI @andre89 Another shortcut may be to have,

  1. Create a SQL View showing the details from all company IDs.
  2. Create a DAC for the SQL View
  3. Create a GI and mark for ODATA.

Thanks

Chandra M
A
Rank
Userlevel 1
Badge

HI @andre89 Another shortcut may be to have,

  1. Create a SQL View showing the details from all company IDs.
  2. Create a DAC for the SQL View
  3. Create a GI and mark for ODATA.

Thanks

Thanks BUT will the drill down and drill through still work?

Andre Aysen
C
Rank
Userlevel 7
Badge +9

Hi @andre89 This is just a thought. Please try.

In PowerBI, you can fetch the data from GI with the CompanyIDs, and then frame the drill down urls like http…….com/Companyid=’ABC’..Screenid=SO301000..OrderType=’SO’..OrderNbr-’SO001234’ to drilldown to the documents. hope this helps.

Thanks

Chandra M
A
Rank
Userlevel 1
Badge

Hi @andre89 This is just a thought. Please try.

In PowerBI, you can fetch the data from GI with the CompanyIDs, and then frame the drill down urls like http…….com/Companyid=’ABC’..Screenid=SO301000..OrderType=’SO’..OrderNbr-’SO001234’ to drilldown to the documents. hope this helps.

Thanks

Hi my Friend. I will talk to one of our Senior Guys to explain. Many thanks

Andre Aysen
C
Rank
Userlevel 7
Badge +9

Thanks for the update @andre89 

Chandra M
V
Rank
Userlevel 7
Badge +11

HI @andre89 Another shortcut may be to have,

  1. Create a SQL View showing the details from all company IDs.
  2. Create a DAC for the SQL View
  3. Create a GI and mark for ODATA.

Thanks

 

Hi @ChandrasekharM 

Were you able to pull data from more than one tenant by creating the GI as mentioned above?

SQL view to pull data from db table for all tenant, works ( its external to Acumatica )

Creating GI, within Acumatica, it wont allow you pull data from more than one tenant. 

Let me know if I am missing any detail here.

Regards,

VijayKumarU
C
Rank
Userlevel 7
Badge +9

Hi @vkumar  Yes, I have seen data from other companies to the reports that utilizes the SQL View, and where Companyid is not a key (Key=True should not be defined on the DAC).

Thanks

 

Chandra M
Naveen Boga
Rank
Userlevel 7
Badge +17

Hi @andre89  One of our client uses the Power BI extensively, when we discussed on this scenario. here is the suggestions. Hope this helps!

 

When you setup the odata feed: https://yourcompany.acumatica.com/odata/yourTenant

You would just point the first dataset to Tenant 1, the second dataset to Tenant 2,

And then, either in GI, or in Power BI, create a custom column "Tenant" and insert the Tenant name.

Then in Power Query, or with a dax UNION append the queries together.

Naveen B
A
Rank
Userlevel 1
Badge

Hi @andre89  One of our client uses the Power BI extensively, when we discussed on this scenario. here is the suggestions. Hope this helps!

 

When you setup the odata feed: https://yourcompany.acumatica.com/odata/yourTenant

You would just point the first dataset to Tenant 1, the second dataset to Tenant 2,

And then, either in GI, or in Power BI, create a custom column "Tenant" and insert the Tenant name.

Then in Power Query, or with a dax UNION append the queries together.

Hi Naveen, thanks for all help. I am new to Acumatica so get a bit lost here and there BUT this community and mates like u sure do help!! I have successfully managed all those 3 steps of creating a DAC from a SQL view and can see it in my GI’s together with the company ID coming from the view.

Now in PowerBI i need to create a report with all tenants do i still need to follow your advice cos i see all required fields within this GI. M problem is the ability to have drilldowns and drill through working all the time

Andre Aysen
A
Rank
Userlevel 1
Badge

Hi @andre89  One of our client uses the Power BI extensively, when we discussed on this scenario. here is the suggestions. Hope this helps!

 

When you setup the odata feed: https://yourcompany.acumatica.com/odata/yourTenant

You would just point the first dataset to Tenant 1, the second dataset to Tenant 2,

And then, either in GI, or in Power BI, create a custom column "Tenant" and insert the Tenant name.

Then in Power Query, or with a dax UNION append the queries together.

Hi Naveen - Me again:(

I am having so much resistance with Power BI and SQL views because of “security” issues I am going to try this recommendation.

I have sucessfully created many PowerBI across tenant reports but our Tech Director does not approve of it due to security.

Tell me please - Example my one SQL query looks at 10 tenants. Now with this recommendation in order to get all 10 tenant into the report i need to union the very same script 10 times with DAX union. Would if not be slow?

Regards

Andre Aysen
A
Rank
Userlevel 1
Badge

Hi no worries as I have done this today - All Good!!

Andre Aysen

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved