Let me preface by saying I have no formal Excel, database or data modeling education just tons of Udemy (online training) and YouTube.
I have been tasked with creating Financial and CRM Dashboards for our company. My current approach is to build BAQ’s and use OData Feeds to get them into an Excel Data Model. Then use Power Query/Power Pivot to create Pivot Tables, graphs, charts, etc. to populate the dashboards. We do not have Power BI.
My immediate challenge is how to create the Fact Table for the Model.
Should I:
A) Try to build an all-inclusive BAQ and then split out the Dimensional data once in Power Query. (This kind of BAQ is overwhelming to me.)
B) Import individual BAQs into Power Query and create a Fact table from the Dimension Tables? (I am struggling with how to do this.)
I know there is a ton of information not stated but I’m trying to be as succinct as possible.
@TWooten, first, best of luck in this endeavor. Dashboards can consume every minute of your day if you’re not careful
I think the safest, simplest, secure, and future-proof way would be to use the BAQ OData service, and create your BAQ’s representing facts and dimensions. You can pack a few of them full of calculations, transforms, and columns used for linking the ‘cube’. But keeping them separate at this level will allow for editing data point fields (not linking fields) without destroying the cube structure.
Do you have access to the Epicor Data Discovery tools in your version?
They are a step up for Epicor for KPI and data graphing based on Epicor BAQ’s. You don’t have to be in Epicor as the EDD website is available separately for me. We are on public cloud so near latest version.
Also Epicor provides a EDA module that is a data warehouse program from Phocus with lots of built in data pulls for all the Epicor sections.
If you are set on Excel already that is fine, but if you are starting from scratch it may make sense to look at the other tools available
I appreciate your suggestion. I did use EDD early on but was not impressed by the versatility of it. May be that I didn’t take a deep enough dive but I couldn’t get the dashboards to a point where the data could be sliced easily. My users do not want to learn anything new, they just want t button to push. They short out when they see a Pivot Table.
