has anyone created any executive dashboards in PowerBI for Epicor? i am about to embark on this…what i know may be a headache of a journey.
wondering what others have done to get some ideas of layouts etc.
1 Like
Before you get too far into this I recommend you look at / reach out to these guys
Their product is awesome and it made our life super easy when we first got started with Power BI and Epicor.
That failing we do it quite a bit, it is a headache you should use a replication Database for your Reporting / Power BI do not try to use REST it is slow and painful.
4 Likes
Hi Jose, thanks for the recommendation!, we have done some work with Neil @ BlueSky previously and we are already using REST to have data displayed on TV screens around the shop floor via PowerBI.
for the executive side of things i was looking at the moment of some ideas etc, i know Neil and his team have some excellent solutions out there, but i like to tinker and learn haha.
plus i have spent the last year and a bit doing a data analytics with PowerBI course, so i hope i have learned something from this!
1 Like
Amazing!
We’ve done quite a bit with it but using SQL Azure Replication for performance. REST is too slow for heavy data load for us.
3 Likes
I have a small herd of PBI dashboards. I deploy them to 365 services and users view them from Teams. They’re all through REST, and it’s true that refresh isn’t snappy. Mine all get their data entirely from BAQ’s. This lets me constrain my data to only what I need, and dial in data retrieval performance. Both vastly improve PBI refresh times.
It’s a lot easier to work through broken down into components that can be dealt with incrementally:
- The data connections use M language ( Power Query M formula language reference - PowerQuery M | Microsoft Learn ), identical to Excel Power Query. This makes Excel a convenient tool to dial in your data, sort out tabular transforms, and performance tune transforms. The ‘advanced editor’ text can be copy-pasted from one to the other.
- DAX is the function language used at the data layer that presentation is reading from. It’s not Excel formulas. It’s set based and contextual, and the context is mutable in a variety of ways. This is where most of the brain bending will happen. I’ve been working in PBI since before general release, and I’ve never found a situation where fancy DAX was the best solution.
- Data relations are extremely powerful if you’re comfortable with implementing normalization. Unforgiving if not. Nobody seems to know what normalization is any more, so most PBI I see in the wild is a heap of unrelated tables and lookups to duplicate data, which can be fine. Or a collection of frustrated workarounds to deal with auto-detect relations. Auto-detect relations is like SQL Server recommended indexes - If they’re right, it’s not on purpose.
- Presentation is straightforward, for BI definitions of straightforward. Things are discoverable with some clicking, usually just as fast to figure out manually as it is to google how to do.
1 Like
We did this at my last company, it really helped our shop efficiency! But, we didn’t use REST though. It wasn’t replication exactly, though. We had a lot of the data already set up using Epicor dashboards, so we used Views based on those BAQs to create a Data Warehouse that we used as the datasource for the PowerBI dashboards.
We’ve been using PowerBI with Epicor for about 2 years. The first year was mostly experimentation with only basic reports for sales figures, yields, scrap reporting and some employee utilization. In the past year it has become immensely more useful by bringing in AP, purchase orders, shipment data and job tracking. Instead of stumbling through Epicor our users can generally find any information they need about supplies, WIP, and the major KPIs such as sales, scrap, yield etc. This is done entirely through BAQ/Rest. The WIP report pulls in the labordtl table (~300k records) & filtered parttran table (~100k records), takes about 10 minutes to refresh, and refreshes 8 times/day. The other reports are much faster.
Overall, I highly recommend Power BI. It’s gone from a tool used once per month for powerpoints to a tool used everyday to see the flow of business. I’ve never used another BI tool, but I’m so happy with Power BI that I don’t plan on looking. We have about 3 years of data at this point. I imagine we will need to look into data replication as Jose described in the next 1-2 years due to performance, but our current setup provides everything we need.
1 Like
@josecgomez Is this still your recommendation? Trying to start our BI journey here and looking for a nice springboard option.
@tonyb1986 How did you get up the shop displays? Was it a static report refreshed daily? Does it rotate through a few reports?
Hi Ken,
we use amazon firesticks with Yodeck and link this to PowerBI
we have 1 PowerBI premium licence and we can refresh this every 15 minutes to keep the displays updated.
every now and again there is a message pops up on the screens saying we need to re-login to re authenticate our link to microsoft but considering its a totally free solution (other than PowerBI) it works well and does exactly what we need it to do.
the TV screen on the shop floor cycles through 4 reports and the one in the sales office scrolls through 3 or 4 reports also.
2 Likes
Hi Ken
If you want something ready and out of the box absolutely the folks at Blue Sky are awesome!
2 Likes
In addition to Blue Sky, we’ve been looking at OptSigns which works with M365 out of the box too. Apparently, they are working on that pesky login issue.
2 Likes
So are you saying your not using HA and using SQL Azure,just for reporting or are you using it for the prod part of your environment…
I know a long time ago experimenting with SQL Azure and latency was bad…
What is HA?
High Availability or maybe known as Availability Groups.
Jose will chime in on this I’m sure, but there are three ways to run SQL Server in Azure:
- Azure SQL
- Azure SQL Managed Instance
- SQL Server running on a VM
I only have seen Epicor users doing the last one since you can’t control the SQL version on the first two.
Exactly That’s how I’ve ever done it on Azure
Correct we use VM SQL for Running Epicor PROD
But our replication Db is a Azure SQL instance cause we don’t care about the version at that point. Serverlessish
2 Likes
Apparently using HA with Read Only Replicas, by default Kinetic will offload the BAQs to the Read Only instance(s)… Sadly to get that to work you need SQL Enterprise $$$$$$$
That being said… You can control the compatibility level on a database running on Azure SQL so you could argue…that it might work…, but would it be certified… That’s the big question.
1 Like