[Datamart-News] OBIEE query build issues

Tue Mar 1 15:59:40 PST 2022

OBIEE users,

We seem to be using some improper query building processes in OBIEE. These issues are causing some major OBIEE server slowness problems. Below are some helpful tips.

  1.  99.9% of all queries should have an 'Agency' filter. If not, this can cause slowness issues.
     *   Even if creating a file that pulls 'all' agencies, create a 'between 100 and 999' type of filter.
     *   A 'Date' filter (AY, FY, Pay Period End) is very important as well but is not always available.
     *   Slow queries can hurt all OBIEE users.

  1.  We have a longtime standing Datamart rule, which is not being followed when building queries in OBIEE.
     *   Rule: A user must first select fields from the main financial table and only select a profile table field if it is not contained on the financial table.

Example: The 'PCA' field is contained in the 'All Acct Event' table as well as on the PCA (profile) table (now titled '026 PCA'). A user should select the 'PCA' field from the 'All Acct Event' table for the selected columns criteria section and not the '026 PCA' field. In this example, selecting the '026 PCA' field from the PCA profile table could cause slowness issues (same as it did with Hyperion).
[cid:image009.png at 01D82D85.592BC290][cid:image006.png at 01D82CAB.6C401B40][cid:image013.png at 01D82D85.592BC290][cid:image003.png at 01D82CAA.C1D62910]

     *   Example continued: It is ok to select the 'PCA title' field from the 'PCA' table because it is not contained in the 'All Acct Event' table.

  1.  When creating a filter, make sure to not use the 'OR' function improperly as it can cause slowness issues. I would recommend not using it at all as I rarely find it helpful. In addition, you are able to add multiple filters on one filter window instead of separating into multiple filters. (ex. 'GL Acct' & 'Cur Doc No' seen below). You only need to add a semicolon between each value (no spaces) to do multiple values at a time.
     *   A 'not begins with' filter is shown below. It is recommended to use the filter window drop down menu 'is not LIKE (pattern match)' over other options.

Incorrect filter format:
[cid:image014.png at 01D82D85.592BC290][cid:image015.png at 01D82D85.592BC290][cid:image008.png at 01D82CAB.6C401B40][cid:image016.jpg at 01D82D85.592BC290]

Recommended filters:
[cid:image018.png at 01D82D85.592BC290][cid:image004.png at 01D82CAA.C1D62910]

Thanks for your understanding.
Aaron Wallace
Sr. Datamart Business Analyst

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://omls.oregon.gov/pipermail/datamart-news/attachments/20220301/fba46b83/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image003.png
Type: image/png
Size: 5980 bytes
Desc: image003.png
URL: <https://omls.oregon.gov/pipermail/datamart-news/attachments/20220301/fba46b83/attachment.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image004.png
Type: image/png
Size: 7855 bytes
Desc: image004.png
URL: <https://omls.oregon.gov/pipermail/datamart-news/attachments/20220301/fba46b83/attachment-0001.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image006.png
Type: image/png
Size: 658 bytes
Desc: image006.png
URL: <https://omls.oregon.gov/pipermail/datamart-news/attachments/20220301/fba46b83/attachment-0002.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image007.png
Type: image/png
Size: 706 bytes
Desc: image007.png
URL: <https://omls.oregon.gov/pipermail/datamart-news/attachments/20220301/fba46b83/attachment-0003.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image008.png
Type: image/png
Size: 558 bytes
Desc: image008.png
URL: <https://omls.oregon.gov/pipermail/datamart-news/attachments/20220301/fba46b83/attachment-0004.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image009.png
Type: image/png
Size: 389 bytes
Desc: image009.png
URL: <https://omls.oregon.gov/pipermail/datamart-news/attachments/20220301/fba46b83/attachment-0005.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image013.png
Type: image/png
Size: 1783 bytes
Desc: image013.png
URL: <https://omls.oregon.gov/pipermail/datamart-news/attachments/20220301/fba46b83/attachment-0006.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image014.png
Type: image/png
Size: 394 bytes
Desc: image014.png
URL: <https://omls.oregon.gov/pipermail/datamart-news/attachments/20220301/fba46b83/attachment-0007.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image015.png
Type: image/png
Size: 171 bytes
Desc: image015.png
URL: <https://omls.oregon.gov/pipermail/datamart-news/attachments/20220301/fba46b83/attachment-0008.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image016.jpg
Type: image/jpeg
Size: 12568 bytes
Desc: image016.jpg
URL: <https://omls.oregon.gov/pipermail/datamart-news/attachments/20220301/fba46b83/attachment.jpg>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image018.png
Type: image/png
Size: 171 bytes
Desc: image018.png
URL: <https://omls.oregon.gov/pipermail/datamart-news/attachments/20220301/fba46b83/attachment-0009.png>