Domain with two fact tables - How to define which join path shall be used?

[arne.arnold]

Hi,

I'm using JasperSoft Server 5.6 and I want to design a domain with two fact tables. Let's assume, I also have two dimensions. Both dimensions are not interconnected, but both dimensions are referenced in both fact tables (see the attached depiction). Furthermore, the two fact tables aren't interconnected. When I want to query information from both dimensions without any information from any fact table, I have two possible join paths. I can either join dimension 1 and 2 via fact table 1 or fact table 2. As far as I've tested JasperSoft Server, JasperSoft decides on its own, which join path it takes. Therefore, I was not be able to decide on my own which join path JasperSoft shall use and to do that dynamically - like everytime I'm opening a report based on this domain. Is there any possibilty to let the user define the join path / which fact table shall be used ? E.g. in SAP BO Universe Designer you can select a context in this case.

Best regards,

Arne

[ernestoo]

Hi Arne,

As long as you are not looking to join fact table 1 + 2 together then we're in good shape. What you need to do is create 2 "data islands" for the join paths. If you notice our example Supermart domain only allows one island at a time (Like Sales) and the others get greyed out. In the domain designer you will use the table copy feature (which creates an Island)...explained in this video 

[Filip C.]

Ernesto,

 

what in case we need via a domain create a JasperSoft Studio report for both fact tables. Say fact_table_1 contains budget data (granularity: month and product), and fact_table_2 contains actuals (granularity: date and product [and customer]). How should we based on these two fact tables with different granularities create a report, which compares the budget on month level with the actuals for that month. In SQL I would create 1 aggregated query for the budget and 1 aggregated query for the actuals and join those. But not sure if I can do the same in JasperSoft STudio (or in the ad-hoc viewer).

 

How should we solve this ?

 

Regard,

 

Filip

[ernestoo]

Hi Arne, unfortunately that is not possible to do with domain queries, Studio does not allow you to "glue" two results together into one - you will need to use SQL in a derived table (there's a tab in the domain designer for this)