In the last post I wrote about authorizations in SAP Data Warehouse Cloud and I had an open topic about authorization on hierarchy nodes in SAP DWC. So I looked into and here is one example of how it could work at the moment. I don't know if SAP changes something in future releases.
So let us started with a CSV file to create our authorization we can use in the SAP Data Warehouse Cloud. I would now authorize my user to a Product Category because my hierarchy looks like this:
- Product Category 01
- Product 1
- Product 2
- Product Category 02
- Product 3
- Product 4
So we have the same structure as in the last post:
Product Category 01
Be aware that the technical key of the product category is used for authorization. After we uploaded this table and created a data access control (how this works can be read in the last post) we also need a hierarchy on our product table.
So we go to the Data Builder and choose our space and either upload a CSV file or create a new table with the semantic usage "Dimension". I used a CSV file, and now we can create a hierarchy on this dimension table.
In the hierarchy dialog, we create a new hierarchy and the corresponding levels. In my case, you see above the levels are product category and then the product ID. After clicking on Close the hierarchy is created. We can now save and deploy our dimension table again to make sure all changes are applied.
Now we create a new graphical view and there we join the fact table with the dimension table. It is called associations. Click on the plus sign and select an association target. In this case, it is the Product Hierarchy. Now the system matches automatically the product ID of the fact table with the product ID of the hierarchy dimension. In case it doesn't work automatically, you have to make the connection for the join.
After we add the hierarchy dimension, we now add the data access control to restrict the data of the view. Click on the plus sign to add a data access control we created earlier.
As before, you have to map the fields of the sales view and the data access control. At the end save the view and deploy it. We switch to the Story Builder and create a new chart based on the sales view we just deployed. Select as source the sales view and add the product to the dimension and unit price to the measures.
As you see the data access control restricts the product ID with the product group we assigned before.
The restriction on a certain hierarchy node is common-use in several companies. In this post, I described one way you could build it. I don't know if there are other ways in the future.
I am Tobias, I write this blog since 2014, you can find me on Twitter, LinkedIn, Facebook and YouTube. I work as a Senior Business Warehouse Consultant. In 2016, I wrote the first edition of Analysis Office - The Comprehensive Guide. If you want, you can leave me a PayPal coffee donation. You can also contact me directly if you want.
- In my newsletter you get informed about new topics
- You learn how to use Analysis Office
- You get tips and tricks about SAP BI topics
- You get the first 3 chapters of my ebook Analysis Office - The Comprehensive Guide for free
You want to know SAP Analysis Office in a perfect detail?
You want to know how to build an Excel Dashboard with your Query in Analysis Office?
You want to know how functions in SAP Analysis Office works?
Then you have to take a look into Analysis Office - The Comprehensive Guide. Either as a video course or as an e-book.