It was a bit relaxing day so thought of blog something but not sure what to blog L. I guess many people might be knowing about this but thought of re-sharing as it may help at least some one.
Okay lets goto topic. If you have some question like following , all those are can be answered using Querying the repository.
a) How to find all hidden presentation attributes (may be based on some condition)
An example query filter (there could be other options):
• In the Administration Tool, navigate to: Tools > Query Repository (Ctrl+Q)
• Name: * (to query all)
• Type: Presentation Column
• Click Filter
o Click 'Item' row to bring up drop down arrow
o Choose 'Visibility Filter'
o In the next column, click in the row to choose the '< >' (not null) operator
o Leave the Value column blank
o Click 'OK'
• Click 'Query'
Like this we can answer many of following questions.
b) How to do I know impact of an foreign key. Where all it is joined , if you have a single alias in physical it is easy to find where all it is joined but if you have many aliases and you don't know what are those aliases (Example Date Dimension could have more than 100 aliases – you want to know what are those and what is the count of joins and join conditions etc).
In other words find all joins with in RPD , with what are the facts joined with dimension on which condition.
Launch query tool , choose type as "Physical Foreign Key" , optionally you can set filters (if you are looking for all joins then don't choose any filters) , now make sure using "Columns" button choose all required columns ie Table1, Table2, Join Expression as bare minimum as bare minimum then run the "Query". Using "Save" button you can save them to spreadsheet.
c) How do I know how many presentation attributes are there (may be by subject area or so, or how many attributes with prefix as some 'abc' or
d) How many MUDE projects are there what are those names
e) How many ragged , skipped hierarchies are in the RPD and what are those.
f) How many variables are there what are those default values , what is the initialization block it is associated to , what is the sql to populate the variable,
g) How many users/roles are defined in the RPD and who are those users
h) How to find how many nested presentation folders are there in all subject areas and what are those. (if you have old technic -> then you may have to query differently).
i) How many time dimensions are there or how many parent child hierarchies are available.
j) How many lookup/fact/Bridge tables are there and what are those.
k) How many presentation attributes have different name than that of logical columns.
l) How to find how many presentation attributes are re-named with alias or in other words report created long back, but presentation attributes are modified , we can find find old alias and corresponding new presentation attribute.
In order to achieve this name should be * , type from drop down "Presentation Table" if looking for presentation table , "Presentation Column" for column, Click in on 'Filter" and choose Alias operator as not equal and input values as NULL.
m) How to find more than 2 levels Hierarchical attributes
n) How to find all metrics which has aggregate as SUM /MIN/AVG etc
o) How to find all derived attributes and what are those ?
p) Find out all the logical sources which has filter in it.
q) How to find all Logical source which are disabled for query also there are many other filters as shown below
Bottom line we can do many things with Query Repository tool, it is very useful for OBIEE administrators to quickly find few useful things and save the results to present.
I hope you like the post.
