Skip to Content
0

Joining two fact tables

3 days ago

23

avatar image

design.jpgHello,

I have problem with designing data model in SAP Information Design Tool.

The assumption is creation report with two measures: first from fact table a, second from fact table b. These facts should be link to one dimension table.

I joined my tables like in attached screen, but when I try biuld report in query panel, it doesn't work. BO created multiple paths with two queries :/Can somebody help design correctly model?Best regards!

design.jpg (59.4 kB)
10 |10000 characters needed characters left characters exceeded

Thanks! I will remember, it was my first post.

0
* Please Login or Register to Answer, Follow or Comment.

4 Answers

Brian Kudera 3 days ago
0

At the business layer settings in IDT, do you have enabled "Multiple SQL statements for each measure"?

Share
10 |10000 characters needed characters left characters exceeded
AMIT KUMAR
3 days ago
0

As you have two fact tables then BO report will generate two different SQL for each fact table.

enable the option "Multiple SQL for each measure" option in Business layer->Query options.

Share
10 |10000 characters needed characters left characters exceeded
Krystiano Jones 2 days ago
0

Unfortunately, when I unchecked the option "Multiple SQL for each measure", BO returned incorrect data grid.

I need to generate query like below (based on subquerys).

How should I change my data model to force BO for generate properly query?

Please not suggest the attached screen in my first tag - it was only example columns.

SELECT  
fx103,
fx104,
fx105, 
SUM(fx117)
FROM 
( 
	SELECT 
		COUNT((tabA.PL_ID)) AS fx117, 
		tabA.DM_PROD_ID AS fx118, 
		tabA.DMUB_CHP_ID AS fx119, 
		tabA.PL_ID AS fx124, 
		tabA.DMUB_ZD_ID AS fx125 
	FROM factTableA tabA  
	GROUP BY 
		tabA.DMUB_ZD_ID, 
		tabA.PL_ID, 
		tabA.DMUB_CHP_ID, 
		tabA.DM_PROD_ID
) ,
( 
	  SELECT 
		  SUM(tabB.SUMA_ZD_PODST) AS fx105 , 
		  tabB.DM_PROD_ID AS fx113, 
		  tabB.DMUB_CHP_ID AS fx114, 
		  tabB.PL_ID AS fx116 
	  FROM factTableB tabB  
	  GROUP BY 
		  tabB.PL_ID, 
		  tabB.DMUB_CHP_ID, 
		  tabB.DM_PROD_ID, 
) ,
(
	SELECT  
		dT.SERIA_POLISY AS fx103, 
		dT.NUMER_POLISY AS fx104, 
		dT.PL_ID AS fx115  
	FROM dimTable dT  
) ,
WHERE 
       fx115 = fx124(+)
   AND fx115 = fx116(+)
GROUP BY 
fx104,
fx103
<br>
Share
10 |10000 characters needed characters left characters exceeded
Joe Peters 3 days ago
0

Assuming that PL_ID is not unique in the fact tables, the splitting of queries is the correct approach. BO will re-join the queries in the report using the common dimension value.

Share
10 |10000 characters needed characters left characters exceeded