Prompt when universe table accessed

building semantic layer
1

Hi,

I’ve built a class from View A, which contains over 100s of objects (I know this View is huge but it’s needed per requirement).
I’ve a requirement where it should prompt user anytime when they access any objects from this View A. The prompt should basically their Employee ID so they can only see the data that they have access to.
I’ve already done the class/dimensions mapping in Universe from this View A.
Now, I feel like holy crap how do I enforce prompt on this view? There is no join in Universe for View A. I don’t want to put prompt in all 100 dimensions as easy fix. I’m rather looking for a prompt where any report refresh or ad-hoc from this View A will automatically prompt user to select or enter Employee ID.
The requirement is very simple just prompt users when accessing data from View A.
e.g.
[list]Table/View Class Objects
View A Employee_View_A Year, Month, Week, Manager_Name
[/list]
Any thoughts or suggestions?

btw, I’m using BI 4.2 Universe Designer Tool and Oracle 11G

Thanks,

zulu1900 (BOB member since 2005-07-27)

2

You might be able to accomplish this using a self-join. Search here for self join prompt, or see this post

charlie :us: (BOB member since 2002-08-20)

3

Charlie,
Thanks for your help! I think you resolved my issue. But I’m little not clear (from your link response below):

You can create a self-restricting join on a table column in a form like:
Code:
table_customers.customer_id IN @prompt(‘select customer IDs’,‘N’,multi,free)
Then whenever any object is used from this table then the self-restricting join is applied so a report user is prompted for customer IDs.

I did not get it. The example is using one object (Customer ID) table_customers.customer_id, but says “whenever any object is used from this table then the self-restricting join is applied so a report user is prompted for customer IDs.”
Can you explain me little more as I’m not understanding the scenario?

Thanks,

zulu1900 (BOB member since 2005-07-27)

4

It means that whenever a query involving objects from that table is built, the SQL will include the self join. If the join contains a prompt, it will be executed.

charlie :us: (BOB member since 2002-08-20)

5

Fabulous, I’ll try it and let you know if I encounter any issue.

Thanks for your quick reply.

zulu1900 (BOB member since 2005-07-27)

6

Charlie,
I was able to use self restricting join successfully using @prompt. However, when I run report from that table it does not prompt.
Is there anything that I need to do to activate the prompt?

zulu1900 (BOB member since 2005-07-27)

7

Does the table join to other tables?
If it does, is the self-restriction join in the same context?

8

Are you saying that the code isn’t showing up in your WebI report? If so, you need to do something to force regeneration of the code, like in Data Access/Edit, click on View Script/Use custom SQL. Or add/remove Result Objects. Sometimes Log off/on does the trick.

charlie :us: (BOB member since 2002-08-20)

9

I’m using SAP BI 4.2 Universe Design Tool. Here is my self restricting join:

TABLE_A.EMP_ID IN (SELECT EMP_ID FROM EMP_SEC_TAB
WHERE EMP_SEC_TAB.PROFILE_ID IN (
@prompt(‘Select Role:’,‘A’,‘JOB ROLE\ROLE NAME’,Mono,primary_key,not_persistent)))

I parsed the above join and it says successful. I saved the Universe and exported as well.
From the universe, I selected the class that uses TABLE_A. I double clicked one of the object went to properties. I clicked EDIT and went to Query Panel, then clicked “SQL” from the toolbar. Below is what it showed me:
SELECT DISTINCT
TABLE_A.FULL_NAME
FROM
TABLE_A
I clicked RUN and it dumped all name list from the DB :(. It did not prompt at all.

zulu1900 (BOB member since 2005-07-27)

10

If you only want to see one Employee ID, change “IN” to “=” in your prompt.

charlie :us: (BOB member since 2002-08-20)

11

Yeah, depending on user role, he/she will see one more employee.
But my main issue is that the @prompt does not prompt when I run the report.

zulu1900 (BOB member since 2005-07-27)

12

Not sure what’s happening there. I tried it and used your workflow; got this:

SELECT DISTINCT
  D_TIME.calendar_date
FROM
  D_TIME
WHERE
  ( D_TIME.calendar_year=@Prompt('Select Year:','N',{'2016','2017'},mono,free)  )

So my self-restricting join showed up in the object properties/Edit/SQL.

charlie :us: (BOB member since 2002-08-20)

13

Can you please add your join here so I can validate what I did wrong.

zulu1900 (BOB member since 2005-07-27)

14

Sure. I have a time dimension table, with a self-restricting join built on the calendar_year column:

D_TIME.calendar_year=@Prompt('Select Year:','N',{'2016','2017'},mono,free)

charlie :us: (BOB member since 2002-08-20)

15

Ok, looks like my issue is related to the join:
TABLE_A.EMP_ID IN (SELECT EMP_ID FROM EMP_SEC_TAB
WHERE EMP_SEC_TAB.PROFILE_ID IN (
@prompt(‘Select Role:’,‘A’,‘JOB ROLE\ROLE NAME’,Mono,primary_key,not_persistent)))

There are two tables in my join (TABLE_A and EMP_SEC_TAB). TABLE_A needs to validate user identity from EMP_SEC_TAB table. But EMP_SEC_TAB is hidden to users. When I add object from EMP_SEC_TAB then the prompt worked as expected.
How I’m going to accomplish this now? any time users use TABLE_A they should be displayed with prompt. however they should not be seeing EMP_SEC_TAB table/class.

zulu1900 (BOB member since 2005-07-27)

16

Can’t you just hide the class?

charlie :us: (BOB member since 2002-08-20)

17

I can hide the class but the problem is when ad-hoc users use objects from TABLE_A then they won’t get any prompt I guess. and I want to force user to select prompt, but it looks like I can’t achieve it without using objects from either EMP_SEC_TAB table or creating a new Derived Table with prompt. I just don’t want to add Derived Table on my universe if there is other solution available.

zulu1900 (BOB member since 2005-07-27)

18

Did you try it? The prompt is coded into the join; they will still see the prompt even if the class is hidden. Hiding will just keep them from dragging the objects into query results.

charlie :us: (BOB member since 2002-08-20)

19

Charlie,
No luck, does not prompt until I use object from EMP_SEC_TAB dim. I’m attaching some sample data just in case if anyone would like to give a shot. The attachments include TABLE_A (Prime table for report data) and EMP_SEC_TAB (security table for user role validation).
Let me know if you can create a self restriction join with active prompt for TABLE_A. The requirement is simple:

  1. EMP_SEC_TAB table/class should always be hidden from user.
  2. Any time, any objects from TABLE_A is used (in canned report or ad-hoc), it should prompt user to identify their job role.
    Sample Data.zip (13.0 KB)

zulu1900 (BOB member since 2005-07-27)

20

Can you not add a mandatory filter on the Class and define the prompt there?

Mak 1 :uk: (BOB member since 2005-01-06)