Combining fields to create new table to be used as dimension
I am struggling to do the below:-
Currently i have below data model used in app:-
My requirement is i want to show "Company_Code_Desc" coming from ORGA table at front end as dimension and i chart i have to use KPI_CODE, NB_KO, NBR_TOTAL coming from fact table in the expression.
Currently due to section access, the user can only see the data for those companies to which he has access. My Requirement is to create a new table comprising the fields "Company_Code_Desc" in a way that user should see the data for all the companies without any restriction of access.
I tired to create a new table in which i can take these fields and link it to the fact table vie "Periode" and removing the "Key_Control" key to remove the section access and renamed the fields like below:-
New_Fact: LOAD //_KEY_DIM as Key_Dim, _Key_Ratio as Key_Ratio, // Periode as NEw_Periode, _Key_Control as KEy_Control, _KEY_DETAIL as Key_Detail, KPI_Code as New_KPI_Code, KPI_Code &'-'& Periode &'-'& _KEY_DIM as %New_Key, Nbr_KO as Nbr_Ko, Nbr_TOTAL as Nbr_Total, Total_Amount_KO as New_Total_Amount_KO, Special_Amount1 as NEw_Special_Amount1, Special_Amount2 as New_Special_Amount2, Special_Amount3 as New_Special_Amount3, Total_Amount as New_Total_Amount FROM ] (qvd);
However, it does not work.
Can anyone please tell me how can do this so that user can see the data for all the company codes .