CATALOG DEVELOPMENT (ANALYTICS
How to enable a search for any string mimicking like % in the back-end, without forcing the user to enter a value
Use contains any
Use the default %%
HOW TO allow search using both upper function and like. This
can be done customizing the SQL
SOLUTION="Sales Prospects
List"."SERV_ADDRESS" LIKE UPPER(CONCAT(CONCAT('%',
'@{var_serv_address1}') ,'%'))
I documented this, and just in case it happens again sending
you so you have it on file
Error: No fact table
exists at the requested level of detail
Problem: The relationship between the fact and dimension
table is not properly defined.,. most likely missing:
FIX: Make sure all the relationships exist. If necessary,
start from scratch and create the business model, the dimension and hierarchies
again
·
There must be a foreign key defined at the
physical model from the fact to the dimension
·
There must be a direct join defined in the
Business model
Problem : AGGREGATION GRAIN
Aggregation
The aggregation grain is defined in the content tab of the logical table source.When the query grain match the aggregation grain of a logical table source, this one is used to create the physical query.
You can also define a aggregation grain calculation on a measure. This kind of calculation creates level-based measure.
Fix for aggregation grain: Link the Content tab to the
Dimension Level of the dimension you are working with.
ACCURATE COUNTS
-Try to count only on the primary key of the fact table,
other methods are highly unreliable
-Use outer join from the fact to the dimensions so the
values are not missing. The best method is to use (right outer) right under
that fact
DISPLAY ALL VALUES
It is very limited
what can be done, it seems to work only with 2 fields
OPTION 1: Hierarchy
One hierarchy will be
the “leading one”, and the values will be displayed to be picked, the other one
is forced to be in the table. Adding more hierarchies will not work. Limkited to 1
OPTION 2: One regular
field and select the option Pivot Table Prompt section, Left lick SUM icon,
then click After
Creating action items to navigate to another page with drill
down details
Behavior: Any column in the X axis of the pivot table should
be included in a filter in the detail being a “x is prompted filter”
Step1: create as many
filters “x is prompted” as columns
participate in the graph/pivo table in the group by section.
Step2:
A table will link fine
How to define outer joins that filter in the fact table
The problem is when the fact table filters data (for example
status=X,y,z, ) then that overwrites the outer join, options
SOLUTION:Physical layer: it can be done with an opaque view,
BUT this would mean having two separate racts
SOLUTION:
Business model layer:
(A)
It requires implementing an outer join (see
screenshot)
(B)
It requires creating an expression based on the
columns that will be filtered so the outer join can still be valid. Example:
IfNull("LMS Track Completion by Job
Function"."CLASSES_COMPLETED"."STATUS" ,'Not Taken')
Presentation: It requires being careful when doing the
filter. This works
Calculating percentages accurately
The percentage has to be calculated in the repository, if
done in the front end it will fail.
PROBLEM: COUNT DISTINCT not working (results not correct)
SCENARIO:
SOLUTIONS: Define the measure in the report
PROBLEM: Building analysis that will be prompted is difficult because it retrieves lots more data that one needs while developing report / Ability to protect filters
RESOLUTION: Use filters in the report to get sample data,
these filters will be later overwritten by the dashboard prompts. This is the
default behavior in OBIEE. Using the “IS PROMPTED” is UNNECESSARY.
OPTIONAL: Please notice if you want to keep a filter (not to
be overwritten) you can select the option Protect filter
Example:
When running analysis by itself
When running analysis in Dashboard Prompt, the Service Line
will be changed, but the year will not
No comments:
Post a Comment