Also, that screenshot / graphic seems off, you would want to assign number fields to number dimensions, strings to text, etc. I would think that cube in that screenshot wouldn’t work at all.
I’m so confused. Are we still discussing saving historical reports or did we switch topics?
Haha! Yes. We are still talking about the same thing. I was originally going to save my data in UD06, but some kind folks suggested I look at executive queries. I had no idea that they essentially ‘save’ data in the syscube table. Once I get that working I think it will do the trick.
Once I setup some Cubes, if I want to delete them becasue tehy were just samples, how does the data in the SysCube table get removed?
For example, I created a cubeID: NateTest. After running it, I have 6 new rows in SysCube with this CubeID. I deleted the NateTest Cube, as it was just a sample. But I still have values for NateTest in the SysCube table. Will these stay in there forever now that I have deleted the cube?
Sorry to go completely off track of the conversation so far, but that’s a really good question! And in the ballpark of work I’ve been doing in the last several months… If you have a start date and an end date to work with, you have a date range and you can reach back into history and filter without warehousing copies of data.
Start date is the tricky one. I see references to JobReleased in your OpenJobValue_Summary BAQ. I’ve never encountered a job released date (I’d love to be proved wrong). I have seen where others have added that with a customization, which is something I’m strongly considering. If you can reference a job’s scheduled start date that could also be an option. If your workflow is strictly on-demand make-to-order, order date might work.
End date’s easy. You’ve got operation complete dates, job complete date, ship dates, and job close date.
1 Like
@NateS
Just a confirmation that Executive Queries\Cubes can be ideal for this type of thing.
I have helped 3 clients setup both dashboard and SSRS Reports based off of data collected with Executive Queries. In some cases we just used one BAQ to populate the Cube and in others we used multiple BAQ’s to populate the Cube.
Also, though the number of data points you can store in a Cube is limited, you don’t have to store all of the data for your reporting in the Cube, just the Date, metrics and some sort of Key or two so you can create another BAQ to pull both the Cube data and it’s related data together.
It’s a tricky thing to work out and takes patience but I can be done and what’s nice is that it is intended to be scheduled.
Purging Cube data is something I wish they would improve, like set a backstop date or something so you could use it as a rolling record.
(On the functions note, while I it would be my second choice for something like this, you really should dig into functions, they can be very useful. I was surprised how often functions were the ideal solution to a problem… but they can be just as or more challenging then Executive Queries)
2 Likes
The only way I’ve found to do it is to set the BAQ to not return any rows and set the deletion to be by BAQ.
In a test DB, SQL was my friend…
1 Like
No there is no JobReleased date. I am using the JobReleased status flag, along with our internal Date08 field. We call this the Sales Ship By Date, and it represents the date that our Sales department would like to ship the products to meet a given sales target.
I like where you are going with this, but I keep coming up to a problem. My Open Job Value, and Targets are calculated based on the current status of the job’s operations. I could try to use the labor and transactions tables to figure out the dates that each operation was completed at, but this does sound a bit more complicated.
Having said all that, I think I got it working. The setup is two BAQs, feeding two Cubes. These run first at say 3am daily. Then at 4am another BAQ inside a final Cube runs to pull the data needed for the report from the existing data in SysCube using the CubeID as filters. In the end, I have to run one last BAQ which will feed a dashboard with charts, that pulls data from the SysCube using my third CubeID as the filter. The data looks more or less correct. However, I may have to wait another day to test the purge routine so that I can keep the previous day’s data in the third Cube.
Is there any good way to export Cubes? Am I right in calling Executive Queries and Cubes the same thing?