<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]--><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph
{mso-style-priority:34;
margin-top:0in;
margin-right:0in;
margin-bottom:0in;
margin-left:.5in;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
span.EmailStyle20
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:black;
font-weight:normal;
font-style:normal;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
/* List Definitions */
@list l0
{mso-list-id:401103598;
mso-list-template-ids:1529149150;}
@list l0:level1
{mso-level-start-at:2;
mso-level-tab-stop:.5in;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level2
{mso-level-start-at:2;
mso-level-number-format:alpha-lower;
mso-level-tab-stop:1.0in;
mso-level-number-position:left;
text-indent:-.25in;}
@list l1
{mso-list-id:553856661;
mso-list-type:hybrid;
mso-list-template-ids:-261201472 67698703 67698713 67698715 67698703 67698713 67698715 67698703 67698713 67698715;}
@list l1:level1
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l1:level2
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l1:level3
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l1:level4
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l1:level5
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l1:level6
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l1:level7
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l1:level8
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l1:level9
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l2
{mso-list-id:773407092;
mso-list-template-ids:-1938275200;}
@list l2:level1
{mso-level-start-at:3;
mso-level-tab-stop:.5in;
mso-level-number-position:left;
text-indent:-.25in;}
@list l2:level2
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:1.0in;
mso-level-number-position:left;
text-indent:-.25in;}
@list l3
{mso-list-id:1942715052;
mso-list-template-ids:-390167548;}
@list l3:level2
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:1.0in;
mso-level-number-position:left;
text-indent:-.25in;}
@list l4
{mso-list-id:2120445104;
mso-list-template-ids:-1173167546;}
@list l4:level1
{mso-level-start-at:2;
mso-level-tab-stop:.5in;
mso-level-number-position:left;
text-indent:-.25in;}
@list l4:level2
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:1.0in;
mso-level-number-position:left;
text-indent:-.25in;}
ol
{margin-bottom:0in;}
ul
{margin-bottom:0in;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1033" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-US" link="#0563C1" vlink="#954F72" style="word-wrap:break-word">
<div class="WordSection1">
<p class="MsoNormal"><span style="color:black">OBIEE users,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:black"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:black">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.
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:black"><o:p> </o:p></span></p>
<ol style="margin-top:0in" start="1" type="1">
<li class="MsoListParagraph" style="color:black;margin-left:0in;mso-list:l1 level1 lfo3">
99.9% of all queries should have an ‘Agency’ filter. If not, this can cause slowness issues.
<o:p></o:p></li><ol style="margin-top:0in" start="1" type="a">
<li class="MsoListParagraph" style="color:black;margin-left:0in;mso-list:l1 level2 lfo3">
Even if creating a file that pulls ‘all’ agencies, create a ‘between 100 and 999’ type of filter.<o:p></o:p></li><li class="MsoListParagraph" style="color:black;margin-left:0in;mso-list:l1 level2 lfo3">
A ‘Date’ filter (AY, FY, Pay Period End) is very important as well but is not always available.
<o:p></o:p></li><li class="MsoListParagraph" style="color:black;margin-left:0in;mso-list:l1 level2 lfo3">
Slow queries can hurt all OBIEE users.<o:p></o:p></li></ol>
</ol>
<p class="MsoListParagraph"><span style="color:black"><o:p> </o:p></span></p>
<ol style="margin-top:0in" start="2" type="1">
<li class="MsoListParagraph" style="color:black;margin-left:0in;mso-list:l1 level1 lfo3">
We have a longtime standing Datamart rule, which is not being followed when building queries in OBIEE.
<o:p></o:p></li><ol style="margin-top:0in" start="1" type="a">
<li class="MsoListParagraph" style="color:black;margin-left:0in;mso-list:l1 level2 lfo3">
<u>Rule</u>: 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.
<o:p></o:p></li></ol>
</ol>
<p class="MsoListParagraph" style="margin-left:1.0in"><span style="color:black">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).<o:p></o:p></span></p>
<p class="MsoNormal"><!--[if gte vml 1]><v:shapetype id="_x0000_t75" coordsize="21600,21600" o:spt="75" o:preferrelative="t" path="m@4@5l@4@11@9@11@9@5xe" filled="f" stroked="f">
<v:stroke joinstyle="miter" />
<v:formulas>
<v:f eqn="if lineDrawn pixelLineWidth 0" />
<v:f eqn="sum @0 1 0" />
<v:f eqn="sum 0 0 @1" />
<v:f eqn="prod @2 1 2" />
<v:f eqn="prod @3 21600 pixelWidth" />
<v:f eqn="prod @3 21600 pixelHeight" />
<v:f eqn="sum @0 0 1" />
<v:f eqn="prod @6 1 2" />
<v:f eqn="prod @7 21600 pixelWidth" />
<v:f eqn="sum @8 21600 0" />
<v:f eqn="prod @7 21600 pixelHeight" />
<v:f eqn="sum @10 21600 0" />
</v:formulas>
<v:path o:extrusionok="f" gradientshapeok="t" o:connecttype="rect" />
<o:lock v:ext="edit" aspectratio="t" />
</v:shapetype><v:shape id="Straight_x0020_Connector_x0020_11" o:spid="_x0000_s1032" type="#_x0000_t75" style='position:absolute;margin-left:15.4pt;margin-top:33.4pt;width:54.75pt;height:13.5pt;z-index:251667456;visibility:visible;mso-wrap-distance-left:9pt;mso-wrap-distance-top:0;mso-wrap-distance-right:9pt;mso-wrap-distance-bottom:0;mso-position-horizontal:absolute;mso-position-horizontal-relative:text;mso-position-vertical:absolute;mso-position-vertical-relative:text'>
<v:imagedata src="cid:image009.png@01D82D85.592BC290" o:title="" />
<o:lock v:ext="edit" aspectratio="f" />
</v:shape><![endif]--><![if !vml]><span style="mso-ignore:vglayout;position:absolute;z-index:251667456;margin-left:21px;margin-top:44px;width:73px;height:18px"><img width="73" height="18" style="width:.7604in;height:.1875in" src="cid:image009.png@01D82D85.592BC290" v:shapes="Straight_x0020_Connector_x0020_11"></span><![endif]><!--[if gte vml 1]><v:shape id="Oval_x0020_5" o:spid="_x0000_s1031" type="#_x0000_t75" style='position:absolute;margin-left:5.6pt;margin-top:30.15pt;width:81pt;height:20.3pt;z-index:251660288;visibility:visible;mso-wrap-style:square;mso-width-percent:0;mso-height-percent:0;mso-wrap-distance-left:9pt;mso-wrap-distance-top:0;mso-wrap-distance-right:9pt;mso-wrap-distance-bottom:0;mso-position-horizontal:absolute;mso-position-horizontal-relative:text;mso-position-vertical:absolute;mso-position-vertical-relative:text;mso-width-percent:0;mso-height-percent:0;mso-width-relative:page;mso-height-relative:page'>
<v:imagedata src="cid:image006.png@01D82CAB.6C401B40" o:title="" />
<o:lock v:ext="edit" aspectratio="f" />
</v:shape><![endif]--><![if !vml]><span style="mso-ignore:vglayout;position:absolute;z-index:251660288;margin-left:7px;margin-top:40px;width:108px;height:27px"><img width="108" height="27" style="width:1.125in;height:.2812in" src="cid:image006.png@01D82CAB.6C401B40" v:shapes="Oval_x0020_5"></span><![endif]><!--[if gte vml 1]><v:shape id="Oval_x0020_3" o:spid="_x0000_s1030" type="#_x0000_t75" style='position:absolute;margin-left:198.4pt;margin-top:27.85pt;width:62.6pt;height:27.35pt;z-index:251659264;visibility:visible;mso-wrap-style:square;mso-width-percent:0;mso-height-percent:0;mso-wrap-distance-left:9pt;mso-wrap-distance-top:0;mso-wrap-distance-right:9pt;mso-wrap-distance-bottom:0;mso-position-horizontal:absolute;mso-position-horizontal-relative:text;mso-position-vertical:absolute;mso-position-vertical-relative:text;mso-width-percent:0;mso-height-percent:0;mso-width-relative:page;mso-height-relative:page'>
<v:imagedata src="cid:image007.png@01D82CAB.6C401B40" o:title="" />
<o:lock v:ext="edit" aspectratio="f" />
</v:shape><![endif]--><![if !vml]><span style="mso-ignore:vglayout;position:absolute;z-index:251659264;margin-left:265px;margin-top:37px;width:83px;height:36px"><img width="83" height="36" style="width:.8645in;height:.375in" src="cid:image013.png@01D82D85.592BC290" v:shapes="Oval_x0020_3"></span><![endif]><img width="350" height="76" style="width:3.6458in;height:.7916in" id="Picture_x0020_2" src="cid:image003.png@01D82CAA.C1D62910"><span style="color:black"><o:p></o:p></span></p>
<ol style="margin-top:0in" start="2" type="1">
<ol style="margin-top:0in" start="2" type="a">
<li class="MsoListParagraph" style="color:black;margin-left:0in;mso-list:l1 level2 lfo3">
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.
<o:p></o:p></li></ol>
</ol>
<p class="MsoListParagraph" style="margin-left:1.0in"><span style="color:black"><o:p> </o:p></span></p>
<ol style="margin-top:0in" start="3" type="1">
<li class="MsoListParagraph" style="color:black;margin-left:0in;mso-list:l1 level1 lfo3">
<span style="color:black">When creating </span>a filter, make sure to not use the ‘<span style="color:black">OR</span>’ function
<span style="color:black">improperly </span>as it can cause slowness issues. <span style="color:black">
I would recommend not using it at all as I rarely find it helpful. </span>In addition, you are able to add
<span style="color:black">multiple </span>filters <span style="color:black">on one filter window instead of separating into multiple filters.
</span>(ex. ‘GL Acct’ & ‘Cur Doc No’ <span style="color:black">seen below</span>). You
<span style="color:black">only need to </span>add a semicolon between each value (no spaces) to do multiple values at a time.<o:p></o:p></li><ol style="margin-top:0in" start="1" type="a">
<li class="MsoListParagraph" style="color:black;margin-left:0in;mso-list:l1 level2 lfo3">
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.
<o:p></o:p></li></ol>
</ol>
<p class="MsoNormal"><span style="color:black"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:black">Incorrect filter format:<o:p></o:p></span></p>
<p class="MsoNormal"><!--[if gte vml 1]><v:shape id="Straight_x0020_Connector_x0020_9" o:spid="_x0000_s1029" type="#_x0000_t75" style='position:absolute;margin-left:19.9pt;margin-top:68.3pt;width:24pt;height:15.75pt;z-index:251664384;visibility:visible;mso-width-percent:0;mso-height-percent:0;mso-wrap-distance-left:9pt;mso-wrap-distance-top:0;mso-wrap-distance-right:9pt;mso-wrap-distance-bottom:0;mso-position-horizontal:absolute;mso-position-horizontal-relative:text;mso-position-vertical:absolute;mso-position-vertical-relative:text;mso-width-percent:0;mso-height-percent:0;mso-width-relative:page;mso-height-relative:page'>
<v:imagedata src="cid:image014.png@01D82D85.592BC290" o:title="" />
<o:lock v:ext="edit" aspectratio="f" />
</v:shape><![endif]--><![if !vml]><span style="mso-ignore:vglayout;position:absolute;z-index:251664384;margin-left:27px;margin-top:91px;width:32px;height:21px"><img width="32" height="21" style="width:.3333in;height:.2187in" src="cid:image014.png@01D82D85.592BC290" v:shapes="Straight_x0020_Connector_x0020_9"></span><![endif]><!--[if gte vml 1]><v:shape id="Rectangle_x0020_8" o:spid="_x0000_s1028" type="#_x0000_t75" style='position:absolute;margin-left:151.9pt;margin-top:39.8pt;width:16.5pt;height:10.5pt;z-index:251663360;visibility:visible;mso-width-percent:0;mso-height-percent:0;mso-wrap-distance-left:9pt;mso-wrap-distance-top:0;mso-wrap-distance-right:9pt;mso-wrap-distance-bottom:0;mso-position-horizontal:absolute;mso-position-horizontal-relative:text;mso-position-vertical:absolute;mso-position-vertical-relative:text;mso-width-percent:0;mso-height-percent:0;mso-width-relative:page;mso-height-relative:page'>
<v:imagedata src="cid:image015.png@01D82D85.592BC290" o:title="" />
<o:lock v:ext="edit" aspectratio="f" />
</v:shape><![endif]--><![if !vml]><span style="mso-ignore:vglayout;position:absolute;z-index:251663360;margin-left:203px;margin-top:53px;width:22px;height:14px"><img width="22" height="14" style="width:.2291in;height:.1458in" src="cid:image015.png@01D82D85.592BC290" v:shapes="Rectangle_x0020_8"></span><![endif]><!--[if gte vml 1]><v:shape id="Oval_x0020_7" o:spid="_x0000_s1027" type="#_x0000_t75" style='position:absolute;margin-left:14.65pt;margin-top:64.25pt;width:39pt;height:24.05pt;z-index:251662336;visibility:visible;mso-wrap-style:square;mso-width-percent:0;mso-height-percent:0;mso-wrap-distance-left:9pt;mso-wrap-distance-top:0;mso-wrap-distance-right:9pt;mso-wrap-distance-bottom:0;mso-position-horizontal:absolute;mso-position-horizontal-relative:text;mso-position-vertical:absolute;mso-position-vertical-relative:text;mso-width-percent:0;mso-height-percent:0;mso-width-relative:page;mso-height-relative:page'>
<v:imagedata src="cid:image008.png@01D82CAB.6C401B40" o:title="" />
<o:lock v:ext="edit" aspectratio="f" />
</v:shape><![endif]--><![if !vml]><span style="mso-ignore:vglayout;position:absolute;z-index:251662336;margin-left:20px;margin-top:86px;width:52px;height:32px"><img width="52" height="32" style="width:.5416in;height:.3333in" src="cid:image008.png@01D82CAB.6C401B40" v:shapes="Oval_x0020_7"></span><![endif]><img width="305" height="176" style="width:3.177in;height:1.8333in" id="Picture_x0020_1" src="cid:image016.jpg@01D82D85.592BC290"><span style="color:black"><br>
<br>
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:black">Recommended filters: <o:p></o:p></span></p>
<p class="MsoNormal"><!--[if gte vml 1]><v:shape id="Rectangle_x0020_10" o:spid="_x0000_s1026" type="#_x0000_t75" style='position:absolute;margin-left:151.45pt;margin-top:39.35pt;width:16.5pt;height:9.75pt;z-index:251666432;visibility:visible;mso-width-percent:0;mso-height-percent:0;mso-wrap-distance-left:9pt;mso-wrap-distance-top:0;mso-wrap-distance-right:9pt;mso-wrap-distance-bottom:0;mso-position-horizontal:absolute;mso-position-horizontal-relative:text;mso-position-vertical:absolute;mso-position-vertical-relative:text;mso-width-percent:0;mso-height-percent:0;mso-width-relative:page;mso-height-relative:page'>
<v:imagedata src="cid:image018.png@01D82D85.592BC290" o:title="" />
<o:lock v:ext="edit" aspectratio="f" />
</v:shape><![endif]--><![if !vml]><span style="mso-ignore:vglayout;position:absolute;z-index:251666432;margin-left:202px;margin-top:52px;width:22px;height:13px"><img width="22" height="13" style="width:.2291in;height:.1354in" src="cid:image018.png@01D82D85.592BC290" v:shapes="Rectangle_x0020_10"></span><![endif]><img width="346" height="103" style="width:3.6041in;height:1.0729in" id="Picture_x0020_4" src="cid:image004.png@01D82CAA.C1D62910"><span style="color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:black"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:black">Thanks for your understanding.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:black">Aaron Wallace<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:black">Sr. Datamart Business Analyst<o:p></o:p></span></p>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
</body>
</html>