Scenario 1:
Suppose in a Universe structure we have tables as shown in the diagram below. Tables A,B and C are in context ABC and C,E and F are in context CDE Now if there is a requirement which requires a join between Table E and Table B, we can define a new context BCDE. But what is the easiest way to implement this?
Define a shortcut join between tables B and E. To do this, join the tables normally. Then open the join editor and check the box shortcut join.
The join will show as a dotted line between the two tables. This kind of a join does not create loop and cannot be placed in any context. The shortcut join between Table B and Table E will only work when objects from both the tables are selected in the query panel of report.
Scenario 2:
We have objects from 3 tables A,B and C in the query panel of a report. Among them C is a lookup table which holds values with respect to keys. Table B holds the foreign key to the table C. A filter condition is applied to Table C in Query Level. The resulting Query is:
SELECT A.a, B.b
FROM
A,B,C
WHERE A.bfk=B.pk
AND B.cfk=C.pk
AND C.val=’XXX’
Now, we define Primary key and foreign key relations for Tables B and C. Suppose the surrogate key corresponding to val ‘XXX’ is 12. How will the query change after implementing this index awareness?
The resulting query will be:
SELECT A.a, B.b
FROM
A,B
WHERE A.bfk=B.pk
AND B.cfk=12
The table C will be eliminated from the Query and the foreign key to C in table be will be equated to 12, the key corresponding to ‘XXX’. The join with C will be eliminated.
Scenario 3:
A User named User1 wants a privilege of running a BO Report for 40 mins and retrieving a report with row limit 40,000. However, in the SQL parameters of the universe, the row limit is set to 10,000 and the execution time limit is set to 10 mins. How can you give the user the required rights?
Go to Tools -> Manage Security Click on Mange Access Restrictions. Create a new restriction. In the Controls tab of the restriction, set the row limit to 40,000 and execution time limit to 40 mins.
In the Main Window, apply this restriction to User1
Scenario 4:
In a report we have a table like this:
Dim A Dim B Measure 1
AA 12 100
BB 34 50
CC 21 40
DD 43 90
EE 45 200
FF 54 75
There is a report filter applied on this block which restricts both DIM A and DIM B in the table, i.e only select values of DIM A, DIM B and the corresponding measures from the query are displayed in the table. Another column needs to be added which will calculate the average for each row based on the sum of Measure 1 in the table (not all values in report). What would be the formula?
For this we will require the sum of Measure 1 in the table, which can be achieved only by In Block keyword.
The formula will be:
Sum( Measure 1 ) / Sum( Measure 1 In Block )
Scenario 5:
In the embedded sheet of an Xcelsius dashboard, we have data like:
Field A Field B Field C Field D
Xxx Tyu 100 98
Xxx Yyy 45 76
Xxx Dev 56 87
Yyy Wes 78 13
Yyy Rid 200 106
In the dash board, we need a selector on Field A for a chart which will plot the values of Field C and Field D against all Field B values against one Field A. How can we achieve that?
This can be achieved using filtered rows. In the Selector Properties, select Insertion Type as filtered rows and map the Labels by selecting all values of Field A(including the duplicates).
The labels will display unique values and when a particular value is selected, all rows corresponding to the value of Field A will be selected as output. The chart component needs to be mapped to the output this selector.
