Using Date Cube as a Time Entity
Hello I've some peculiar report requirement that has to be done in BOARD.
I have 2 cubes, let's say cube A holds an transaction amount value, and cube B holds a transaction payment due date value. So the data type for cube A is double, and cube B is date. The cube B data is from a calculation that has been done inside BOARD. Cube A and B shares the same structure.
What I want to achieve is, I want to use the Cube B (which is a calculation result and a Date Cube), as a time entity. So the user can do a filtering using a pager / selector based on the due date that stored in Cube B and showing the transaction amount (which is stored in the cube A).
Current progress is I already tried to extract both Cubes (TXT / CSV files) and reload it again to a different cube. But when I tried to load the due date as a time entity, I can't get the transaction amount. But in another side, if I tried to load the transaction amount, I can't get the due date as a time entity. Because those are two separate files and ASCII files data reader cannot do a join like SQL.
Is there any workaround or solution regarding this case? Any help would be appreciated. Thank you.
Answers
-
Hi,
a possible solution might be to use rolap Cubes.
In your current scenario you have 2 cubes
A (structured on some entities) - Double --> Amount
B (strucured on same entities) - Data --> Payment Date (for certain entity combination)
now you need a third cube
C (structured on same entities + Time) -> Double --> Amount at a certain date
So if you Cube A and B are rolap Cubes, you can easily define C on tables of A and B using normal SQL join syntax
Regards
Björn
3 -
Hi,
Thanks for your comments. This is interesting as I never used the rolap cubes in BOARD. But, to fill in the rolap cubes with data, it must be done via SQL / ODBC connection? Because I used the normal data reader but the rolap cube is still empty.
And for your information, my data source is using SAP with BOARD Connector.
0 -
Hi William Limy,
Another option, more similar to what you're currently trying, is rather than extracting each cube separately you can extract a layout. This will allow you to generate an ascii file containing the data from both your cubes, and so allow you to load into the single target cube as you need.
This option is down the bottom of the Extract tab, and you just need to set up your layout the same way you would set it up in a Dataview
Hope this helps
2 -
Thanks for the comments. But I'm getting an error when I tried to run the procedure. "Procedure xxx failed". Is it because of my cubes are in day dimension?
0 -
Updates:
It seems like there's too many data so the procedure fails. And I found something interesting when I tried to run the procedure from a screen that has an active selection and the extraction adheres to that active selection. And the procedure runs correctly without error.
1 -
Hi William, I'm not sure the size of your cube or dimensions/entities, but if it's working with a selection applied it then yeah you might be right, as that could suggest the issue is it's trying to extract 'too much' data.
If I had that behaviour I'd look at ways to limit the amount of data needing to be extracted.
A couple of suggestions;
- Do you need to extract the entire cube(s) each time, or can you use a selector as you have done?
- You can also use a filter within the dataview layout to limit the data displayed/extracted. This could be done with an algorithm comparing your 'date' value in the cube to the current date [date algorithm => today() ]
- And the simplest is to make sure you have 'Show All' UNCHECKED under 'by rows' in the Axes tab of the dataview layout, so that it's not extracting rows of blank/empty data.
Hope that helps.
0 -
Okay, thanks to Brendan Broughton, this question has been answered correctly with extracting a layout from a procedure. Thank you for your help.
Have a good day.
1 -
Hi William,
you can also fill a Rolap Cube using standard board techniques like dataflow or dataentry
We have a very helpful description in our manual: Rolap Cubes
regards
Björn
0
