Skip to Content

Joining two fact tables

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)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Apr 16 at 01:59 PM

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

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 16 at 02:01 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 17 at 09:57 AM

    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>
    Add comment
    10|10000 characters needed characters exceeded

  • Apr 16 at 02:46 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded