The long forgotten tool.
@rturrentine
This query would be perfect to run into a executive query cube.
the Cube can be setup to add records each time it runs.
The Company would be DIM1 and Date DIM2
The four columns would be integer 1, 2, 3, and 4
1 Like
I think I see how this can work but I’m not getting it. I built my Cube and mapped it as you said. I’m stuck after that. How do I tell it to populate my UD01 table? I know I have to add this as a task and then to a process set but I’m stuck fleshing out the cube and the next query.
Here’s my cube:
I reverse engineered one of the Shop Vision dashboards.
This helped explain how you can have multiple roll up’s of the data.
after you create the cube
Look at the Dimensions
SysCubeDef and SysCubeParam
Create a seperate BAQ for the Dimension Details
SysCubeDim
then use a BAQ to review the Cube Data
SysCube
Using Publish and Subscribe in the dashboard - you select a parameter and filter the data results.
I’m struggling to follow you on all of this, I apologize. I have my BAQ and Cube complete but I don’t know where the dimensions are found. We’re working in the modern view and not the kinetic view. Not sure if that makes a difference.
In your scenario if you are just doing a single dimension which the fabricated date (Dim2) you don’t need the other baqs for your analysis.
Just bring in SysCube filters for CubeID = “Daily_Inv_KPI”
This will give you the daily value of the various
Create a process Set “DailyInvKPI”
When you are in the Executive Cube, click on Save Process Set
It will save the cube to a process set to be run
Schedule the process Set against a systemAgent schedule for a daily run.
The results will be stored in SysCube - Not UD01.
You can have multiple results in SysCube - they are all segregated thru the CubeID
1 Like
I’m seeing it now. I ran the process and created a BAQ to look at the Ice.SysCube table. I didn’t realize that table existed. When filtered for the CubeID I see my KPI data.
Now my question does this keep the historical data? If this process runs each day does it save each day’s data so I can create charts and reports of historical inventory levels?
Since I do need those, if this doesn’t do that how could I get it to save that data into, say, UD01 for further use?
Thanks
The data can be added if you delete nothing.
Each time the Executive Query is run, it adds a row of data in your case for each day.
I have also added the data to the SysCube data from a UD table for historical balances for example.
Just create another Cube that has the same Cube ID change the baq to one that is pointing to using the UD Table. Run this once to pull in the historical data.
1 Like
So far that works like I need it to. Is there a way to take the cube data, e.i. the date and inventory totals, and have it populate UD01 automatically each day the process runs? Each day the process runs, the cube has the data and it creates a new row in UD01 and populates it. Can this be done here or would it involve a BPM of sorts?
Why move it to UD01?
You can use SysCube like UD01 just filter it by the CubeID
1 Like
That’s correct. I’m not sure why I keep think of it as a temporary table. I have this set up and working as I want it in my Test environment. Going to move it to Production next.
Are there any performance issues with using the SysCube table to hold data for multiple queries? If I develop other executive queries for different metrics.
Executive Queries have been around a long time.
I used them in Vantage.
What is nice about the Executive query is you can have a BAQ that takes a long time to run and park the results in the Executive Cube nightly.
Then change your dashboard to utilize the Cube for the results for a faster response time.
Epicor comes with five different Cube
Bookings
Backlog
Cash Flow
Capacity utilization
On Time Supplier delivery
2 Likes
So far this is doing exactly what I want. Thank you very much. But I need to learn more about these. It seems you could build a near infinite number of executive queries. After building the first one for inventory costs I built a second for inventory SKU counts. I have set both of these to delete nothing so I can have historical data for chart building. How many can I make before I start having performance issues?
You say there are 5 different Cubes but when I query the cube for my dashboard I’m just looking at the Ice.SysCube table. Are there more? Should I use them?
While this seems like a great tool for extracting historical data one of the reasons I ask about the UD tables is, after 10 years of putting data in the SysCube table (for example) Can it crash and lose data or just run really slow because there might be 10 or 15 executive queries running on it?
What do I need to know to protect my data down the road?
The cubes are setup already attached to process sets.
After you have scheduled the process sets to run, their dashboards will have data.
As far as performance hits with too many cubes. The syscube table is just a list of data. I worked with one company that had over 400,000 parts. Accessing that part table was not an issue for response.
Great, thanks! I think I’ve been setting it up wrong since my dashboard isn’t updating. I added the cube direct to the system agent task instead of saving it to a process set and putting that on the system agent to run.
The system monitor shows the cube process ran last night but the dashboard didn’t update with new data.
Note that the executive query sums the fields selected.
So you just need to put in OpenQty as a value not an aggregate
See the document from 2017 on how to do a cube.
Using Sales Order Backlog - note that Group By is not being used.
The GroupBy is done in the executive cube.
Creating Executive Dashboards.docx (539.4 KB)
1 Like
Thanks! I have it all working now and I was able to bring in historical data to back fill the SysCube table. Now I’m just formatting the charts and will publish it.
Thanks for all your help on this.