Section Access

[marishnagendran]

Dear All,

I'm Having Branch Master table in that I had created one section access based on Branch Region.

Now I want to give a section access to Product Master table based on some products heads.

Is it possible to write 2 section access in one Model???

Else If you have a scenario like this kindly share your section access code or any document

[marishnagendran]

Any Ideas?

[Vegar · Accepted solution]

You can use section access on multiple dimensions in a datamodel. Like this.

Section Access;
SA:
LOAD * inline [
ACCESS, USERID,BRANCH, PRODUCT
ADMIN, DOMAIN\Vegar, WEST, DOLL
];

Section Application;
Transactions: 
LOAD * inline [
BranchID , ProductId, Date, Value
E, C, 2019-08-12, 100
W, C, 2019-08-12, 200
E, D, 2019-07-01, 200
W, D, 2019-07-02, 222
];
BRANCH:
LOAD * inline [
BranchID, BRANCH
E, EAST
W, WEST
];

PRODUCT:
LOAD * inline [
ProductId, PRODUCT 
C, CAKE
D, DOLL
];

[marishnagendran]

Thanks vegar..

In My Sales Application 2 Modules are concatenate with Fact table

• Sales Data
• Business Plan

I have one Item Master table the columns are

• Item_code
• Item_Name
• Item_Group
• etc

For Sales Module Item_code is unique so i have connect fact table Item_code with Item master Item_code

For Business Plan Module i don't have Item_code level it has Item_Group level Data.

Give some solution for this

[Vegar]

Section Access will reduce your data based on the values you set in the Access section. To avoid your business plan data to falsely get reduced I see two possible solutions.

1. Add a item id field value to the business plan transactions, i.e. 'N/A'. Include the 'N/A' value in you section access for all users.

2. Remodel your data. Keep the business plan as an separate table connected to the main transaction table with a composite key.

Good luck!
-Vegar

[marishnagendran]

Following your first step means

• For  Business plan Itemgroup is seen by all the users??

Following Second step means

• It Will create circular reference looping because my Data Model seen below

Item Master					Customer_Master
			Fact_Table
Item_Code					Customer_Num
Item_Group			Item_Code		Customer_Name
Item Description			Branch_Code
			Customer_Num
			Company_Code
			Qty
Branch_Master			Sales_Value		Company_Master
			Net_Profit
Branch_Code					Company_Code
Branch_name					Company_Name
Area
Pincode

For Business Plan and Sales Module all Master table are same except Item_Master so if i separate business plan as separate table then it forms Circular reference

How means,

Item_Master -> Fact_table -> Customer_Master -> Business_Plan -> Item_Master forms circular reference

[Vegar]

My thougth of separating the plan from the fact would look similar to the picture below.  Where the field %PlanId is a composit key created by multiple fields.

autonumberhash256(Item_Code, Branch_Code ,Customer_Num, Company_Code) as %PlanID,

You might even want to include period into that key if you have a plan value per period.  

[marishnagendran]

In Business Plan table there is no Item_Code column

In Business Plan table following Fields are there,

• Item_Group
• Branch_Code
• Customer_Num
• Company_Code
• Potential
• Target

[Vegar]

I see, I forgot about the ItemCode. My thougth was that you should leave the item out of the key field. 

I notice you got item group in the plan and not in the sales. 

Maybe the best way to solve your issue is just to move the information about item group from the ItemCode table into the fact table. You could just add it to the sales transactions using a simple applymap. 

ItemGroupMap:
MAPPING
LOAD
Item_Code
Item_Group
FROM ItemCode;

Fact:
LOAD 
Item_Code, 
applymap('ItemGroupMap', Item_Code) as Item_Group
Branch_Code ,
Customer_Num, 
Company_Code,
Qty,
Sales_Value,
Net_Profit
FROM Sales;

[marishnagendran]

This solution also i'm given to my Head but they told in Item master Table,Item_code is Unique because for one Item group multiple Item_Code in Master table.

If i connect item group column with Fact table and item Master table is there any performance degradation???