I have just set up Apache Kylin 5.0 and have been playing around with the sample Data provided in the docker (DB: SSB). I have understood most of the concepts of Cubes, Dimensions, Indexes, Measures etc. but am stuck at a peculiar test report I need to create.
Scenario: LO_ORDERDATE (Red outline in the image) is the date of the order LO_COMMITDATE (Green Outline) is the date of Shipment. (Am assuming this, of course) LO_QUANTITY (Yellow Outline) is the quantity of product ordered
Report Required Daywise data of the sum of product ID x. i.e. if product x is ordered in 50 quantity on jan 01 and commitdate is jan 31. And the same product is ordered in 100 quantity on jan 10 and the commitdate is jan 25. The data should show as follows
1-Jan 50
2-Jan 50
3-Jan 50
4-Jan 50
5-Jan 50
6-Jan 50
7-Jan 50
8-Jan 50
9-Jan 50
10-Jan 150
11-Jan 150
12-Jan 150
13-Jan 150
...
24-Jan 150
25-Jan 150
26-Jan 50
27-Jan 50
28-Jan 50
29-Jan 50
30-Jan 50
31-Jan 50
Is this possible using cubes?
I have tried using Calculated Column but I don't think they are meant for this type of scenario.
The same scenario can be imagined if I were to check daywise number of occupancy in a hotel if we have only the check-in date and check-out date in the reservation fact table.