tag:blogger.com,1999:blog-38200314715245037312024-02-24T23:29:38.383-08:00Oracle OLAPThe most powerful, open Analytic EngineBrian Macdonaldhttp://www.blogger.com/profile/18408740222558531436noreply@blogger.comBlogger77125tag:blogger.com,1999:blog-3820031471524503731.post-18728120056467217762014-03-04T12:57:00.002-08:002014-03-04T13:00:42.916-08:00The OLAP Extension is now available in SQL Developer 4.0<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhJbhVcy8__pd4EUgzWTOw5ZvV00L2SnuEjGEnM7D9uEtu3rIkqTqHgWQhVY8I3X2fFju9_an2pjJ9muc5ZjwwhJNIJ2esbFPHQSfN62-D7MUcq80z8TpHsR0OBKGrmONtEirtIx2S2wUY/s1600/sqldev.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhJbhVcy8__pd4EUgzWTOw5ZvV00L2SnuEjGEnM7D9uEtu3rIkqTqHgWQhVY8I3X2fFju9_an2pjJ9muc5ZjwwhJNIJ2esbFPHQSfN62-D7MUcq80z8TpHsR0OBKGrmONtEirtIx2S2wUY/s1600/sqldev.png" height="307" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<!--[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]--><br />
<!--[if gte mso 9]><xml>
<o:OfficeDocumentSettings>
<o:AllowPNG/>
</o:OfficeDocumentSettings>
</xml><![endif]--><!--[if gte mso 9]><xml>
<w:WordDocument>
<w:View>Normal</w:View>
<w:Zoom>0</w:Zoom>
<w:TrackMoves>false</w:TrackMoves>
<w:TrackFormatting/>
<w:PunctuationKerning/>
<w:ValidateAgainstSchemas/>
<w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid>
<w:IgnoreMixedContent>false</w:IgnoreMixedContent>
<w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText>
<w:DoNotPromoteQF/>
<w:LidThemeOther>EN-US</w:LidThemeOther>
<w:LidThemeAsian>X-NONE</w:LidThemeAsian>
<w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript>
<w:Compatibility>
<w:BreakWrappedTables/>
<w:SnapToGridInCell/>
<w:WrapTextWithPunct/>
<w:UseAsianBreakRules/>
<w:DontGrowAutofit/>
<w:SplitPgBreakAndParaMark/>
<w:DontVertAlignCellWithSp/>
<w:DontBreakConstrainedForcedTables/>
<w:DontVertAlignInTxbx/>
<w:Word11KerningPairs/>
<w:CachedColBalance/>
</w:Compatibility>
<m:mathPr>
<m:mathFont m:val="Cambria Math"/>
<m:brkBin m:val="before"/>
<m:brkBinSub m:val="--"/>
<m:smallFrac m:val="off"/>
<m:dispDef/>
<m:lMargin m:val="0"/>
<m:rMargin m:val="0"/>
<m:defJc m:val="centerGroup"/>
<m:wrapIndent m:val="1440"/>
<m:intLim m:val="subSup"/>
<m:naryLim m:val="undOvr"/>
</m:mathPr></w:WordDocument>
</xml><![endif]--><!--[if gte mso 9]><xml>
<w:LatentStyles DefLockedState="false" DefUnhideWhenUsed="true"
DefSemiHidden="true" DefQFormat="false" DefPriority="99"
LatentStyleCount="267">
<w:LsdException Locked="false" Priority="0" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Normal"/>
<w:LsdException Locked="false" Priority="9" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="heading 1"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 2"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 3"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 4"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 5"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 6"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 7"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 8"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 9"/>
<w:LsdException Locked="false" Priority="39" Name="toc 1"/>
<w:LsdException Locked="false" Priority="39" Name="toc 2"/>
<w:LsdException Locked="false" Priority="39" Name="toc 3"/>
<w:LsdException Locked="false" Priority="39" Name="toc 4"/>
<w:LsdException Locked="false" Priority="39" Name="toc 5"/>
<w:LsdException Locked="false" Priority="39" Name="toc 6"/>
<w:LsdException Locked="false" Priority="39" Name="toc 7"/>
<w:LsdException Locked="false" Priority="39" Name="toc 8"/>
<w:LsdException Locked="false" Priority="39" Name="toc 9"/>
<w:LsdException Locked="false" Priority="35" QFormat="true" Name="caption"/>
<w:LsdException Locked="false" Priority="10" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Title"/>
<w:LsdException Locked="false" Priority="1" Name="Default Paragraph Font"/>
<w:LsdException Locked="false" Priority="11" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtitle"/>
<w:LsdException Locked="false" Priority="22" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Strong"/>
<w:LsdException Locked="false" Priority="20" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Emphasis"/>
<w:LsdException Locked="false" Priority="59" SemiHidden="false"
UnhideWhenUsed="false" Name="Table Grid"/>
<w:LsdException Locked="false" UnhideWhenUsed="false" Name="Placeholder Text"/>
<w:LsdException Locked="false" Priority="1" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="No Spacing"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 1"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 1"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 1"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 1"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 1"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 1"/>
<w:LsdException Locked="false" UnhideWhenUsed="false" Name="Revision"/>
<w:LsdException Locked="false" Priority="34" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="List Paragraph"/>
<w:LsdException Locked="false" Priority="29" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Quote"/>
<w:LsdException Locked="false" Priority="30" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Quote"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 1"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 1"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 1"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 1"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 1"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 1"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 1"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 1"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 2"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 2"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 2"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 2"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 2"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 2"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 2"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 2"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 2"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 2"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 2"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 2"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 2"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 2"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 3"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 3"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 3"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 3"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 3"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 3"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 3"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 3"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 3"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 3"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 3"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 3"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 3"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 3"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 4"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 4"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 4"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 4"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 4"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 4"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 4"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 4"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 4"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 4"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 4"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 4"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 4"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 4"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 5"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 5"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 5"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 5"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 5"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 5"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 5"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 5"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 5"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 5"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 5"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 5"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 5"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 5"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 6"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 6"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 6"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 6"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 6"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 6"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 6"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 6"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 6"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 6"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 6"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 6"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 6"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 6"/>
<w:LsdException Locked="false" Priority="19" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtle Emphasis"/>
<w:LsdException Locked="false" Priority="21" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Emphasis"/>
<w:LsdException Locked="false" Priority="31" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtle Reference"/>
<w:LsdException Locked="false" Priority="32" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Reference"/>
<w:LsdException Locked="false" Priority="33" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Book Title"/>
<w:LsdException Locked="false" Priority="37" Name="Bibliography"/>
<w:LsdException Locked="false" Priority="39" QFormat="true" Name="TOC Heading"/>
</w:LatentStyles>
</xml><![endif]--><!--[if gte mso 10]>
<style>
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-qformat:yes;
mso-style-parent:"";
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin-top:0in;
mso-para-margin-right:0in;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0in;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:minor-bidi;}
</style>
<![endif]-->
<br />
<div class="MsoNormal">
<span style="mso-no-proof: yes;"></span></div>
<div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;">
</div>
<div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;">
<span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">T<a href="https://www.blogger.com/null" name="_GoBack"></a>he OLAP
Extension is now in SQL Developer 4.0.<br />
<br />
See </span><a href="http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/sqldev-releasenotes-v4-1925251.html" target="_blank"><span style="color: blue; font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/sqldev-releasenotes-v4-1925251.html</span></a><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> for the details.<br />
<br />
The OLAP functionality is mentioned toward the bottom of the web page.</span></div>
<div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;">
<span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">You will still need AWM 12.1.0.1.0
to</span></div>
<ul type="disc">
<li class="MsoNormal" style="line-height: normal; mso-list: l0 level1 lfo1; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Manage and enable cube and dimension MV's.</span></li>
<li class="MsoNormal" style="line-height: normal; mso-list: l0 level1 lfo1; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Manage data security.</span></li>
<li class="MsoNormal" style="line-height: normal; mso-list: l0 level1 lfo1; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Create and edit nested measure folders (i.e. measure
folders that are children of other measure folders)</span></li>
<li class="MsoNormal" style="line-height: normal; mso-list: l0 level1 lfo1; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Create and edit Maintenance Scripts</span></li>
<li class="MsoNormal" style="line-height: normal; mso-list: l0 level1 lfo1; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Manage multilingual support for OLAP Metadata objects</span></li>
<li class="MsoNormal" style="line-height: normal; mso-list: l0 level1 lfo1; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Use the OBIEE plugin or the Data Validation plugin </span></li>
</ul>
<div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;">
<span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">What is new or improved:</span></div>
<ul type="disc">
<li class="MsoNormal" style="line-height: normal; mso-list: l1 level1 lfo2; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">New Calculation Expression editor for calculated
measures. This allows the user to nest different types to calculated
measures easily. For instance a user can now create a Moving Total
of a Prior Period as one calculated measure. In AWM, it would have
required a user to create a Prior Period first and then create a Moving
Total calculated measure which referred to the Prior Period measure.
Also the new Calculation Expression editor displays hypertext helper
templates when the user selects the OLAP API syntax in the editor.</span></li>
<li class="MsoNormal" style="line-height: normal; mso-list: l1 level1 lfo2; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Support for OLAP DML command execution in the SQL
Worksheet. Simply prefix OLAP DML commands by a '~' and then select
the execute button to execute them on the SQL Worksheet. The output
of the command will appear in the DBMS Output Window if it is opened, or
the Script Output Window if the user has executed 'set serveroutput on'
before executing the DML command.</span></li>
<li class="MsoNormal" style="line-height: normal; mso-list: l1 level1 lfo2; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Improved OLAP DML Program Editor integrated within the
SQL Developer framework.</span></li>
<li class="MsoNormal" style="line-height: normal; mso-list: l1 level1 lfo2; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">New diagnostic reports in the SQL Developer Report
navigator.</span></li>
<li class="MsoNormal" style="line-height: normal; mso-list: l1 level1 lfo2; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Ability to create a fact view with a measure dimension
(i.e. "pivot cube"). This functionality is accessible from
the SQL Developer Tools-OLAP menu option.</span></li>
<li class="MsoNormal" style="line-height: normal; mso-list: l1 level1 lfo2; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Cube scripts have been renamed to Build Specifications
and are now accessible within the Create/Edit Cube dialog. The Build
Specifications editor there, is similar to the calculation expression
editor as far as functionality.</span></li>
</ul>
Christopher Kearneyhttp://www.blogger.com/profile/17722642593898599800noreply@blogger.com0tag:blogger.com,1999:blog-3820031471524503731.post-83476132954244942662013-03-28T02:02:00.001-07:002013-03-28T02:02:26.231-07:00Oracle SQL Developer Data Modeler 3.3 now available<p><span style="font-family: Helvetica, Arial, sans-serif;"><span class="Apple-style-span" style="font-size: 13px; line-height: 15px;"><img style="display: block; margin-left: auto; margin-right: auto;" title="" src="http://www.oracle.com/technetwork/developer-tools/datamodeler/dm33banner-1925097.png" alt="" width="" height="" border="0" /></span></span></p>
<p style="text-align: center;"><span style="color: #000000; font-family: Helvetica, Arial, sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: 15px; orphans: 2; text-align: -webkit-auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; background-color: #ffffff; display: inline !important; float: none;"><a href="http://www.oracle.com/technetwork/developer-tools/datamodeler/overview/index.html">http://www.oracle.com/technetwork/developer-tools/datamodeler/overview/index.html</a></span></p>
<p><span style="color: #000000; font-family: Helvetica, Arial, sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: 15px; orphans: 2; text-align: -webkit-auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; background-color: #ffffff; display: inline !important; float: none;">Attention all data modellers - we are pleased to announce the release of SQL Developer Data Modeler 3.3. This release includes a new search, reports can be generated from search results, extended Excel import and export capabilities and more control and flexibility in generating your DDL. </span><span style="color: #000000; font-family: Helvetica, Arial, sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: 15px; orphans: 2; text-align: -webkit-auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; background-color: #ffffff; display: inline !important; float: none;">Here are a few links to get you started:</span></p>
<ul>
<li><span style="color: #000000; font-family: Helvetica, Arial, sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: 15px; orphans: 2; text-align: -webkit-auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; background-color: #ffffff; display: inline !important; float: none;">For downloads see here: </span><a style="margin: 0px; padding: 0px; border: 0px; outline: 0px; font-weight: normal; font-style: normal; font-size: 13px; font-family: Helvetica, Arial, sans-serif; vertical-align: baseline; text-decoration: none; color: #006699; font-variant: normal; letter-spacing: normal; line-height: 15px; orphans: 2; text-align: -webkit-auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; background-color: #ffffff;" href="http://www.linkedin.com/redirect?url=http%3A%2F%2Fwww%2Eoracle%2Ecom%2Ftechnetwork%2Fdeveloper-tools%2Fdatamodeler%2Fdownloads%2Findex%2Ehtml&urlhash=avlw&_t=tracking_anet" rel="nofollow" target="blank">http://www.oracle.com/technetwork/developer-tools/datamodeler/downloads/index.html</a></li>
<li><span style="color: #000000; font-family: Helvetica, Arial, sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: 15px; orphans: 2; text-align: -webkit-auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; background-color: #ffffff; display: inline !important; float: none;">For information about new features see here: </span><a style="margin: 0px; padding: 0px; border: 0px; outline: 0px; font-weight: normal; font-style: normal; font-size: 13px; font-family: Helvetica, Arial, sans-serif; vertical-align: baseline; text-decoration: none; color: #006699; font-variant: normal; letter-spacing: normal; line-height: 15px; orphans: 2; text-align: -webkit-auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; background-color: #ffffff;" href="http://www.linkedin.com/redirect?url=http%3A%2F%2Fwww%2Eoracle%2Ecom%2Ftechnetwork%2Fdeveloper-tools%2Fdatamodeler%2Fdatamodeler-33-newfeatures-1869156%2Ehtml&urlhash=f1At&_t=tracking_anet" rel="nofollow" target="blank">http://www.oracle.com/technetwork/developer-tools/datamodeler/datamodeler-33-newfeatures-1869156.html</a></li>
<li><span style="color: #000000; font-family: Helvetica, Arial, sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: 15px; orphans: 2; text-align: -webkit-auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; background-color: #ffffff; display: inline !important; float: none;">The release notes are available here: </span><a style="margin: 0px; padding: 0px; border: 0px; outline: 0px; font-weight: normal; font-style: normal; font-size: 13px; font-family: Helvetica, Arial, sans-serif; vertical-align: baseline; text-decoration: none; color: #006699; font-variant: normal; letter-spacing: normal; line-height: 15px; orphans: 2; text-align: -webkit-auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; background-color: #ffffff;" href="http://www.linkedin.com/redirect?url=http%3A%2F%2Fwww%2Eoracle%2Ecom%2Ftechnetwork%2Fdeveloper-tools%2Fdatamodeler%2Fdata-modeler-releasenotes33-1869517%2Ehtml&urlhash=U93f&_t=tracking_anet" rel="nofollow" target="blank">http://www.oracle.com/technetwork/developer-tools/datamodeler/data-modeler-releasenotes33-1869517.html</a></li>
<li><span style="color: #000000; font-family: Helvetica, Arial, sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: 15px; orphans: 2; text-align: -webkit-auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; background-color: #ffffff; display: inline !important; float: none;">The documentation can be found here: </span><a style="margin: 0px; padding: 0px; border: 0px; outline: 0px; font-weight: normal; font-style: normal; font-size: 13px; font-family: Helvetica, Arial, sans-serif; vertical-align: baseline; text-decoration: none; color: #006699; font-variant: normal; letter-spacing: normal; line-height: 15px; orphans: 2; text-align: -webkit-auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; background-color: #ffffff;" href="http://www.linkedin.com/redirect?url=http%3A%2F%2Fwww%2Eoracle%2Ecom%2Fpls%2Ftopic%2Flookup%3Fctx%3DE29463_01%26id%3Dhomepage&urlhash=wP65&_t=tracking_anet" rel="nofollow" target="blank">http://www.oracle.com/pls/topic/lookup?ctx=E29463_01&id=homepage</a></li>
</ul>
<p><br style="color: #000000; font-family: Helvetica, Arial, sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: 15px; orphans: 2; text-align: -webkit-auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; background-color: #ffffff;" /><span style="color: #000000; font-family: Helvetica, Arial, sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: 15px; orphans: 2; text-align: -webkit-auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; background-color: #ffffff; display: inline !important; float: none;">For data warehouse data modellers there are some very important new features around logical models, multi-dimensional models and physical models. For example:</span></p>
<ul>
<li><span style="color: #000000; font-family: Helvetica, Arial, sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: 15px; orphans: 2; text-align: -webkit-auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; background-color: #ffffff; display: inline !important; float: none;">Support for surrogate keys during engineering to relational model which can be set on each entity. </span></li>
<li><span style="color: #000000; font-family: Helvetica, Arial, sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: 15px; orphans: 2; text-align: -webkit-auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; background-color: #ffffff; display: inline !important; float: none;">More flexible transformation to relational model with mixed engineering strategies based on “engineer” flag and subtypes setting for each entity in the hierarchy</span></li>
<li><span style="color: #000000; font-family: Helvetica, Arial, sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: 15px; orphans: 2; text-align: -webkit-auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; background-color: #ffffff; display: inline !important; float: none;">Export to “</span><span style="color: #000000; font-family: Helvetica, Arial, sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: 15px; orphans: 2; text-align: -webkit-auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; background-color: #ffffff; display: inline !important; float: none;"><strong>Oracle AW</strong></span><span style="color: #000000; font-family: Helvetica, Arial, sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: 15px; orphans: 2; text-align: -webkit-auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; background-color: #ffffff; display: inline !important; float: none;">” now supports </span><span style="color: #000000; font-family: Helvetica, Arial, sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: 15px; orphans: 2; text-align: -webkit-auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; background-color: #ffffff; display: inline !important; float: none;"><strong>Oracle 11g OLAP</strong></span></li>
<li><span style="color: #000000; font-family: Helvetica, Arial, sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: 15px; orphans: 2; text-align: -webkit-auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; background-color: #ffffff; display: inline !important; float: none;">Support for role playing dimensions in export to Oracle AW.</span></li>
<li><span style="color: #000000; font-family: Helvetica, Arial, sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: 15px; orphans: 2; text-align: -webkit-auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; background-color: #ffffff; display: inline !important; float: none;">Level descriptive attributes can be created without mapping to attribute in logical model.</span></li>
<li><span style="color: #000000; font-family: Helvetica, Arial, sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: 15px; orphans: 2; text-align: -webkit-auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; background-color: #ffffff; display: inline !important; float: none;">Multidimensional model can be bound directly to relational model. </span></li>
<li><span style="color: #000000; font-family: Helvetica, Arial, sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: 15px; orphans: 2; text-align: -webkit-auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; background-color: #ffffff; display: inline !important; float: none;">Support EDITIONING option on views, and support for invisible indexes in Oracle 11g physical model.</span></li>
</ul>
<p><br style="color: #000000; font-family: Helvetica, Arial, sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: 15px; orphans: 2; text-align: -webkit-auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; background-color: #ffffff;" /><span style="color: #000000; font-family: Helvetica, Arial, sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: 15px; orphans: 2; text-align: -webkit-auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; background-color: #ffffff; display: inline !important; float: none;">Lots of great features that will make life a lot easier for data warehouse teams.</span></p>ASQLBaristahttp://www.blogger.com/profile/13350994132294695189noreply@blogger.com0tag:blogger.com,1999:blog-3820031471524503731.post-75750196026925973032013-03-12T12:41:00.004-07:002013-03-12T12:41:26.027-07:00Starbucks 1TB cube in production<div dir="ltr" style="text-align: left;" trbidi="on">
Check out the customer snapshot Oracle has published which describes the success <a href="http://www.starbucks.com/" target="_blank">Starbucks Coffee</a> has achieved by moving their data warehouse to the Exadata platform, leveraging the Oracle Database OLAP Option and Oracle BIEE at the front end. 10,000 users in HQ and across thousands of store locations now have timely accurate and calculation rich information at their fingertips.<br />
<br />
<br />
<h3 style="background-color: white; font-family: Arial, Helvetica, sans-serif; font-size: 14px; list-style: none; margin: 0px 0px 10px; padding: 0px;">
<a href="http://www.oracle.com/us/corporate/customers/customersearch/starbucks-coffee-co-1-exadata-ss-1907993.html" target="_blank">Starbucks Coffee Company Delivers Daily, Actionable Information to Store Managers, Improves Business Insight with High Performance Data Warehouse</a></h3>
<br />
( <span style="font-size: xx-small;">http://www.oracle.com/us/corporate/customers/customersearch/starbucks-coffee-co-1-exadata-ss-1907993.html</span> )<br />
<br />
By delivering extreme performance combined with the architectural simplicity and sophisticated multidimensional calculation power of the in-database analytics of the Database, Starbucks use of OLAP has enabled some outstanding results. Together with the power of other Oracle Database and Exadata benefits such as Partitioning, Hybrid Columnar Compression, Storage Indexes and Flash Memory, Starbucks is able to handle the constant growth in data volumes and end-user demands with ease.<br />
<br />
A great example of the power of the "Disk To Dashboard" capability of Oracle Business Analytics.</div>
Kevin Lancasterhttp://www.blogger.com/profile/06742628997065141834noreply@blogger.com0tag:blogger.com,1999:blog-3820031471524503731.post-35697469591101969982013-01-29T09:35:00.000-08:002013-01-29T11:07:00.902-08:00Webcast: Using Oracle OLAP data in multiple BI clients. Feb. 5 at 9:00 PST<br />
<form action="https://attendee.gotowebinar.com/registration.tmpl" id="registrationForm" method="POST" style="font-family: arial, verdana, helvetica; font-size: 12px;">
Want to learn how to connect multiple BI clients such as SAP BusinessObjects Analysis, Cognos and Microsoft Excel to Oracle OLAP, quickly and easily, using the XMLA version of the MDX Provider for Oracle OLAP? Check out the Oracle webcast on February 5 at 9:00 AM PST. <br />
<br />
You will see how it works and how customers in different industries are using this solution.<br />
<br />
Just <a href="https://attendee.gotowebinar.com/register/5075922165628167424" target="_blank">sign up here</a> and join us on February 5th.</form>
Bud Endresshttp://www.blogger.com/profile/02590149552658898625noreply@blogger.com0tag:blogger.com,1999:blog-3820031471524503731.post-4891627154657791672012-12-10T08:01:00.000-08:002012-12-10T08:01:13.294-08:00Webinar: Using XMLA with Cognos and Oracle OLAP Cubes<b>When: Thursday, Dec 13, 2012 at 9:00am PST / 12:00pm EST / 6:00pm CET.</b><br />
<br />
<b>To attend:</b> <b><span style="font-size: small;"><span style="font-family: Arial,Helvetica,sans-serif;"><a href="https://attendee.gotowebinar.com/register/4201304645265500672" target="_blank">S<span style="font-size: small;">ign up here.</span></a></span></span></b><br />
<b><span style="font-size: small;"><span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-size: small;"> </span> </span></span></b><br />
<br />
If you use a business intelligence tool such as IBM Cognos, Microstrategy or SPA BusinessObjects Analysis that uses XMLA to connect to multidimensional data sources, check out a free webinar by Simba Technologies which offers a "sneak peak" of the Simba XMLA Provider for Oracle OLAP. The Simba XMLA Provider for Oracle OLAP is an XMLA version for the <a href="http://www.simba.com/MDX-Provider-for-Oracle-OLAP.htm" target="_blank">Simba MDX Provider for Oracle OLAP</a>, the gold standard in MDX connectivity to Oracle OLAP. (The Simba MDX Provider for Oracle OLAP allows MDX based clients such as Microsoft Excel PivotTables to query Oracle OLAP cubes. The XMLA version allows clients that use XMLA rather than ODBO to connect to Oracle OLAP.)<br />
<br />
Simba will demonstrate using IBM Cognos using the XMLA provider to query Oracle OLAP cubes. Here's a brief outline of the session.<br />
<br />
See how:<br />
<ul>
<li> Familiar business intelligence applications such as IBM Cognos can connect to an Oracle OLAP cube.</li>
<li>Ad-hoc querying and data analysis can be performed directly in IBM Cognos on your OLAP data.</li>
<li>The most advanced application that responds to XMLA requests available enables users to interactively build reports, drill into details and slice and dice data</li>
<li>Connectivity can be established without the need to install any software on the client machine.<br />Simply connect to the XMLA service and everything works! </li>
</ul>
See you there!<br />
<br />
<br />Bud Endresshttp://www.blogger.com/profile/02590149552658898625noreply@blogger.com0tag:blogger.com,1999:blog-3820031471524503731.post-20621963456574460412012-06-14T13:52:00.000-07:002012-06-14T13:52:25.245-07:00Creating Oracle BI Presentation Layers from Oracle CubesBeginning with Oracle BI 11.1.1.5, the Oracle BI Administration tool has the ability to import Oracle cubes, dimensions and hierarchies into the Physical layer of the Oracle BI repository. This provides an alternative to the Oracle BI plug-in for Analytic Workspace Manager. Each method works somewhat differently and each has certain advantages that might make it the best choice for different situations.<br />
<br />
The Oracle BI plug-in for Analytic Workspace Manager pushes metadata into the Oracle BI repository using UDML code. The result is a ready to use subject area with the Physical, Business Model and Presentation layers fully built for you. It creates hierarchies in the Business Model layer, but it does not add hierarchical columns to the Presentation layer. You can easily add hierarchical columns to the Physical layer by dragging hierarchies from the Business Model layer.<br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhCKPi970YO1VeS4M7qjeBujW054iSIRQeT_kjBzoH6hKYl78JKRHLK20IgsIn1UOvB52SEv0882U5HkrSdtaH1Rzfu7I1fM_WP1aBmE8ETC3P09vXx-o6ZkJ7pm35iOwFi8bdPeWq9CxKp/s1600/hier_column.bmp" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhCKPi970YO1VeS4M7qjeBujW054iSIRQeT_kjBzoH6hKYl78JKRHLK20IgsIn1UOvB52SEv0882U5HkrSdtaH1Rzfu7I1fM_WP1aBmE8ETC3P09vXx-o6ZkJ7pm35iOwFi8bdPeWq9CxKp/s1600/hier_column.bmp" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Hierarchical column in the channel dimension.</td></tr>
</tbody></table>
<br />
The Oracle BI plug-in for Analytic Workspace Manager creates a Physical layer that maps to hierarchy and cube views (these views make the dimensions, hierarchies and cubes look like a star schema to SQL). In the Business Model, it assigns the SUM aggregation operator to measures selected from the cube view. This approach allows SQL to aggregate data above the cube. This is useful for queries that first filter and then aggregate and attribute break outs. For example:<br />
<br />
<ul>
<li>Report by Year where Quarter of Year is 3 and 4. Data at the Year level will be the aggregate of child quarters 3 and 4. </li>
<li>Report by Year, Color and Size where Color and Size are attributes of Item. Oracle will SELECT year, color, size, SUM(sales) .... GROUP BY year, color, size.</li>
</ul>
<br />
In both cases, Oracle BI will get the highest aggregate level data it can from the cube and do some additional aggregation in SQL.<br />
<br />
When cubes are imported using the Oracle BI Administration tool the Physical layer is mapped directly to the cube and dimensions, bypassing the cube and hierarchy views. The SQL created in this case selects from the OLAP_TABLE table function. Oracle BI has optimized this SQL to take advantage of some features in OLAP_TABLE, which offers better query performance with hierarchical columns in some situations.<br />
<br />
When the cube is imported with the Oracle BI Administration tool, aggregation is set to 'External Aggregation'. This will disable the ability to create queries that require aggregation above the cube. This is good in that it prevents users creating queries that might not work as they expect in some cases, but it prevents filter before aggregate and attribute break out queries.<br />
<br />
Also, the import method only creates the Physical layer. It does not create the Business Model and Presentation layers. You do that by dragging the Physical layer to the Business Model layer and then dragging the Business Model to the Presentation layer. That's all quick and easy, but there will be some things you will want to update in the Business Model and Presentation layers. For example, deleting some columns you don't really need and arranging columns in hierarchical order. I've posted the paper <a href="http://www.oracle.com/technetwork/database/options/olap/obiee-cube-presentation-layer-1663816.pdf" target="_blank">Creating an Oracle BI Presentation Layer from Oracle Cubes</a> on OTN. This paper will walk you through the process of refining the Business Model and Presentation layers.<br />
<br />
My recommendation is to try each method, learn about the strengths of each and choose which is best for any particular application. Since all the data comes from the same cube, there's nothing wrong with using both methods.Bud Endresshttp://www.blogger.com/profile/02590149552658898625noreply@blogger.com0tag:blogger.com,1999:blog-3820031471524503731.post-58258105111837540462012-05-22T08:08:00.000-07:002012-05-22T08:08:18.337-07:00Introduction to Oracle OLAP Web Presentation SeriesI've posted a series of three videos introducing Oracle OLAP. This is a great series for people how are interested in learning about what Oracle OLAP is and what it's used for. I suggest starting viewing these in order. Here are the links:<br />
<br />
Oracle OLAP Overview: <a href="http://download.oracle.com/otndocs/products/warehouse/olap/videos/intro_part_1/OLAP_Features_and_Use_Cases_1.html" target="_blank">Part 1 - Architecture</a><br />
Oracle OLAP Overview: <a href="http://download.oracle.com/otndocs/products/warehouse/olap/videos/intro_part_2/OLAP_Features_and_Use_Cases_2.html" target="_blank">Part 2 - Key Features</a><br />
Oracle OLAP Overview: <a href="http://download.oracle.com/otndocs/products/warehouse/olap/videos/intro_part_3/OLAP_Features_and_Use_Cases_3.html" target="_blank">Part 3 - Use Cases</a>Bud Endresshttp://www.blogger.com/profile/02590149552658898625noreply@blogger.com2tag:blogger.com,1999:blog-3820031471524503731.post-6482780330194265482012-05-14T14:22:00.001-07:002012-05-14T14:24:57.752-07:00Creating Custom (Calculated) MembersIf you have ever wanted to report on the total of two or more dimension members you have probably done so using in SQL using SUM ... GROUP BY. <br />
<br />
Let's look at an example using the OLAPTRAIN schema. Suppose you want the total of the states in the northeastern United States. In OLAPTRAIN, that would include Massachusetts, Rhode Island, New York and Pennsylvania..<br />
<br />
If you did this with SQL, it might look something like this.<br />
<br />
<div style="font-family: "Courier New",Courier,monospace;">
<span style="font-size: x-small;">SELECT t.calendar_year_long_descr AS calendar_year,<br /> <b style="color: blue;">g.country_long_description AS country,</b><br /> p.all_products_long_descri AS all_products,<br /> c.all_channels_long_descri AS all_channels,<br /> <span style="color: blue;">SUM(f.sales)</span><br />FROM time_calendar_view t,<br /> product_standard_view p,<br /> geography_regional_view g,<br /> channel_sales_channel_view c,<br /> sales_cube_view f<br />WHERE t.dim_key = f.time<br />AND p.dim_key = f.product<br />AND g.dim_key = f.geography<br />AND c.dim_key = f.channel<br />AND t.level_name = 'CALENDAR_YEAR'<br />AND p.level_name = 'ALL_PRODUCTS'<br />AND c.level_name = 'ALL_CHANNELS'<br /><b style="color: blue;">AND g.level_name = 'STATE_PROVINCE'</b><br style="color: blue;" /><b style="color: blue;">AND g.state_province_long_desc IN ('Massachusetts','Rhode Island','New York', 'Pennsylvania')</b><br />AND t.calendar_year_long_descr = 'CY2009'<br /><b style="color: blue;">AND g.country_long_description = 'United States'</b><br /><b style="color: blue;">GROUP BY t.calendar_year_long_descr,<br /> g.country_long_description,<br /> p.all_products_long_descri,<br /> c.all_channels_long_descri;</b></span></div>
<br />
And the query would return as<br />
<div style="font-family: "Courier New",Courier,monospace;">
<br /></div>
<div style="font-family: "Courier New",Courier,monospace;">
<span style="font-size: x-small;">CY2009 <b style="color: blue;">United States</b> All Products All Channels 10828869.07 </span></div>
<br />
A different way to approach this is to create a custom member that is the aggregate of other members. In this case, the custom member is added to the dimension and can be used just like any other dimension member. The only real difference is that a custom member is not within a hierarchy and does not belong to a level. The advantages are that the custom member is available to all users (unless you control access, more on that later), they work with all of the cube's aggregation rules (e.g., first, last, hierarchical weighted average and so on), they work seamlessly with calculated measures and they are available in all tools (e.g., Excel PivotTables).<br />
<br />
Custom aggregates are created using the dbms_cube.import program. Note that the dimension keys are numeric in OLAPTRAIN. (Sorry for posting this sample as an image ... blogger wasn't happy about displaying XML. To view the full example option the image in a new tab or window).<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhxiFf4lpzyC9yoEMMn-rfAxn2Usx9OmC-8elZrHjaZm95XDHKe2oGlScLKiA4XO3Q3fOEOoFyz8emkSvpniUNA4KFlIFnBWbVwhWt2gF1jQ9iVwOBTrFG5Cng8GJZSlL_5wv45KJN7Ffvx/s1600/cust_member.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhxiFf4lpzyC9yoEMMn-rfAxn2Usx9OmC-8elZrHjaZm95XDHKe2oGlScLKiA4XO3Q3fOEOoFyz8emkSvpniUNA4KFlIFnBWbVwhWt2gF1jQ9iVwOBTrFG5Cng8GJZSlL_5wv45KJN7Ffvx/s1600/cust_member.png" /></a></div>
<br />
<div style="font-family: "Courier New",Courier,monospace;">
<br /></div>
<div style="font-family: "Courier New",Courier,monospace;">
<span style="font-size: x-small;"><span style="font-size: small;"><span style="font-family: Times,"Times New Roman",serif;">I can view custom members in the dimension or hierarchy view with a query such as this:</span></span></span></div>
<div style="font-family: "Courier New",Courier,monospace;">
<br /></div>
<div style="font-family: "Courier New",Courier,monospace;">
<span style="font-size: x-small;">SELECT * FROM geography_view WHERE member_type = 'C';</span></div>
<div style="font-family: "Courier New",Courier,monospace;">
<br /></div>
<div style="font-family: "Courier New",Courier,monospace;">
<span style="font-size: x-small;"><span style="font-size: small;"><span style="font-family: Times,"Times New Roman",serif;">I can query the cube view with a simplified query (note the lack of SUM ... GROUP BY).</span></span></span></div>
<div style="font-family: "Courier New",Courier,monospace;">
<br /></div>
<div style="font-family: "Courier New",Courier,monospace;">
<span style="font-size: x-small;">SELECT t.long_description AS time,<br /> g.long_description AS geog,<br /> p.long_description AS product,<br /> c.long_description AS channel,<br /> f.sales<br />FROM time_calendar_view t,<br /> product_standard_view p,<br /> geography_regional_view g,<br /> channel_sales_channel_view c,<br /> sales_cube_view f<br />WHERE t.dim_key = f.time<br />AND p.dim_key = f.product<br />AND g.dim_key = f.geography<br />AND c.dim_key = f.channel<br />AND t.level_name = 'CALENDAR_YEAR'<br />AND p.level_name = 'ALL_PRODUCTS'<br />AND c.level_name = 'ALL_CHANNELS'<br />AND <b><span style="color: blue;">g.long_description = 'Northeast US'</span></b><br />AND t.calendar_year_long_descr = 'CY2009';</span></div>
<div style="font-family: "Courier New",Courier,monospace;">
<br /></div>
<div style="font-family: inherit;">
<span style="font-size: small;">And the query returns Northeast US rather than a modified version of United States.</span></div>
<div style="font-family: "Courier New",Courier,monospace;">
<br /></div>
<div style="font-family: "Courier New",Courier,monospace;">
<span style="font-size: x-small;">CY2009 <b style="color: blue;">Northeast US</b> All Products All Channels 10828869.07 </span></div>
<div style="font-family: "Courier New",Courier,monospace;">
<br /></div>
<div style="font-family: "Courier New",Courier,monospace;">
<span style="font-size: x-small;"><span style="font-size: small;"><span style="font-family: Times,"Times New Roman",serif;">If it is appropriate for the custom members to be visable to all users, there's nothing else to do. If you want to control access to custom member, Virtual Private Database is probably a good choice. </span></span></span></div>
<div style="font-family: "Courier New",Courier,monospace;">
<span style="font-size: x-small;"><span style="font-size: small;"><span style="font-family: Times,"Times New Roman",serif;"><br /></span></span></span></div>
<div style="font-family: "Courier New",Courier,monospace;">
<span style="font-size: x-small;"><span style="font-size: small;"><span style="font-family: Times,"Times New Roman",serif;">If you would like a sample script that creates a few custom members and sets up a VPD policy to control access, send me an email at william.endress@oracle.com.</span></span></span></div>
<div style="font-family: "Courier New",Courier,monospace;">
<br /></div>
<div style="font-family: "Courier New",Courier,monospace;">
</div>
<br />
<br />
<br />
<br />
<br />
<br />Bud Endresshttp://www.blogger.com/profile/02590149552658898625noreply@blogger.com0tag:blogger.com,1999:blog-3820031471524503731.post-67045586335832043822012-04-13T08:57:00.005-07:002012-04-13T11:02:58.041-07:00Fine Tuning Incremental Updates using LOAD PRUNEIf you are like most people, you probably use the LOAD_AND_AGGREGATE cube script that is automatically created by Analytic Workspace Manager. Fine tuning the update process simply involves filtering the fact table for new or changed rows.<br /><br />With a little bit of effort, you can improve update times by writing your own cube processing script. You can also use MV log tables to automatically captured changes made to the fact table and use them as the data sources to cube updates.<br /><br />AWM defines and makes the LOAD_AND_AGGREGATE script the default script of the cube. If you don’t specify a different script, LOAD_AND_AGGREGATE is automatically used as shown in the following example (note that the script references the OLAPTRAIN.SALES_CUBE but does not including the USING clause).<br /><br /><span style="font-family:courier new;">BEGIN</span><br /><span style="font-family:courier new;"> DBMS_CUBE.BUILD('OLAPTRAIN.SALES_CUBE','C',false,4,true,true,false);</span><br /><span style="font-family:courier new;">END;</span><br /><span style="font-family:courier new;">/</span><br /><br />This script will run the LOAD PARALLEL and SOLVE PARALLEL commands. What this means is that for each partition, the database will LOAD data from the fact table/view and then SOLVE (aggregate) data. If you have specified a value for parallel that is greater than 1, partitions will be processed in parallel (in the example above, 4 processes). AWM also provides the ability to set the refresh method (C, or complete, in the above example).<br /><br />LOAD_AND_AGGREGATE is a good choice for a full build, but it might not be the best choice for an incremental update. If you are simply updating the cube with changes within a few recent partitions (e.g., yesterday or this month), the LOAD PRUNE command is probably better than LOAD PARALLEL.<br /><br />LOAD PRUNE will first query the fact table or view to first determine which partition will have new data using a SELECT DISTINCT. It will then only generate LOAD commands for those partitions that will have records loaded into them.<br />Let’s run through an update scenario. Make the following assumptions:<br /><br />* The time dimension has months for 2008 through 2012 and the cube is partitioned by month. The cube will have 60 partitions.<br /><br />* You have loaded data into the cube for January 2008 through March 2012.<br /><br />* It’s now time to load data for April 2012. This data has been inserted into the fact table.<br /><br />* You have mapped the cube to a view. For the April 2012 update, you have added a filter to the view so that it returns data only for April.<br /><br />If you use the LOAD_AND_AGGREGATE script and choose the FAST SOLVE refresh method, the database will really to the following:<br /><br /><span style="font-family:courier new;">BEGIN</span><br /><span style="font-family:courier new;"> DBMS_CUBE.BUILD('OLAPTRAIN.SALES_CUBE USING (LOAD PARALLEL, SOLVE PARALLEL)','S',false,4,true,true,false);</span><br /><span style="font-family:courier new;">END;</span><br /><span style="font-family:courier new;">/</span><br /><br />With LOAD PARALLEL, the database will process the LOAD command for each partition (all 60). Since it’s selecting from a view that’s filtered out all but April 2012, 59 partitions will have no new or changed data. Although it doesn’t take a long time to load 0 rows and figure out that a SOLVE is not required, it still adds up if there are a lot of partitions.<br /><br />With LOAD PRUNE, the database will determine that a LOAD is only required for April 2012. The LOAD step is skipped for all other partitions. While you will still see the SOLVE for all partitions, it doesn’t really do any work because no rows were loaded into the partition. An example using LOAD PRUNE follows.<br /><br /><span style="font-family:courier new;">BEGIN</span><br /><span style="font-family:courier new;"> DBMS_CUBE.BUILD('OLAPTRAIN.SALES_CUBE USING (LOAD PRUNE, SOLVE PARALLEL)','S',false,2,true,true,false);</span><br /><span style="font-family:courier new;">END;</span><br /><span style="font-family:courier new;">/</span><br /><br />If you would like a script that walked through a complete example using the OLAPTRAIN schema, including the use of an MV log table to automatically capture changes to the fact table, send me an email william.endress@oracle.com with a link to this posting.Bud Endresshttp://www.blogger.com/profile/02590149552658898625noreply@blogger.com4tag:blogger.com,1999:blog-3820031471524503731.post-643993795229067882012-04-03T09:42:00.005-07:002012-05-22T08:16:22.551-07:00Excel and OLAP: ODBC vs. MDXA question that I often get is "what is the difference between using ODBC and the MDX Provider for Oracle OLAP (from Simba Technologies) to query Oracle cubes"? Given that the Oracle cube is easily queried with SQL, it's a reasonable question.<br />
The answer really boils down to leveraging meta data and automatic query generation.<br />
With ODBC, it's up to the Excel user to write a SQL query to fetch data from the cube. Data can be returned in tabular format or a pivot table. When the data is viewed in a pivot table Excel will aggregate data, sometimes with unexpected results. For example Excel might choose to aggregate a measure such as Sales with COUNT or might try to SUM a measure such as Sales YTD Percent Change. Neither make any sense. It's up to the user to get it right.<br />
With the MDX Provider, Excel understands what all the columns mean. It understands dimensions, hierarchies and levels. It's understand the difference between a key and a label. It knows what a measure is. It allows the server to calculate the data. Query generation is automatic. Business users just choose hierarchies and measures and the MDX Provider does the rest.<br />
Here's a list of some of the advantages of using the MDX Provider for Oracle OLAP as compared to using ODBC and writing your own SQL.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhHGLT6sigpOkEjs5oVObSRb8b5gJ7PaGaRlX7pFH5cm4aYkjLElqa-4eLOhjLTgH8XJwrhhAxMQHTS00yEu9Mc7z7BnsmY_8aNOvlTt03owpk3-nS16o9VNYcaoFtKL7t9QvNNG5bb-emo/s1600/odbc_vs_mdx.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="291" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhHGLT6sigpOkEjs5oVObSRb8b5gJ7PaGaRlX7pFH5cm4aYkjLElqa-4eLOhjLTgH8XJwrhhAxMQHTS00yEu9Mc7z7BnsmY_8aNOvlTt03owpk3-nS16o9VNYcaoFtKL7t9QvNNG5bb-emo/s400/odbc_vs_mdx.png" width="400" /></a></div>
<br />
<br />Bud Endresshttp://www.blogger.com/profile/02590149552658898625noreply@blogger.com0tag:blogger.com,1999:blog-3820031471524503731.post-84384160857200375282012-04-02T06:39:00.002-07:002012-04-02T06:44:20.439-07:00Oracle OLAP Exadata Performance DemonstrationFor a great paper on Oracle OLAP running on Exadata, see:<br /><br /><a href="http://www.oracle.com/technetwork/database/options/olap/olap-exadata-x2-2-performance-1429042.pdf">http://www.oracle.com/technetwork/database/options/olap/olap-exadata-x2-2-performance-1429042.pdf</a><br /><br />The Executive Overview section of this paper provides an introduction:<br /><br />This paper describes a performance demonstration of the OLAP Option to the Oracle Database running on an X2-2 Exadata Database Machine half rack. It shows how Oracle OLAP cubes can be used to enhance the performance and analytic content of the data warehouse and business intelligence solutions, supporting a demanding user community with ultrafast query and rich analytic content.<br /><br />The demonstration represents users of a business intelligence application using SQL to query an Oracle OLAP cube that has been enhanced with a variety of analytic measures. The cube contains data loaded from a fact table with more than 1 billion rows.<br /><br />Utilizing Exadata features such as Smart Flash Cache, Oracle Database supported a community of 50 concurrent users querying the cube with queries that are typical of those executed from a business intelligence tool such as Oracle Business Intelligence Enterprise Edition.<br /><br />With each user querying the database non-stop (without waits between queries) with median query times ranged from .03 to .58 seconds, average query times ranged from .26 to 2.32 seconds, and 95 percent of queries returned in 1.5 to 5.5 seconds, depending on the type of query.<br /><br />Query performance can be attributed to highly optimized data types and Exadata Smart Flash Cache. Cubes are designed for fast access to random data points, using features such as array-based storage, cost-based aggregation, and joined cube scans. Exadata Smart Flash Cache contributes significantly to cube query performance, virtually eliminating IO wait for the high volume, random IO typically seen with cube queries.Bud Endresshttp://www.blogger.com/profile/02590149552658898625noreply@blogger.com1tag:blogger.com,1999:blog-3820031471524503731.post-87877363478095894952011-01-17T07:06:00.000-08:002012-11-14T08:48:33.337-08:00Script for Time Dimension TableNote - This blog post was updated on Nov. 14, 2012 with a new script. This as been simplified a bit and includes half year. <br />
<br />
One of the more common requests I get is a script for creating time dimension tables for Oracle OLAP. The following script will create a time dimension table for a standard calendar. It starts by creating a table with dimension members and labels. The second part of the script fills in end date and time span attributes. The section that creates end date and time span can be easily adapted for completing other calendars (e.g., fiscal) where the members have already been filled in.<br />
<br />
<!--[if gte mso 9]><xml> <w:worddocument> <w:view>Normal</w:View> <w:zoom>0</w:Zoom> <w:trackmoves/> <w:trackformatting/> <w:punctuationkerning/> <w:validateagainstschemas/> <w:saveifxmlinvalid>false</w:SaveIfXMLInvalid> <w:ignoremixedcontent>false</w:IgnoreMixedContent> <w:alwaysshowplaceholdertext>false</w:AlwaysShowPlaceholderText> <w:donotpromoteqf/> <w:lidthemeother>EN-US</w:LidThemeOther> <w:lidthemeasian>X-NONE</w:LidThemeAsian> <w:lidthemecomplexscript>X-NONE</w:LidThemeComplexScript> <w:compatibility> <w:breakwrappedtables/> <w:snaptogridincell/> <w:wraptextwithpunct/> <w:useasianbreakrules/> <w:dontgrowautofit/> <w:splitpgbreakandparamark/> <w:dontvertaligncellwithsp/> <w:dontbreakconstrainedforcedtables/> <w:dontvertalignintxbx/> <w:word11kerningpairs/> <w:cachedcolbalance/> </w:Compatibility> <w:browserlevel>MicrosoftInternetExplorer4</w:BrowserLevel> <m:mathpr> <m:mathfont val="Cambria Math"> <m:brkbin val="before"> <m:brkbinsub val="--"> <m:smallfrac val="off"> <m:dispdef/> <m:lmargin val="0"> <m:rmargin val="0"> <m:defjc val="centerGroup"> <m:wrapindent val="1440"> <m:intlim val="subSup"> <m:narylim val="undOvr"> </m:mathPr></w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:latentstyles deflockedstate="false" defunhidewhenused="true" defsemihidden="true" defqformat="false" defpriority="99" latentstylecount="267"> <w:lsdexception locked="false" priority="0" semihidden="false" unhidewhenused="false" qformat="true" name="Normal"> <w:lsdexception locked="false" priority="9" semihidden="false" unhidewhenused="false" qformat="true" name="heading 1"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 2"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 3"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 4"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 5"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 6"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 7"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 8"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 9"> <w:lsdexception locked="false" priority="39" name="toc 1"> <w:lsdexception locked="false" priority="39" name="toc 2"> <w:lsdexception locked="false" priority="39" name="toc 3"> <w:lsdexception locked="false" priority="39" name="toc 4"> <w:lsdexception locked="false" priority="39" name="toc 5"> <w:lsdexception locked="false" priority="39" name="toc 6"> <w:lsdexception locked="false" priority="39" name="toc 7"> <w:lsdexception locked="false" priority="39" name="toc 8"> <w:lsdexception locked="false" priority="39" name="toc 9"> <w:lsdexception locked="false" priority="35" qformat="true" name="caption"> <w:lsdexception locked="false" priority="10" semihidden="false" unhidewhenused="false" qformat="true" name="Title"> <w:lsdexception locked="false" priority="1" name="Default Paragraph Font"> <w:lsdexception locked="false" priority="11" semihidden="false" unhidewhenused="false" qformat="true" name="Subtitle"> <w:lsdexception locked="false" priority="22" semihidden="false" unhidewhenused="false" qformat="true" name="Strong"> <w:lsdexception locked="false" priority="20" semihidden="false" unhidewhenused="false" qformat="true" name="Emphasis"> <w:lsdexception locked="false" priority="59" semihidden="false" unhidewhenused="false" name="Table Grid"> <w:lsdexception locked="false" unhidewhenused="false" name="Placeholder Text"> <w:lsdexception locked="false" priority="1" semihidden="false" unhidewhenused="false" qformat="true" name="No Spacing"> <w:lsdexception locked="false" priority="60" semihidden="false" unhidewhenused="false" name="Light Shading"> <w:lsdexception locked="false" priority="61" semihidden="false" unhidewhenused="false" name="Light List"> <w:lsdexception locked="false" priority="62" semihidden="false" unhidewhenused="false" name="Light Grid"> <w:lsdexception locked="false" priority="63" semihidden="false" unhidewhenused="false" name="Medium Shading 1"> <w:lsdexception locked="false" priority="64" semihidden="false" unhidewhenused="false" name="Medium Shading 2"> <w:lsdexception locked="false" priority="65" semihidden="false" unhidewhenused="false" name="Medium List 1"> <w:lsdexception locked="false" priority="66" semihidden="false" unhidewhenused="false" name="Medium List 2"> <w:lsdexception locked="false" priority="67" semihidden="false" unhidewhenused="false" name="Medium Grid 1"> <w:lsdexception locked="false" priority="68" semihidden="false" unhidewhenused="false" name="Medium Grid 2"> <w:lsdexception locked="false" priority="69" semihidden="false" unhidewhenused="false" name="Medium Grid 3"> <w:lsdexception locked="false" priority="70" semihidden="false" unhidewhenused="false" name="Dark List"> <w:lsdexception locked="false" priority="71" semihidden="false" unhidewhenused="false" name="Colorful Shading"> <w:lsdexception locked="false" priority="72" semihidden="false" unhidewhenused="false" name="Colorful List"> <w:lsdexception locked="false" priority="73" semihidden="false" unhidewhenused="false" name="Colorful Grid"> <w:lsdexception locked="false" priority="60" semihidden="false" unhidewhenused="false" name="Light Shading Accent 1"> <w:lsdexception locked="false" priority="61" semihidden="false" unhidewhenused="false" name="Light List Accent 1"> <w:lsdexception locked="false" priority="62" semihidden="false" unhidewhenused="false" name="Light Grid Accent 1"> <w:lsdexception locked="false" priority="63" semihidden="false" unhidewhenused="false" name="Medium Shading 1 Accent 1"> <w:lsdexception locked="false" priority="64" semihidden="false" unhidewhenused="false" name="Medium Shading 2 Accent 1"> <w:lsdexception locked="false" priority="65" semihidden="false" unhidewhenused="false" name="Medium List 1 Accent 1"> <w:lsdexception locked="false" unhidewhenused="false" name="Revision"> <w:lsdexception locked="false" priority="34" semihidden="false" unhidewhenused="false" qformat="true" name="List Paragraph"> <w:lsdexception locked="false" priority="29" semihidden="false" unhidewhenused="false" qformat="true" name="Quote"> <w:lsdexception locked="false" priority="30" semihidden="false" unhidewhenused="false" qformat="true" name="Intense Quote"> <w:lsdexception locked="false" priority="66" semihidden="false" unhidewhenused="false" name="Medium List 2 Accent 1"> <w:lsdexception locked="false" priority="67" semihidden="false" unhidewhenused="false" name="Medium Grid 1 Accent 1"> <w:lsdexception locked="false" priority="68" semihidden="false" unhidewhenused="false" name="Medium Grid 2 Accent 1"> <w:lsdexception locked="false" priority="69" semihidden="false" unhidewhenused="false" name="Medium Grid 3 Accent 1"> <w:lsdexception locked="false" priority="70" semihidden="false" unhidewhenused="false" name="Dark List Accent 1"> <w:lsdexception locked="false" priority="71" semihidden="false" unhidewhenused="false" name="Colorful Shading Accent 1"> <w:lsdexception locked="false" priority="72" semihidden="false" unhidewhenused="false" name="Colorful List Accent 1"> <w:lsdexception locked="false" priority="73" semihidden="false" unhidewhenused="false" name="Colorful Grid Accent 1"> <w:lsdexception locked="false" priority="60" semihidden="false" unhidewhenused="false" name="Light Shading Accent 2"> <w:lsdexception locked="false" priority="61" semihidden="false" unhidewhenused="false" name="Light List Accent 2"> <w:lsdexception locked="false" priority="62" semihidden="false" unhidewhenused="false" name="Light Grid Accent 2"> <w:lsdexception locked="false" priority="63" semihidden="false" unhidewhenused="false" name="Medium Shading 1 Accent 2"> <w:lsdexception locked="false" priority="64" semihidden="false" unhidewhenused="false" name="Medium Shading 2 Accent 2"> <w:lsdexception locked="false" priority="65" semihidden="false" unhidewhenused="false" name="Medium List 1 Accent 2"> <w:lsdexception locked="false" priority="66" semihidden="false" unhidewhenused="false" name="Medium List 2 Accent 2"> <w:lsdexception locked="false" priority="67" semihidden="false" unhidewhenused="false" name="Medium Grid 1 Accent 2"> <w:lsdexception locked="false" priority="68" semihidden="false" unhidewhenused="false" name="Medium Grid 2 Accent 2"> <w:lsdexception locked="false" priority="69" semihidden="false" unhidewhenused="false" name="Medium Grid 3 Accent 2"> <w:lsdexception locked="false" priority="70" semihidden="false" unhidewhenused="false" name="Dark List Accent 2"> <w:lsdexception locked="false" priority="71" semihidden="false" unhidewhenused="false" name="Colorful Shading Accent 2"> <w:lsdexception locked="false" priority="72" semihidden="false" unhidewhenused="false" name="Colorful List Accent 2"> <w:lsdexception locked="false" priority="73" semihidden="false" unhidewhenused="false" name="Colorful Grid Accent 2"> <w:lsdexception locked="false" priority="60" semihidden="false" unhidewhenused="false" name="Light Shading Accent 3"> <w:lsdexception locked="false" priority="61" semihidden="false" unhidewhenused="false" name="Light List Accent 3"> <w:lsdexception locked="false" priority="62" semihidden="false" unhidewhenused="false" name="Light Grid Accent 3"> <w:lsdexception locked="false" priority="63" semihidden="false" unhidewhenused="false" name="Medium Shading 1 Accent 3"> <w:lsdexception locked="false" priority="64" semihidden="false" unhidewhenused="false" name="Medium Shading 2 Accent 3"> <w:lsdexception locked="false" priority="65" semihidden="false" unhidewhenused="false" name="Medium List 1 Accent 3"> <w:lsdexception locked="false" priority="66" semihidden="false" unhidewhenused="false" name="Medium List 2 Accent 3"> <w:lsdexception locked="false" priority="67" semihidden="false" unhidewhenused="false" name="Medium Grid 1 Accent 3"> <w:lsdexception locked="false" priority="68" semihidden="false" unhidewhenused="false" name="Medium Grid 2 Accent 3"> <w:lsdexception locked="false" priority="69" semihidden="false" unhidewhenused="false" name="Medium Grid 3 Accent 3"> <w:lsdexception locked="false" priority="70" semihidden="false" unhidewhenused="false" name="Dark List Accent 3"> <w:lsdexception locked="false" priority="71" semihidden="false" unhidewhenused="false" name="Colorful Shading Accent 3"> <w:lsdexception locked="false" priority="72" semihidden="false" unhidewhenused="false" name="Colorful List Accent 3"> <w:lsdexception locked="false" priority="73" semihidden="false" unhidewhenused="false" name="Colorful Grid Accent 3"> <w:lsdexception locked="false" priority="60" semihidden="false" unhidewhenused="false" name="Light Shading Accent 4"> <w:lsdexception locked="false" priority="61" semihidden="false" unhidewhenused="false" name="Light List Accent 4"> <w:lsdexception locked="false" priority="62" semihidden="false" unhidewhenused="false" name="Light Grid Accent 4"> <w:lsdexception locked="false" priority="63" semihidden="false" unhidewhenused="false" name="Medium Shading 1 Accent 4"> <w:lsdexception locked="false" priority="64" semihidden="false" unhidewhenused="false" name="Medium Shading 2 Accent 4"> <w:lsdexception locked="false" priority="65" semihidden="false" unhidewhenused="false" name="Medium List 1 Accent 4"> <w:lsdexception locked="false" priority="66" semihidden="false" unhidewhenused="false" name="Medium List 2 Accent 4"> <w:lsdexception locked="false" priority="67" semihidden="false" unhidewhenused="false" name="Medium Grid 1 Accent 4"> <w:lsdexception locked="false" priority="68" semihidden="false" unhidewhenused="false" name="Medium Grid 2 Accent 4"> <w:lsdexception locked="false" priority="69" semihidden="false" unhidewhenused="false" name="Medium Grid 3 Accent 4"> <w:lsdexception locked="false" priority="70" semihidden="false" unhidewhenused="false" name="Dark List Accent 4"> <w:lsdexception locked="false" priority="71" semihidden="false" unhidewhenused="false" name="Colorful Shading Accent 4"> <w:lsdexception locked="false" priority="72" semihidden="false" unhidewhenused="false" name="Colorful List Accent 4"> <w:lsdexception locked="false" priority="73" semihidden="false" unhidewhenused="false" name="Colorful Grid Accent 4"> <w:lsdexception locked="false" priority="60" semihidden="false" unhidewhenused="false" name="Light Shading Accent 5"> <w:lsdexception locked="false" priority="61" semihidden="false" unhidewhenused="false" name="Light List Accent 5"> <w:lsdexception locked="false" priority="62" semihidden="false" unhidewhenused="false" name="Light Grid Accent 5"> <w:lsdexception locked="false" priority="63" semihidden="false" unhidewhenused="false" name="Medium Shading 1 Accent 5"> <w:lsdexception locked="false" priority="64" semihidden="false" unhidewhenused="false" name="Medium Shading 2 Accent 5"> <w:lsdexception locked="false" priority="65" semihidden="false" unhidewhenused="false" name="Medium List 1 Accent 5"> <w:lsdexception locked="false" priority="66" semihidden="false" unhidewhenused="false" name="Medium List 2 Accent 5"> <w:lsdexception locked="false" priority="67" semihidden="false" unhidewhenused="false" name="Medium Grid 1 Accent 5"> <w:lsdexception locked="false" priority="68" semihidden="false" unhidewhenused="false" name="Medium Grid 2 Accent 5"> <w:lsdexception locked="false" priority="69" semihidden="false" unhidewhenused="false" name="Medium Grid 3 Accent 5"> <w:lsdexception locked="false" priority="70" semihidden="false" unhidewhenused="false" name="Dark List Accent 5"> <w:lsdexception locked="false" priority="71" semihidden="false" unhidewhenused="false" name="Colorful Shading Accent 5"> <w:lsdexception locked="false" priority="72" semihidden="false" unhidewhenused="false" name="Colorful List Accent 5"> <w:lsdexception locked="false" priority="73" semihidden="false" unhidewhenused="false" name="Colorful Grid Accent 5"> <w:lsdexception locked="false" priority="60" semihidden="false" unhidewhenused="false" name="Light Shading Accent 6"> <w:lsdexception locked="false" priority="61" semihidden="false" unhidewhenused="false" name="Light List Accent 6"> <w:lsdexception locked="false" priority="62" semihidden="false" unhidewhenused="false" name="Light Grid Accent 6"> <w:lsdexception locked="false" priority="63" semihidden="false" unhidewhenused="false" name="Medium Shading 1 Accent 6"> <w:lsdexception locked="false" priority="64" semihidden="false" unhidewhenused="false" name="Medium Shading 2 Accent 6"> <w:lsdexception locked="false" priority="65" semihidden="false" unhidewhenused="false" name="Medium List 1 Accent 6"> <w:lsdexception locked="false" priority="66" semihidden="false" unhidewhenused="false" name="Medium List 2 Accent 6"> <w:lsdexception locked="false" priority="67" semihidden="false" unhidewhenused="false" name="Medium Grid 1 Accent 6"> <w:lsdexception locked="false" priority="68" semihidden="false" unhidewhenused="false" name="Medium Grid 2 Accent 6"> <w:lsdexception locked="false" priority="69" semihidden="false" unhidewhenused="false" name="Medium Grid 3 Accent 6"> <w:lsdexception locked="false" priority="70" semihidden="false" unhidewhenused="false" name="Dark List Accent 6"> <w:lsdexception locked="false" priority="71" semihidden="false" unhidewhenused="false" name="Colorful Shading Accent 6"> <w:lsdexception locked="false" priority="72" semihidden="false" unhidewhenused="false" name="Colorful List Accent 6"> <w:lsdexception locked="false" priority="73" semihidden="false" unhidewhenused="false" name="Colorful Grid Accent 6"> <w:lsdexception locked="false" priority="19" semihidden="false" unhidewhenused="false" qformat="true" name="Subtle Emphasis"> <w:lsdexception locked="false" priority="21" semihidden="false" unhidewhenused="false" qformat="true" name="Intense Emphasis"> <w:lsdexception locked="false" priority="31" semihidden="false" unhidewhenused="false" qformat="true" name="Subtle Reference"> <w:lsdexception locked="false" priority="32" semihidden="false" unhidewhenused="false" qformat="true" name="Intense Reference"> <w:lsdexception locked="false" priority="33" semihidden="false" unhidewhenused="false" qformat="true" name="Book Title"> <w:lsdexception locked="false" priority="37" name="Bibliography"> <w:lsdexception locked="false" priority="39" qformat="true" name="TOC Heading"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin-top:0in; mso-para-margin-right:0in; mso-para-margin-bottom:10.0pt; mso-para-margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:"Times New Roman"; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin;} </style> <![endif]--> <br />
<div class="MsoNormalCxSpMiddle">
<span style="font-family: "Courier New"; font-size: 8pt; line-height: 115%;">--<br />-- Create time dimension table for a standard calendar year (day, month,<br />-- quarter, half year and year).<br />--<br />-- Drop table.<br />--<br />--DROP TABLE time_calendar_dim;<br />--<br />-- Create time dimension table for calendar year.<br />--<br />-- First day if the next day after TO_DATE('31/12/2010','DD/MM/YYYY').<br />-- Number of days is set in CONNECT BY level <= 365.<br />--<br />-- Values for end date and time span attributes are place holders. They need<br />-- to be filled in correctly later in this script.<br />--<br />CREATE TABLE time_calendar_dim AS<br />WITH base_calendar AS<br /> (SELECT CurrDate AS Day_ID,<br /> 1 AS Day_Time_Span,<br /> CurrDate AS Day_End_Date,<br /> TO_CHAR(CurrDate,'Day') AS Week_Day_Full,<br /> TO_CHAR(CurrDate,'DY') AS Week_Day_Short,<br /> TO_NUMBER(TRIM(leading '0'<br /> FROM TO_CHAR(CurrDate,'D'))) AS Day_Num_of_Week,<br /> TO_NUMBER(TRIM(leading '0'<br /> FROM TO_CHAR(CurrDate,'DD'))) AS Day_Num_of_Month,<br /> TO_NUMBER(TRIM(leading '0'<br /> FROM TO_CHAR(CurrDate,'DDD'))) AS Day_Num_of_Year,<br /> UPPER(TO_CHAR(CurrDate,'Mon')<br /> || '-'<br /> || TO_CHAR(CurrDate,'YYYY')) AS Month_ID,<br /> TO_CHAR(CurrDate,'Mon')<br /> || ' '<br /> || TO_CHAR(CurrDate,'YYYY') AS Month_Short_Desc,<br /> RTRIM(TO_CHAR(CurrDate,'Month'))<br /> || ' '<br /> || TO_CHAR(CurrDate,'YYYY') AS Month_Long_Desc,<br /> TO_CHAR(CurrDate,'Mon') AS Month_Short,<br /> TO_CHAR(CurrDate,'Month') AS Month_Long,<br /> TO_NUMBER(TRIM(leading '0'<br /> FROM TO_CHAR(CurrDate,'MM'))) AS Month_Num_of_Year,<br /> 'Q'<br /> || UPPER(TO_CHAR(CurrDate,'Q')<br /> || '-'<br /> || TO_CHAR(CurrDate,'YYYY')) AS Quarter_ID,<br /> TO_NUMBER(TO_CHAR(CurrDate,'Q')) AS Quarter_Num_of_Year,<br /> CASE<br /> WHEN TO_NUMBER(TO_CHAR(CurrDate,'Q')) <= 2<br /> THEN 1<br /> ELSE 2<br /> END AS half_num_of_year,<br /> CASE<br /> WHEN TO_NUMBER(TO_CHAR(CurrDate,'Q')) <= 2<br /> THEN 'H'<br /> || 1<br /> || '-'<br /> || TO_CHAR(CurrDate,'YYYY')<br /> ELSE 'H'<br /> || 2<br /> || '-'<br /> || TO_CHAR(CurrDate,'YYYY')<br /> END AS half_of_year_id,<br /> TO_CHAR(CurrDate,'YYYY') AS Year_ID<br /> FROM<br /> (SELECT level n,<br /> -- Calendar starts at the day after this date.<br /> TO_DATE('31/12/2010','DD/MM/YYYY') + NUMTODSINTERVAL(level,'DAY') CurrDate<br /> FROM dual<br /> -- Change for the number of days to be added to the table.<br /> CONNECT BY level <= 365<br /> )<br /> )<br />SELECT day_id,<br /> day_time_span,<br /> day_end_date,<br /> week_day_full,<br /> week_day_short,<br /> day_num_of_week,<br /> day_num_of_month,<br /> day_num_of_year,<br /> month_id,<br /> COUNT(*) OVER (PARTITION BY month_id) AS Month_Time_Span,<br /> MAX(day_id) OVER (PARTITION BY month_id) AS Month_End_Date,<br /> month_short_desc,<br /> month_long_desc,<br /> month_short,<br /> month_long,<br /> month_num_of_year,<br /> quarter_id,<br /> COUNT(*) OVER (PARTITION BY quarter_id) AS Quarter_Time_Span,<br /> MAX(day_id) OVER (PARTITION BY quarter_id) AS Quarter_End_Date,<br /> quarter_num_of_year,<br /> half_num_of_year,<br /> half_of_year_id,<br /> COUNT(*) OVER (PARTITION BY half_of_year_id) AS Half_Year_Time_Span,<br /> MAX(day_id) OVER (PARTITION BY half_of_year_id) AS Half_Year_End_Date,<br /> year_id,<br /> COUNT(*) OVER (PARTITION BY year_id) AS Year_Time_Span,<br /> MAX(day_id) OVER (PARTITION BY year_id) AS Year_End_Date<br />FROM base_calendar<br />ORDER BY day_id;<br /> );<br />--<br />COMMIT;</span></div>
Bud Endresshttp://www.blogger.com/profile/02590149552658898625noreply@blogger.com6tag:blogger.com,1999:blog-3820031471524503731.post-81427333554960337802010-12-02T04:26:00.000-08:002010-12-02T05:28:29.166-08:00Simba previews Cognos8 Analysis Studio accessing Oracle Database OLAP Option cubesHot on the heels of support for BusinessObjects Voyager, and in addition to the native Excel 2003/2007/2010 pivot table access, Simba are previewing the same connectivity for Cognos8 Analysis Studio - the dimensionally aware UI in the Cognos BI suite. <div><br /></div><div>Together with the unique SQL access to the same multidimensional data & calculations in Oracle Database OLAP cubes <i>(meaning that *any* tool or application capable of connecting to Oracle and issuing simple SQL can leverage the power of Database OLAP - like Oracle Application Express <a href="http://oracleolap.blogspot.com/2009/02/new-tutorial-creating-interactive-apex.html">for example</a>)</i>, <b>plus</b> the existing support for Oracle's own BI tools including </div><div><ul><li>Oracle BIEE 10g and 11g (see <a href="http://oracleolap.blogspot.com/2010/07/first-look-at-obiee-11g-with-oracle.html">http://oracleolap.blogspot.com/2010/07/first-look-at-obiee-11g-with-oracle.html</a> ) and </li><li>Oracle BI Discoverer Plus OLAP (see <a href="http://oracleolap.blogspot.com/2010/08/discoverer-olap-is-certified-with-olap.html">http://oracleolap.blogspot.com/2010/08/discoverer-olap-is-certified-with-olap.html</a> ), </li></ul></div><div>together with the big functionality and performance improvements in 11g , there is now every reason to move to Oracle Database 11gR2 and to fully exploit the OLAP Option - whatever your choice of front end tool(s). </div><div><br /></div><div>For Cognos fans: Here is the Video on YouTube:</div><div><br /><iframe width="480" height="295" src="http://www.youtube.com/v/ykMnttc9SNU?fs=1&hl=en_US&rel=0&color1=0x3a3a3a&color2=0x999999">&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;lt;/param&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;gt;&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;lt;param name="allowFullScreen" value="true" frameborder="0"&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;gt;</iframe><br /></div><div>More information, see the Simba website : <a href="http://www.simba.com/MDX-Provider-for-Oracle-OLAP.htm">http://www.simba.com/MDX-Provider-for-Oracle-OLAP.htm</a></div>Kevin Lancasterhttp://www.blogger.com/profile/06742628997065141834noreply@blogger.com0tag:blogger.com,1999:blog-3820031471524503731.post-8703976536440126902010-11-16T13:39:00.000-08:002010-11-16T13:55:28.297-08:00Simba previews Oracle OLAP MDX Provider connectivity to SAP BusinessObjects VoyagerSimba technologies have released a short video to preview 'Using MDX Provider for Oracle OLAP to directly connect SAP BusinessObjects Voyager to Oracle Database OLAP Option'<br /><br />This will be a great capability for users of both Oracle OLAP and BusinessObjects and will futher extend the reach of Oracle database embedded OLAP cubes.<br /><br /><br /><object width="640" height="390"><param name="movie" value="http://www.youtube.com/v/J4Vg655zbbQ&hl=en_US&feature=player_embedded&version=3"><param name="allowFullScreen" value="true"><param name="allowScriptAccess" value="always"><embed src="http://www.youtube.com/v/J4Vg655zbbQ&hl=en_US&feature=player_embedded&version=3" type="application/x-shockwave-flash" allowfullscreen="true" allowscriptaccess="always" width="640" height="390"></embed></object><br /><br /><br />You can get more details on the <a href="http://www.simba.com/MDX-Provider-for-Oracle-OLAP-FAQ-support-SAP-BusinessObjects-Voyager.htm">Simba website</a>Stuart Bunbyhttp://www.blogger.com/profile/10781347144821555643noreply@blogger.com0tag:blogger.com,1999:blog-3820031471524503731.post-54733650358617732032010-11-04T13:30:00.000-07:002010-12-02T05:06:16.657-08:00Microsoft Certifies Simba’s MDX Provider for Oracle OLAP as “Compatible with Windows 7”Simba announced today that Microsoft has certified its MDX Provider for Oracle Database OLAP Option for Windows 7.<div><br /></div><div>The press release is here : <a href="http://www.simba.com/news/MDX-Provider-for-Oracle-OLAP-Certified-for-Windows-7.htm">http://www.simba.com/news/MDX-Provider-for-Oracle-OLAP-Certified-for-Windows-7.htm</a></div><div><br /></div><div>This is great news for MS Office connectivity to your Database OLAP cubes. Already supported was Excel 2010 with its cool new BI features, as well as 2007 and 2003 versions. With Windows 7 support too, even the most up-to-date PCs are in good shape to exploit the OLAP Option.</div><div><br /></div><div>Remember - via the native Excel pivot table connectivity, other features of MS-Office can also leverage the power, performance and calculation functionality of Oracle OLAP. So for example, your Powerpoint presentation to the senior management team, or the one you use when talking to suppliers or key customers can now contain live charts onto the latest information available in the Oracle Database cubes. Instead of spending time cutting and pasting static images into your slide shows each day, week or period-end you can be focusing on the news in the data and doing a better analysis of the results.</div><div><br /></div><div>This is the perfect complement to your use of Oracle BI Enterprise Edition (10g or 11g) onto the exact same cubes for the broader BI use-case.</div><div><br /></div><div>In addition to Microsoft Excel, Simba is gradually certifying other MDX front ends for the OLAP Option MDX Provider - with SAP-BusinessObjects Voyager already available and others due soon.</div><div><br /></div><div>You can learn all about it on the Simba web site : <a href="http://www.simba.com/MDX-Provider-for-Oracle-OLAP.htm">http://www.simba.com/MDX-Provider-for-Oracle-OLAP.htm</a></div><div><br /></div><div>Connectivity into Oracle Database OLAP 11g is market leading: with the same cubes and calculations being accessible via MDX as well as a huge range of SQL based tools and applications, it has never been easier to deploy multidimensional analytics to the masses.</div><div><br /></div>Kevin Lancasterhttp://www.blogger.com/profile/06742628997065141834noreply@blogger.com0tag:blogger.com,1999:blog-3820031471524503731.post-82420729803238039782010-10-22T08:03:00.000-07:002010-10-22T08:49:14.547-07:00Cell level write-back via PL/SQLA topic of conversation that regularly comes up when I talk to customers and developers about the OLAP Option is write-back to OLAP cubes. The most frustrating of these conversations usually involves someone saying 'but... the OLAP Option doesn't support write-back'. This is not the case and never has been.<br /><br />Since the first OLAP Option release in 9i it has always been possible to write-back to cubes via the Java OLAP API and OLAP DML. But in recent releases, a new PL/SQL package based API has been developed. My thanks go to the ever-excellent David Greenfield of the Oracle OLAP product development group for bringing this to my attention.<br /><br />At the most simple level, it is possible to write to a qualified cell:<br /><br /><!--[if gte mso 9]><xml> <w:worddocument> <w:view>Normal</w:View> <w:zoom>0</w:Zoom> <w:trackmoves/> <w:trackformatting/> <w:punctuationkerning/> <w:validateagainstschemas/> <w:saveifxmlinvalid>false</w:SaveIfXMLInvalid> <w:ignoremixedcontent>false</w:IgnoreMixedContent> <w:alwaysshowplaceholdertext>false</w:AlwaysShowPlaceholderText> <w:donotpromoteqf/> <w:lidthemeother>EN-GB</w:LidThemeOther> <w:lidthemeasian>X-NONE</w:LidThemeAsian> <w:lidthemecomplexscript>X-NONE</w:LidThemeComplexScript> <w:compatibility> <w:breakwrappedtables/> <w:snaptogridincell/> <w:wraptextwithpunct/> <w:useasianbreakrules/> <w:dontgrowautofit/> <w:splitpgbreakandparamark/> <w:dontvertaligncellwithsp/> <w:dontbreakconstrainedforcedtables/> <w:dontvertalignintxbx/> <w:word11kerningpairs/> <w:cachedcolbalance/> </w:Compatibility> <w:browserlevel>MicrosoftInternetExplorer4</w:BrowserLevel> <m:mathpr> <m:mathfont val="Cambria Math"> <m:brkbin val="before"> <m:brkbinsub val="--"> <m:smallfrac val="off"> <m:dispdef/> <m:lmargin val="0"> <m:rmargin val="0"> <m:defjc val="centerGroup"> <m:wrapindent val="1440"> <m:intlim val="subSup"> <m:narylim val="undOvr"> </m:mathPr></w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:latentstyles deflockedstate="false" defunhidewhenused="true" defsemihidden="true" defqformat="false" defpriority="99" latentstylecount="267"> <w:lsdexception locked="false" priority="0" semihidden="false" unhidewhenused="false" qformat="true" name="Normal"> <w:lsdexception locked="false" priority="9" semihidden="false" unhidewhenused="false" qformat="true" name="heading 1"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 2"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 3"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 4"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 5"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 6"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 7"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 8"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 9"> <w:lsdexception locked="false" priority="39" name="toc 1"> <w:lsdexception locked="false" priority="39" name="toc 2"> <w:lsdexception locked="false" priority="39" name="toc 3"> <w:lsdexception locked="false" priority="39" name="toc 4"> <w:lsdexception locked="false" priority="39" name="toc 5"> <w:lsdexception locked="false" priority="39" name="toc 6"> <w:lsdexception locked="false" priority="39" name="toc 7"> <w:lsdexception locked="false" priority="39" name="toc 8"> <w:lsdexception locked="false" priority="39" name="toc 9"> <w:lsdexception locked="false" priority="35" qformat="true" name="caption"> <w:lsdexception locked="false" priority="10" semihidden="false" unhidewhenused="false" qformat="true" name="Title"> <w:lsdexception locked="false" priority="1" name="Default Paragraph Font"> <w:lsdexception locked="false" priority="11" semihidden="false" unhidewhenused="false" qformat="true" name="Subtitle"> <w:lsdexception locked="false" priority="22" semihidden="false" unhidewhenused="false" qformat="true" name="Strong"> <w:lsdexception locked="false" priority="20" semihidden="false" unhidewhenused="false" qformat="true" name="Emphasis"> <w:lsdexception locked="false" priority="59" semihidden="false" unhidewhenused="false" name="Table Grid"> <w:lsdexception locked="false" unhidewhenused="false" name="Placeholder Text"> <w:lsdexception locked="false" priority="1" semihidden="false" unhidewhenused="false" qformat="true" name="No Spacing"> <w:lsdexception locked="false" priority="60" semihidden="false" unhidewhenused="false" name="Light Shading"> <w:lsdexception locked="false" priority="61" semihidden="false" unhidewhenused="false" name="Light List"> <w:lsdexception locked="false" priority="62" semihidden="false" unhidewhenused="false" name="Light Grid"> <w:lsdexception locked="false" priority="63" semihidden="false" unhidewhenused="false" name="Medium Shading 1"> <w:lsdexception locked="false" priority="64" semihidden="false" unhidewhenused="false" name="Medium Shading 2"> <w:lsdexception locked="false" priority="65" semihidden="false" unhidewhenused="false" name="Medium List 1"> <w:lsdexception locked="false" priority="66" semihidden="false" unhidewhenused="false" name="Medium List 2"> <w:lsdexception locked="false" priority="67" semihidden="false" unhidewhenused="false" name="Medium Grid 1"> <w:lsdexception locked="false" priority="68" semihidden="false" unhidewhenused="false" name="Medium Grid 2"> <w:lsdexception locked="false" priority="69" semihidden="false" unhidewhenused="false" name="Medium Grid 3"> <w:lsdexception locked="false" priority="70" semihidden="false" unhidewhenused="false" name="Dark List"> <w:lsdexception locked="false" priority="71" semihidden="false" unhidewhenused="false" name="Colorful Shading"> <w:lsdexception locked="false" priority="72" semihidden="false" unhidewhenused="false" name="Colorful List"> <w:lsdexception locked="false" priority="73" semihidden="false" unhidewhenused="false" name="Colorful Grid"> <w:lsdexception locked="false" priority="60" semihidden="false" unhidewhenused="false" name="Light Shading Accent 1"> <w:lsdexception locked="false" priority="61" semihidden="false" unhidewhenused="false" name="Light List Accent 1"> <w:lsdexception locked="false" priority="62" semihidden="false" unhidewhenused="false" name="Light Grid Accent 1"> <w:lsdexception locked="false" priority="63" semihidden="false" unhidewhenused="false" name="Medium Shading 1 Accent 1"> <w:lsdexception locked="false" priority="64" semihidden="false" unhidewhenused="false" name="Medium Shading 2 Accent 1"> <w:lsdexception locked="false" priority="65" semihidden="false" unhidewhenused="false" name="Medium List 1 Accent 1"> <w:lsdexception locked="false" unhidewhenused="false" name="Revision"> <w:lsdexception locked="false" priority="34" semihidden="false" unhidewhenused="false" qformat="true" name="List Paragraph"> <w:lsdexception locked="false" priority="29" semihidden="false" unhidewhenused="false" qformat="true" name="Quote"> <w:lsdexception locked="false" priority="30" semihidden="false" unhidewhenused="false" qformat="true" name="Intense Quote"> <w:lsdexception locked="false" priority="66" semihidden="false" unhidewhenused="false" name="Medium List 2 Accent 1"> <w:lsdexception locked="false" priority="67" semihidden="false" unhidewhenused="false" name="Medium Grid 1 Accent 1"> <w:lsdexception locked="false" priority="68" semihidden="false" unhidewhenused="false" name="Medium Grid 2 Accent 1"> <w:lsdexception locked="false" priority="69" semihidden="false" unhidewhenused="false" name="Medium Grid 3 Accent 1"> <w:lsdexception locked="false" priority="70" semihidden="false" unhidewhenused="false" name="Dark List Accent 1"> <w:lsdexception locked="false" priority="71" semihidden="false" unhidewhenused="false" name="Colorful Shading Accent 1"> <w:lsdexception locked="false" priority="72" semihidden="false" unhidewhenused="false" name="Colorful List Accent 1"> <w:lsdexception locked="false" priority="73" semihidden="false" unhidewhenused="false" name="Colorful Grid Accent 1"> <w:lsdexception locked="false" priority="60" semihidden="false" unhidewhenused="false" name="Light Shading Accent 2"> <w:lsdexception locked="false" priority="61" semihidden="false" unhidewhenused="false" name="Light List Accent 2"> <w:lsdexception locked="false" priority="62" semihidden="false" unhidewhenused="false" name="Light Grid Accent 2"> <w:lsdexception locked="false" priority="63" semihidden="false" unhidewhenused="false" name="Medium Shading 1 Accent 2"> <w:lsdexception locked="false" priority="64" semihidden="false" unhidewhenused="false" name="Medium Shading 2 Accent 2"> <w:lsdexception locked="false" priority="65" semihidden="false" unhidewhenused="false" name="Medium List 1 Accent 2"> <w:lsdexception locked="false" priority="66" semihidden="false" unhidewhenused="false" name="Medium List 2 Accent 2"> <w:lsdexception locked="false" priority="67" semihidden="false" unhidewhenused="false" name="Medium Grid 1 Accent 2"> <w:lsdexception locked="false" priority="68" semihidden="false" unhidewhenused="false" name="Medium Grid 2 Accent 2"> <w:lsdexception locked="false" priority="69" semihidden="false" unhidewhenused="false" name="Medium Grid 3 Accent 2"> <w:lsdexception locked="false" priority="70" semihidden="false" unhidewhenused="false" name="Dark List Accent 2"> <w:lsdexception locked="false" priority="71" semihidden="false" unhidewhenused="false" name="Colorful Shading Accent 2"> <w:lsdexception locked="false" priority="72" semihidden="false" unhidewhenused="false" name="Colorful List Accent 2"> <w:lsdexception locked="false" priority="73" semihidden="false" unhidewhenused="false" name="Colorful Grid Accent 2"> <w:lsdexception locked="false" priority="60" semihidden="false" unhidewhenused="false" name="Light Shading Accent 3"> <w:lsdexception locked="false" priority="61" semihidden="false" unhidewhenused="false" name="Light List Accent 3"> <w:lsdexception locked="false" priority="62" semihidden="false" unhidewhenused="false" name="Light Grid Accent 3"> <w:lsdexception locked="false" priority="63" semihidden="false" unhidewhenused="false" name="Medium Shading 1 Accent 3"> <w:lsdexception locked="false" priority="64" semihidden="false" unhidewhenused="false" name="Medium Shading 2 Accent 3"> <w:lsdexception locked="false" priority="65" semihidden="false" unhidewhenused="false" name="Medium List 1 Accent 3"> <w:lsdexception locked="false" priority="66" semihidden="false" unhidewhenused="false" name="Medium List 2 Accent 3"> <w:lsdexception locked="false" priority="67" semihidden="false" unhidewhenused="false" name="Medium Grid 1 Accent 3"> <w:lsdexception locked="false" priority="68" semihidden="false" unhidewhenused="false" name="Medium Grid 2 Accent 3"> <w:lsdexception locked="false" priority="69" semihidden="false" unhidewhenused="false" name="Medium Grid 3 Accent 3"> <w:lsdexception locked="false" priority="70" semihidden="false" unhidewhenused="false" name="Dark List Accent 3"> <w:lsdexception locked="false" priority="71" semihidden="false" unhidewhenused="false" name="Colorful Shading Accent 3"> <w:lsdexception locked="false" priority="72" semihidden="false" unhidewhenused="false" name="Colorful List Accent 3"> <w:lsdexception locked="false" priority="73" semihidden="false" unhidewhenused="false" name="Colorful Grid Accent 3"> <w:lsdexception locked="false" priority="60" semihidden="false" unhidewhenused="false" name="Light Shading Accent 4"> <w:lsdexception locked="false" priority="61" semihidden="false" unhidewhenused="false" name="Light List Accent 4"> <w:lsdexception locked="false" priority="62" semihidden="false" unhidewhenused="false" name="Light Grid Accent 4"> <w:lsdexception locked="false" priority="63" semihidden="false" unhidewhenused="false" name="Medium Shading 1 Accent 4"> <w:lsdexception locked="false" priority="64" semihidden="false" unhidewhenused="false" name="Medium Shading 2 Accent 4"> <w:lsdexception locked="false" priority="65" semihidden="false" unhidewhenused="false" name="Medium List 1 Accent 4"> <w:lsdexception locked="false" priority="66" semihidden="false" unhidewhenused="false" name="Medium List 2 Accent 4"> <w:lsdexception locked="false" priority="67" semihidden="false" unhidewhenused="false" name="Medium Grid 1 Accent 4"> <w:lsdexception locked="false" priority="68" semihidden="false" unhidewhenused="false" name="Medium Grid 2 Accent 4"> <w:lsdexception locked="false" priority="69" semihidden="false" unhidewhenused="false" name="Medium Grid 3 Accent 4"> <w:lsdexception locked="false" priority="70" semihidden="false" unhidewhenused="false" name="Dark List Accent 4"> <w:lsdexception locked="false" priority="71" semihidden="false" unhidewhenused="false" name="Colorful Shading Accent 4"> <w:lsdexception locked="false" priority="72" semihidden="false" unhidewhenused="false" name="Colorful List Accent 4"> <w:lsdexception locked="false" priority="73" semihidden="false" unhidewhenused="false" name="Colorful Grid Accent 4"> <w:lsdexception locked="false" priority="60" semihidden="false" unhidewhenused="false" name="Light Shading Accent 5"> <w:lsdexception locked="false" priority="61" semihidden="false" unhidewhenused="false" name="Light List Accent 5"> <w:lsdexception locked="false" priority="62" semihidden="false" unhidewhenused="false" name="Light Grid Accent 5"> <w:lsdexception locked="false" priority="63" semihidden="false" unhidewhenused="false" name="Medium Shading 1 Accent 5"> <w:lsdexception locked="false" priority="64" semihidden="false" unhidewhenused="false" name="Medium Shading 2 Accent 5"> <w:lsdexception locked="false" priority="65" semihidden="false" unhidewhenused="false" name="Medium List 1 Accent 5"> <w:lsdexception locked="false" priority="66" semihidden="false" unhidewhenused="false" name="Medium List 2 Accent 5"> <w:lsdexception locked="false" priority="67" semihidden="false" unhidewhenused="false" name="Medium Grid 1 Accent 5"> <w:lsdexception locked="false" priority="68" semihidden="false" unhidewhenused="false" name="Medium Grid 2 Accent 5"> <w:lsdexception locked="false" priority="69" semihidden="false" unhidewhenused="false" name="Medium Grid 3 Accent 5"> <w:lsdexception locked="false" priority="70" semihidden="false" unhidewhenused="false" name="Dark List Accent 5"> <w:lsdexception locked="false" priority="71" semihidden="false" unhidewhenused="false" name="Colorful Shading Accent 5"> <w:lsdexception locked="false" priority="72" semihidden="false" unhidewhenused="false" name="Colorful List Accent 5"> <w:lsdexception locked="false" priority="73" semihidden="false" unhidewhenused="false" name="Colorful Grid Accent 5"> <w:lsdexception locked="false" priority="60" semihidden="false" unhidewhenused="false" name="Light Shading Accent 6"> <w:lsdexception locked="false" priority="61" semihidden="false" unhidewhenused="false" name="Light List Accent 6"> <w:lsdexception locked="false" priority="62" semihidden="false" unhidewhenused="false" name="Light Grid Accent 6"> <w:lsdexception locked="false" priority="63" semihidden="false" unhidewhenused="false" name="Medium Shading 1 Accent 6"> <w:lsdexception locked="false" priority="64" semihidden="false" unhidewhenused="false" name="Medium Shading 2 Accent 6"> <w:lsdexception locked="false" priority="65" semihidden="false" unhidewhenused="false" name="Medium List 1 Accent 6"> <w:lsdexception locked="false" priority="66" semihidden="false" unhidewhenused="false" name="Medium List 2 Accent 6"> <w:lsdexception locked="false" priority="67" semihidden="false" unhidewhenused="false" name="Medium Grid 1 Accent 6"> <w:lsdexception locked="false" priority="68" semihidden="false" unhidewhenused="false" name="Medium Grid 2 Accent 6"> <w:lsdexception locked="false" priority="69" semihidden="false" unhidewhenused="false" name="Medium Grid 3 Accent 6"> <w:lsdexception locked="false" priority="70" semihidden="false" unhidewhenused="false" name="Dark List Accent 6"> <w:lsdexception locked="false" priority="71" semihidden="false" unhidewhenused="false" name="Colorful Shading Accent 6"> <w:lsdexception locked="false" priority="72" semihidden="false" unhidewhenused="false" name="Colorful List Accent 6"> <w:lsdexception locked="false" priority="73" semihidden="false" unhidewhenused="false" name="Colorful Grid Accent 6"> <w:lsdexception locked="false" priority="19" semihidden="false" unhidewhenused="false" qformat="true" name="Subtle Emphasis"> <w:lsdexception locked="false" priority="21" semihidden="false" unhidewhenused="false" qformat="true" name="Intense Emphasis"> <w:lsdexception locked="false" priority="31" semihidden="false" unhidewhenused="false" qformat="true" name="Subtle Reference"> <w:lsdexception locked="false" priority="32" semihidden="false" unhidewhenused="false" qformat="true" name="Intense Reference"> <w:lsdexception locked="false" priority="33" semihidden="false" unhidewhenused="false" qformat="true" name="Book Title"> <w:lsdexception locked="false" priority="37" name="Bibliography"> <w:lsdexception locked="false" priority="39" qformat="true" name="TOC Heading"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:"Times New Roman"; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} </style> <![endif]--><span style=";font-family:";font-size:12pt;color:black;" ></span>dbms_cube.build(<br /> 'PRICE_COST_CUBE USING (<br /> SET PRICE_COST_CUBE.PRICE["TIME" = ''24'', PRODUCT = ''26''] = 711.61, SOLVE)')<br /><br />In the example above, a cube solve is executed after the cell write. The objects are referenced by their logical (ie. AWM) names.<br /><br />This approach is very flexible. For example you can qualify only some dimensions, in this case the assignment is for all products:<br /><br />dbms_cube.build(<br /> 'PRICE_COST_CUBE USING (<br /> SET PRICE_COST_CUBE.PRICE["TIME" = ''24''] = 711.61, SOLVE)')<br /><br />You can also skip the aggregation:<br /><br />dbms_cube.build(<br /> 'PRICE_COST_CUBE USING (<br /> SET PRICE_COST_CUBE.PRICE["TIME" = ''24'', PRODUCT = ''26''] = 711.61)')<br /><br />or run multiple cell updates in one call:<br /><br />dbms_cube.build(<br /> 'PRICE_COST_CUBE USING (<br /> SET PRICE_COST_CUBE.PRICE["TIME" = ''24'', PRODUCT = ''26''] = 711.61,<br /> SET PRICE_COST_CUBE.PRICE["TIME" = ''27'', PRODUCT = ''27''] = 86.82,<br /> SOLVE)');<br /><br />You can also copy from one measure to another.<br /><br />dbms_cube.build('UNITS_CUBE USING (SET LOCAL_CUBE.UNITS = UNITS_CUBE.UNITS'));<br /><br />This will copy everything from the UNITS measure in UNITS_CUBE to the UNITS measure in the LOCAL_CUBE. You can put fairly arbitrary expressions on the right hand side and the code will attempt to loop the appropriate composite. You can also control status.<br /><br /><br />For more details, take a look at the <a href="http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16760/d_cube.htm#CHDFCJBF">PL/SQL reference documentation</a>Stuart Bunbyhttp://www.blogger.com/profile/10781347144821555643noreply@blogger.com3tag:blogger.com,1999:blog-3820031471524503731.post-85615370831653292082010-10-07T08:11:00.001-07:002010-10-07T08:46:31.174-07:00Incremental Refresh of Oracle CubesOne of the more common questions I get is about how cubes are processed, in particular how and when Oracle cubes are processed incrementally. Here is a short summary of how the cube refresh process works, a few scenarios and some suggestions on being smart about solutions.<br /><br />- The cube will load all data from the source table.<br /><br />- You can limit the data loaded into the cube by a) presenting new/changed data only via a staging table or filtered view or b) making the cube refreshable using the materialized view refresh system and using a materialized view log table.<br /><br />- The cube will understand if data has been loaded into a partition. If data has not been loaded into a partition, it will not be processed (aggregated).<br /><br />- If a partition has had data loaded into it, it will processed (aggregated).<br /><br />- The cube will understand if a loaded value is new, changed or existing and unchanged. Only new or changed values are processed (the cube attempts to aggregate only new and changed cells and their ancestors).<br /><br />- Changing parentage of a member in a non-partitioned dimension will trigger a full solve of the cube (across all partitions).<br /><br />- If a member is added to a non-partitioned dimension, the cube will attempt an incremental aggregation of that dimension (that is, the new member and ancestors only).<br /><br />Here are two scenarios that illustrate how this works.<br /><br />1) How to trigger a lot of processing in the cube during a refresh:<br /><br />- Load from the full fact table rather than a staging table, filtered view or MV log table. The full table will be loaded into the cube.<br /><br />- Load data into many partitions. Each partition will need to be processed. For example, load data for the last 36 months when the cube is partitioned by time.<br /><br />- Load data into large partitions. For example, partition by year or quarter rather than quarter or month. Smaller partitions will process more quickly.<br /><br />- Make frequent hierarchy (parentage) changes in dimensions. For example, realign customers with new regions or reorganize the product dimension during the daily cube refresh. This will trigger a full cube refresh.<br /><br />2) How to efficiently manage a daily cube refresh:<br /><br />- Load from staging tables, filtered views or MV log tables where the tables/views contain only new or updated fact data. This will reduce the load step of the build. This becomes more important the larger the fact table is.<br /><br />- Use as fine grained partitioning strategy as possible. This will result in smaller partitions, which process more efficiently (full or incremental refresh) and offer more opportunity for parallel processing. Also, it is likely that fewer partitions will be processed.<br /><br />There can be a trade off with query performance. Typically, query performance is better when partitions are at a higher level (e.g., quarter rather than week) because there may be fewer partitions to access and less dynamic aggregation might be required. That said, the gain in refresh performance is typically much greater than the loss in query performance. Building a cube twice as fast is often more important than a small slowdown in query performance.<br /><br />- Only add new data into the partitioned dimension. For example if the cube is partitioned by time, add data only for new time periods. Only the partitions with those time periods will be refreshed.<br /><br />Clearly, there are many cases where data must be added to the non-partitioned dimensions. For example, new customers might be added daily. This is ok because a) new members are processed incrementally and b) new customers will likely affect only more recent partitions.<br /><br />Schedule hierarchy realignments (e.g., changing parentage in product, geography and account type dimensions) weekly instead of daily. This will limit the number of times a full refresh is required. It might also allow you to scheduled the full refresh for a time where the availability of system resources is high and/or the query load is low.<br /><br />The above scenarios help you understand how to most efficiently refresh a single cube. Also consider business requirements and how model the overall solution Two scenarios follow.<br /><br />1) Data is available at the day, item and store levels in the fact table. The business requirements are such that all data must be available for query, but in practice most queries (e.g., 95% or more) are at the week, item and store levels.<br /><br />Consider a solution where data is loaded in the cube at the week, item and city levels and more detailed data (day, item and store levels) are made available by drilling through to the table. This is very easy to do in a product such as Oracle Business Intelligence (OBIEE) or any other tool that has federated query capabilities and will be transparent to the business user.<br /><br />In this case, the cube is simply smaller and will process more quickly. The compromise is that calculations defined in the cube are only available at the week, item and city levels. This is often a reasonable trade off for faster processing (and perhaps more frequent updates).<br /><br />2) Data is available at the day, item and store levels in the fact table. The business requirements are such that all data must be available for query, but in practice:<br /><br />- Longer term trending (e.g., year over year) is done at the week level or higher.<br /><br />- Analysis of daily data (e.g., same day, week or month ago) is only done for the most recent 90 days.<br /><br />In this scenario, consider a two cube solution:<br /><br />- Cube A contains data at the week, item and store levels for all history (e.g., the past 36 months). This might be partitioned at the week level and aggregated to the month, quarter and year levels. Depending on reporting requirements, it might only be refreshed at the end of the week when the full week of data is available.<br /><br />- Cube B contains data only at the day level for the most recent 90 days. It is not aggregated to the week, month, quarter and year levels (aggregates are serviced by Cube A). This cube is used for the daily sales analysis. This might be partitioned at the day level so that a) any full build of the cube can be highly parallelized and b) the daily update processes only a single and relatively small partition.<br /><br />Using a tool such as Oracle Business Intelligence, which has federated query capabilities, a single business model can be created that accesses data from both cubes and the table transparently to the business user. When ever the user is querying at the week level or above, data OBIEE queries Cube A. If data is queried at the day level within the most recent 90 days, OBIEE queries Cube B. If data at the day level that is older than 90 days is access, OBIEE queries the table. Again, this can all be transparent to the user in a tool such as Oracle Business Intelligence.Bud Endresshttp://www.blogger.com/profile/02590149552658898625noreply@blogger.com1tag:blogger.com,1999:blog-3820031471524503731.post-7408110840213826892010-09-18T10:16:00.000-07:002010-09-18T10:33:57.959-07:00Simba Technologies and Vlamis Software Solutions hosting special reception at OpenWorld 2010<a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhOJjpmrLwwyXlqlcGv6JZ1qw2S73_rSdaL5271DiPVyCkZIvVHJZlVpQrRHFIFHiVDevUgZINgWsWVZkdJgZDGC7njLK0vzT5PDAN2kkJiNqiXJDhyphenhyphenuKOpk-4zi2W36Db4AgN9JVUugmpQ/s1600/image003.jpg"><img style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 332px; height: 400px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhOJjpmrLwwyXlqlcGv6JZ1qw2S73_rSdaL5271DiPVyCkZIvVHJZlVpQrRHFIFHiVDevUgZINgWsWVZkdJgZDGC7njLK0vzT5PDAN2kkJiNqiXJDhyphenhyphenuKOpk-4zi2W36Db4AgN9JVUugmpQ/s400/image003.jpg" border="0" alt="" id="BLOGGER_PHOTO_ID_5518307386129381570" /></a><br /><div><br /></div><div>Please join <b>Simba Technologies</b> and <b>Vlamis Software Solutions</b> at a special OpenWorld 2010 reception for current and prospective <b><span class="Apple-style-span" style="color:#FF0000;">Oracle</span></b> OLAP users.</div><div> </div><div><br /></div><div>Cocktails and hors d’oeuvres will be served.</div><div> </div><div><br /></div><div><b>Time</b>: Tuesday, September 21st, 2010 - 5pm – 7pm</div><div> </div><div><b>Location</b>: <i>walking distance from Moscone Center. Please RSVP for location</i>. </div><div><br /></div><div>By Invitation Only. RSVP to <a href="mailto:events@simba.com">events@simba.com</a></div><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiH0bR4uo2KKTU13Q_5pkaj6kIehalX-95EhalnoaQMLSnOuLF5AFy__myGy_qmxB5gUU8YLNIz-urQ2exeUyaq-UCuIJGrhugmj5B0H_yyK8O_Z_xC_kX23ggqRNoNSKuiBcDXl8waX9IU/s1600/image005.png"><img style="float:right; margin:0 0 10px 10px;cursor:pointer; cursor:hand;width: 80px; height: 92px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiH0bR4uo2KKTU13Q_5pkaj6kIehalX-95EhalnoaQMLSnOuLF5AFy__myGy_qmxB5gUU8YLNIz-urQ2exeUyaq-UCuIJGrhugmj5B0H_yyK8O_Z_xC_kX23ggqRNoNSKuiBcDXl8waX9IU/s400/image005.png" border="0" alt="" id="BLOGGER_PHOTO_ID_5518304786112099010" /></a><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjtJ74klp2i6liBTnoPDy1D4qwC-9aS5UlrIHCDbeIUyk0qlSNzgPHxg8ShT8ov87azB6AlYefGpfgfjAieKdk0MDrLoMHkjTL1OXdD5FS78v2F5ix7zxC05C7VAWQSOsSs7St__1FgfUE3/s1600/image009.png"><img style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 163px; height: 68px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjtJ74klp2i6liBTnoPDy1D4qwC-9aS5UlrIHCDbeIUyk0qlSNzgPHxg8ShT8ov87azB6AlYefGpfgfjAieKdk0MDrLoMHkjTL1OXdD5FS78v2F5ix7zxC05C7VAWQSOsSs7St__1FgfUE3/s400/image009.png" border="0" alt="" id="BLOGGER_PHOTO_ID_5518305122137563970" /></a>ASQLBaristahttp://www.blogger.com/profile/13350994132294695189noreply@blogger.com0tag:blogger.com,1999:blog-3820031471524503731.post-58852178836695651692010-09-17T08:35:00.000-07:002010-09-17T08:40:50.012-07:00Special OpenWorld 2010 Cocktail ReceptionJoin us! Simba Technologies and Vlamis Software Solutions are celebrating Oracle OLAP with a reception for current and prospective users. Please contact <a href="mailto:events@simba.com">events@simba.com</a> for exact location. Tuesday September 21, 2010 from 5-7PM. Walking distance from Moscone.<br /><br />Simba product managers will also be available at the Oracle OLAP pod in Moscone West to answer questions you might have about Excel Pivot Tables and the MDX Provider for Oracle OLAP.Jameson Whitehttp://www.blogger.com/profile/04697460456284466583noreply@blogger.com0tag:blogger.com,1999:blog-3820031471524503731.post-44364467760009494352010-08-10T04:02:00.000-07:002010-09-09T07:58:33.443-07:00Discoverer OLAP is certified with OLAP 11gA few people have asked me recently when an updated version of Discoverer OLAP will be released that supports the 11g OLAP Option.<span style=""> </span>The answer is simple - it has already been released!! (but I guess that many people missed it because it was bundled as part of a broader patchset and not widely announced)<p class="MsoNormal"><br /></p><p class="MsoNormal"><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi4tsj1GD5-mNBfs06VUcBK4JEqQbrU_5mICfyNIVV0PnvuN1_8AU7FnAUpy-3W7B4oPYLd1DW-Q1nbxjvbqovWl6tcVquxw0BQBUMwXK0tWNVwliJKPK-zG0PaVmVrdmL6CdTe8foo1Do/s1600/sshot.jpg"><img style="display: block; margin: 0px auto 10px; text-align: center; cursor: pointer; width: 400px; height: 300px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi4tsj1GD5-mNBfs06VUcBK4JEqQbrU_5mICfyNIVV0PnvuN1_8AU7FnAUpy-3W7B4oPYLd1DW-Q1nbxjvbqovWl6tcVquxw0BQBUMwXK0tWNVwliJKPK-zG0PaVmVrdmL6CdTe8foo1Do/s400/sshot.jpg" alt="" id="BLOGGER_PHOTO_ID_5503740127097184674" border="0" /></a></p> <p class="MsoNormal"><br /></p><p class="MsoNormal">If you are interested, you can download it from OTN under <a href="http://www.oracle.com/technetwork/middleware/downloads/fmw-11-download-092893.html"><span class="boldbodycopy">Portal, Forms, Reports and Discoverer (11.1.1.3.0)</span></a></p><p class="MsoNormal">An updated version of the BI Spreadsheet add-in has been released too and can also be downloaded from <a href="http://www.oracle.com/technetwork/middleware/bi-foundation/downloads/download-088181.html">OTN</a><br /></p><p class="MsoNormal"><br /></p><p class="MsoNormal"></p>Stuart Bunbyhttp://www.blogger.com/profile/10781347144821555643noreply@blogger.com0tag:blogger.com,1999:blog-3820031471524503731.post-54695066686896930352010-07-20T14:53:00.000-07:002010-08-16T02:05:28.448-07:00A first look at OBIEE 11g with Oracle OLAPFor those who missed it, the global launch for <a href="http://www.oracle.com/oms/businessintelligence11g/index.html">the 11g release of the Oracle Business Intelligence Enterprise Edition suite (OBIEE)</a> took place in London on July 7th.<br /><br />And the fantastic news for Oracle OLAP customers is that OBIEE 11g will work out-of-the-box with Oracle OLAP in almost exactly the same way as OBIEE 10g does - with just one additional configuration step required to enable the new OLAP-style front-end functionality.<br /><br />Of course, there are other features that are relevant such as the WebLogic application server, and the new security model, but these have already been well blogged elsewhere so the focus of this posting will be Oracle OLAP integration.<br /><br />To illustrate how easy it is, I will use a trusted old friend as a starting point - the <a href="http://www.oracle.com/technology/products/bi/olap/11g/samples/schemas/global_11g_schema.zip">11g Global sample schema</a>. I have installed this in an Oracle 11.2 database instance, created an Oracle OLAP Analytic Workspace, and then refreshed this AW so that the dimensions and cubes are built.<br /><br />With an AW in place, the next step is to use <a href="http://www.oracle.com/technology/products/bi/olap/11g/awm_plugin/biee/awm_plugin_biee.html">the OBIEE plug-in for AWM</a> to generate the metadata required for the OBIEE Server. For those who have not used the plug-in before, check out this <a href="http://download.oracle.com/otndocs/products/warehouse/olap/videos/obiee_plug_in_for_awm/OBIEE_Plugin.html">excellent demonstration of how it works</a>. While this particular version of the plug-in was originally released to work with OBIEE 10g, and presumably an updated version will be released in due course, it can be used in exactly the same way in OBIEE 11g to import metadata into the Administration tool.<br /><br /><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjgCzM0ehRsIp9K1hzFOKR2F4eVXka31Z5SQRU6GPzuduiK5XJlavLrCawTevO3rx9926hAD_wG-tEjkb429M7FcSFPMofFgrQZ1fg91644hi95nK5FcpnULd0EXfJg5ay20Bbofu0yb3Y/s1600/sshot1.JPG"><img style="display: block; margin: 0px auto 10px; text-align: center; cursor: pointer; width: 400px; height: 208px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjgCzM0ehRsIp9K1hzFOKR2F4eVXka31Z5SQRU6GPzuduiK5XJlavLrCawTevO3rx9926hAD_wG-tEjkb429M7FcSFPMofFgrQZ1fg91644hi95nK5FcpnULd0EXfJg5ay20Bbofu0yb3Y/s400/sshot1.JPG" alt="" id="BLOGGER_PHOTO_ID_5496132403917766642" border="0" /></a><br />And at first glance, aside from a few updated icons, this version of the Administration tool looks very similar, but the biggest change related to the administration of OLAP data sources (relational or MOLAP) is the ability to map hierarchy objects right through into the presentation layer.<br /><br />Here is the Metadata generated by the plug-in for the Channel Dimension in both the Business Model and Presentation layers<br /><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiFOqNRSLhyykAs3Z2jM4rQ4AdLjClWkHWkrDJqJCE9Dn1qLdjKl5md6odCF6EfDbfRi1iM0JaXRmAheeFuRIOyIp49vaIOZTOogoaGDH5KgKC8B6zj8M2_YMP9qmvCgTy7ceXxz4qKJSU/s1600/sshot2.jpg"><img style="display: block; margin: 0px auto 10px; text-align: center; cursor: pointer; width: 400px; height: 195px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiFOqNRSLhyykAs3Z2jM4rQ4AdLjClWkHWkrDJqJCE9Dn1qLdjKl5md6odCF6EfDbfRi1iM0JaXRmAheeFuRIOyIp49vaIOZTOogoaGDH5KgKC8B6zj8M2_YMP9qmvCgTy7ceXxz4qKJSU/s400/sshot2.jpg" alt="" id="BLOGGER_PHOTO_ID_5496405423368794850" border="0" /></a><br />The new 11g OLAP-style front-end functionality is enabled by adding these hierarchies into the Presentation layer too. This can be achieved by a simple click-and-drag for each hierarchy like the following which is again for the Channel Dimension<br /><br /><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEilkyfdDAG-NuOMnankB2AyeFTOyRpz-GiqNCo-2AYWABg1-YT-4Qa-HcTj27SL8CPeyiQz5dJTJEaEFBnUZRH0psLdxtSntvU9F2mj7rfa9UYh7J-y8k1Z43o9DWhe1Wln8Ttm3DXDxeU/s1600/sshot3.jpg"><img style="display: block; margin: 0px auto 10px; text-align: center; cursor: pointer; width: 320px; height: 193px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEilkyfdDAG-NuOMnankB2AyeFTOyRpz-GiqNCo-2AYWABg1-YT-4Qa-HcTj27SL8CPeyiQz5dJTJEaEFBnUZRH0psLdxtSntvU9F2mj7rfa9UYh7J-y8k1Z43o9DWhe1Wln8Ttm3DXDxeU/s320/sshot3.jpg" alt="" id="BLOGGER_PHOTO_ID_5496406933809428098" border="0" /></a><br />Hopefully, the next release of the plug-in will handle this additional step automatically (and also provide support for value-based hierarchies which were not supported by the front-end in OBIEE 10g) but in the meantime it really is just a simple click-and-drag for each dimension.<br /><br />Once all the hierarchies are mapped through into the Presentation Layer, the cube is ready to query. I can log into the OBIEE 11g home page and create a new analysis based upon my Oracle OLAP subject area. The new hierarchies are available for selection when I construct a query<br /><br /><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjeKuKdmeM8gYMlvXYZVBbSjfXqXbTd9WxTgvYuRMzuv0miiqnWlmP9cNFYGT1F1nBymQ0ecznTo4aC-eLaGf0UPZJ1earDEhEzRmhxmzSSTxQM80fzuBPTL-VwXBNYNok3cPbbmgx0_zg/s1600/sshot4.jpg"><img style="display: block; margin: 0px auto 10px; text-align: center; cursor: pointer; width: 400px; height: 319px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjeKuKdmeM8gYMlvXYZVBbSjfXqXbTd9WxTgvYuRMzuv0miiqnWlmP9cNFYGT1F1nBymQ0ecznTo4aC-eLaGf0UPZJ1earDEhEzRmhxmzSSTxQM80fzuBPTL-VwXBNYNok3cPbbmgx0_zg/s400/sshot4.jpg" alt="" id="BLOGGER_PHOTO_ID_5496760049859744818" border="0" /></a><br /><br />I can then select all of the 'columns' I need for my query and view the results as a pivot table. Here is a really simple example showing Sales by Time. I have also added some calculated measures which have been created inside the AW and derive really useful analytics from the Sales measure. This is a classic reason for using the OLAP Option in the first place - it facilitates the easy creation of calculations that are difficult (or often impossible) to express in SQL. And by having them embedded in the cube, the only thing that the SQL tool (in this case OBIEE) needs to do is select the calculation as a field in a view. How easy is that?!<br /><br /><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgi5A3rrADfkJwMXaOpuOwJrK1zOukfkN2g7cxRE_AgVi6TQxpyNTxSr9EzteWlIgMlqPP-H9djrMVnPGN4J-l__NeY4mva_LKF2URlfOsHOazfn6wrMo7C2t3NDS3Tkvq95CNDkdbHLCc/s1600/sshot6.jpg"><img style="display: block; margin: 0px auto 10px; text-align: center; cursor: pointer; width: 400px; height: 230px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgi5A3rrADfkJwMXaOpuOwJrK1zOukfkN2g7cxRE_AgVi6TQxpyNTxSr9EzteWlIgMlqPP-H9djrMVnPGN4J-l__NeY4mva_LKF2URlfOsHOazfn6wrMo7C2t3NDS3Tkvq95CNDkdbHLCc/s400/sshot6.jpg" alt="" id="BLOGGER_PHOTO_ID_5497112732735028514" border="0" /></a><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhD_Ww38nQFJsC-PbL1LVAjQXFqJnx-rNdpmkW8aQR087UsD9Terez67QzrA5-y4mZMP-1fyGxDU7xE_-pDmhjfoJX6_dN5hABEOtmvA3UTwumUTd4niV_kGypwf0i2a6sZSakDsIOzDCs/s1600/sshot5.jpg"><br /></a>Once a pivot table with Hierarchy-based columns has been created, this is where the new front-end features really come into play. Some highlights include Calculated Items (derived Dimension members) and a new Selector (which allows dimension selections to be built up as a series of steps based upon add/keep/remove logic):<br /><br /><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiZdjF9NFYEwXGO6OLcQc8VahV8KmMV3ai6fYttgvuwAYC6zYDcN8MnJ4amZfVJEe_UJY7JpOoFhBUfegJZmgVOcWQbeaAiP0JV7hSTJqG0S0EW2-c6IYISQai-mtpu6K2jdiJdhKHM83U/s1600/sshot7.jpg"><img style="display: block; margin: 0px auto 10px; text-align: center; cursor: pointer; width: 400px; height: 278px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiZdjF9NFYEwXGO6OLcQc8VahV8KmMV3ai6fYttgvuwAYC6zYDcN8MnJ4amZfVJEe_UJY7JpOoFhBUfegJZmgVOcWQbeaAiP0JV7hSTJqG0S0EW2-c6IYISQai-mtpu6K2jdiJdhKHM83U/s400/sshot7.jpg" alt="" id="BLOGGER_PHOTO_ID_5498621118915984258" border="0" /></a><br />For those familiar with Discoverer OLAP, or Sales Analyzer, Financial Analyzer and Express Objects/Analyzer, these aren't exactly revolutionary features, but combined with all the other great features of the OBIEE suite, this is now a very compelling platform for your Oracle OLAP data.<br /><br />Finally, I would guess that there are probably thousands of old Oracle Express/OLAP systems that have been waiting for a BI platform like this. If you work on one, what are you waiting for?<br /><br />***<a href="http://www.oracle.com/technetwork/middleware/bi-enterprise-edition/downloads/index.html">OBIEE 11g can now be downloaded from OTN</a>***Stuart Bunbyhttp://www.blogger.com/profile/10781347144821555643noreply@blogger.com3tag:blogger.com,1999:blog-3820031471524503731.post-74401175323828464002010-06-18T07:56:00.000-07:002010-06-18T08:54:28.463-07:00Bissantz DeltaMaster - Cool Tool for OLAP<p>I recently returned from a trip to Germany where visted a Bissantz, a relatively small company in Nürnburg that develops and markets an interesting reporting and data visualization tool named DeltaMaster that works with Oracle OLAP (and other data sources). I was very impressed with this tool. There are few things that I really liked about it:</p><ul><li>It's very good at displaying a lot of information within a single report. One of the ways that it does this is by mixing graphical representations of data with numerical representation (they are very big on something called 'Sparklines'). This makes it very easy to create a single report that includes data on, for example, sales for the current quarter but also provides indications of sales trends and shares.</li><br /><li>The presentation of data is very clean. While the reports themselves are very sophisticated, the developers have done a terrific job of presenting them to users. The presentation tends to be more functional than fluffy, but it's done very well. It is easy on the eyes.</li><br /><li>DeltaMaster goes way beyond basic cross tabs and charts. There are prebuilt reports / analysis templates for rankings, concentration analysis, portfolio analysis, etc. There's quite a few different types of pre-built analysis and I won't try to do justice to them here. See for yourself.</li><br /><li>It works better on OLAP than tables. I'm obviously biased when it comes to this topic, but for the end user this means more analytic power and flexibility.</li></ul><p></p><p></p><p></p><p>Below is a concentration analysis report. This is along the lines of a Pareto chart. There are many different types of built-in analysis, but this one looks nice in the confined space of this blog's page.</p><br /><p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiD1uOO6bMRBtJ5JeBwP0WtFOaX-UApqtMkIoWo0VadlIEDmV2DTMwl-A8RLqgcTmfRgr5QsRbpUSlID6VsqqtPCTXun8icB8vU8SzlZfmlhWTMEB2FWoBf4-lg-Rc9DkQvpncPM7UYBCwJ/s1600/dm1.JPG"><img style="WIDTH: 400px; HEIGHT: 271px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5484136058074133906" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiD1uOO6bMRBtJ5JeBwP0WtFOaX-UApqtMkIoWo0VadlIEDmV2DTMwl-A8RLqgcTmfRgr5QsRbpUSlID6VsqqtPCTXun8icB8vU8SzlZfmlhWTMEB2FWoBf4-lg-Rc9DkQvpncPM7UYBCwJ/s400/dm1.JPG" /></a><br /><br />Here are some links:</p><p>The DeltaMaster page at Bissantz: <a href="http://www.bissantz.com/products/">http://www.bissantz.com/products/</a></p><p>A clever blog by Bella, the Bissantz company dog: <a href="http://www.bella-consults.com/">http://www.bella-consults.com/</a></p><p>Bella, if you happen to find your way to this blog, here's a 'hello' from Henry (the OLAP product manager's dog).</p><p></p><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEieQdbsIvtjjcJww9hgktkKIe-yIkgS0zR03G1dSfn0EH9TO9dB-i1gOCPuCgSDO951Dc2AghS4nw60x6ov3G__SaSWyJz_15oS8zdn4D5RM4bpwKJ38g2F1aqgsAtO_IwbGGXN0JMz3tmk/s1600/henry2.bmp"><img style="WIDTH: 255px; HEIGHT: 400px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5484141358881257074" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEieQdbsIvtjjcJww9hgktkKIe-yIkgS0zR03G1dSfn0EH9TO9dB-i1gOCPuCgSDO951Dc2AghS4nw60x6ov3G__SaSWyJz_15oS8zdn4D5RM4bpwKJ38g2F1aqgsAtO_IwbGGXN0JMz3tmk/s400/henry2.bmp" /></a><br /><p></p><br /><p></p><br /><p></p><br /><p></p><br /><p></p>Bud Endresshttp://www.blogger.com/profile/02590149552658898625noreply@blogger.com0tag:blogger.com,1999:blog-3820031471524503731.post-49125360497630275672010-05-06T06:26:00.000-07:002010-05-06T08:02:34.092-07:00Time Dimensions with Hourly Time PeriodsI was working on an application last week that required time series analysis at Hour, Day, Month, Quarter and Year levels. Two interesting things came out of this application.<br /><br />First, a little implementation detail. The data was supplied in the fact and dimension tables at the Hour level with a TIMESTAMP data type. As you might expect then, there were time periods at the hour level such as:<br /><br />02-JAN-10 10.00.00.000000000 AM<br />02-JAN-10 11.00.00.000000000 AM<br />02-JAN-10 12.00.00.000000000 PM<br />02-JAN-10 01.00.00.000000000 PM<br />02-JAN-10 02.00.00.000000000 PM<br /><br />In my first attempt at building the time dimension I loaded hours directly from TIMESTAMP data type. In that case, the members at Hour level were loaded into the dimension stripped of the hour (e.g., 02-JAN-10). Since this isn't what I wanted, I converted the hours into a CHAR as follows:<br /><br />CREATE VIEW time_dim_view AS<br />SELECT<br /> TO_CHAR(hour_id, 'DD-MON-YYYY HH24') AS hour_id,<br /> TO_CHAR(hour_id, 'DD-MON-YYYY HH24') AS hour_desc,<br /> hour_time_span,<br /> hour_id AS hour_end_date,<br /> .. and so on.<br /><br />This gave me dimension members at hour as follows:<br /><br />01-JAN-2010 00<br />01-JAN-2010 01<br />01-JAN-2010 02<br />01-JAN-2010 03<br />01-JAN-2010 04<br /><br />That worked just fine. I did the same for the descriptions (so that they would be more easily readable by end users) and added a corresponding column to a fact view so that the time view and fact view joined correctly on the TO_CHAR(...) columns.<br /><br />For the TIME SPAN attribute, I used a fractional value of DAY (0.041667, which is 1/24th of a day). I read the DATETIME into the END DATE attribute as is (no conversion required). From there on, everything worked perfectly (cube builds, time series calculations, etc).<br /><br />If you happen to look at the END DATE attribute from the OLAP DML side, be sure to wrap the END_DATE object in a TO_CHAR function so that you see the hours. Otherwise, you will see only the day in most cases (it depends on the NLS_DATE_FORMAT setting for the session). For example:<br /><br />REPORT DOWN TIME TO_CHAR(TIME_END_DATE 'DD_MON_YYYY HH24')<br /><br />The other thing that was interesting has more to do with the application design. As so often happens, the customer was inclined to build one cube with all history at the hour level (two years of history). When examining the reporting requirements, however, it turned out that hour level analysis very rarely occurs more than 2 month back. Almost all of the reporting looking back over the two years was at the day level or higher (that is, not hourly level reporting).<br /><br />We could have built the one cube (two years, hour and higher), but most of the processing of hour level data would have been a waste because users don't look at the older data at that level. Instead, we built a very efficient application with two cubes. One cube contained only three months of data at the hour, day, month, quarter and year levels. Another cube contained two years of history starting at the day level.<br /><br />Presentation of the data is mostly done using Oracle Business Intelligence Enterprise Edition(via SQL to the cube). Some reports examine hourly level data. Other reports examine more aggregate data over longer time periods. Time series calculations (e.g., period to date, moving average, etc.) were added to both cubes and made available in the OBIEE reports.<br /><br />Occasionally, a user will want to drill from day to hour more than three months back. To support this, OBIEE was set up to drill from day (in the two year cube) to hour in the fact table. The only compromise was that the time series calculations of the cube were not available when drilling to hour in the fact table. That didn't matter to these users.<br /><br />From the end user perspective, the fact that there were two cubes instead of one (as well as a fact table) was completely irrelevant since OBIEE presented all data in reports in a single dashboard. From a processing perspective, the system was much more efficient and manageable as compared to the single big cube approach.<br /><br />It is very worthwhile to keep this lesson in mind when you design your applications. Pay careful attention to reporting requirements and build cubes that meet those requirements. You can tie multiple cubes together in a tool such as OBIEE. This approach is often much better then building a single cube every level of detail.<br /><br />In this case, the example is about what level of detail is in which cube. The same concept applies to dimensions. You might find it much more efficient to build Cube 1 with dimensions A, B, C and D and Cube 2 with dimensions A, B, E and F rather than one big cube with all dimensions.Bud Endresshttp://www.blogger.com/profile/02590149552658898625noreply@blogger.com0tag:blogger.com,1999:blog-3820031471524503731.post-41227035682840109082010-03-16T11:44:00.000-07:002010-03-16T12:37:16.706-07:00Parallel Execution of OLAP DMLWhile I was teaching a workshop in Moscow recently a student asked about parallel execution of OLAP DML. Their cube processing included the usual loading and aggregation, which are automatically parallelized, but they also have some assignments into the cube which are done using an OLAP DML program. They noted that this was slow because it was single threaded on one CPU. I told the student that Oracle OLAP supports a multi-write attach mode that can be used to update multiple objects in parallel using separate sessions. I checked the documentation, but didn't find a clear explanation of how this works in the context of processing partitions of a cube in parallel.<br /><br />Here's a code sample that illustrates how to process partitions of a cube in parallel. For this example, assume that the cube has four dimensions: time, product, geography and channel. The cube is partitioned on the time dimension at the retail year level. An OLAP DML program is used to assign data into the cube at the day level (it will be aggregated later).<br /><br /><span style="font-family:courier new;">" Attach the AW<br />AW ATTACH global_business multi<br /><br />" Set status of time to match the partition. In this example, the partition is 'P3'<br />LIMIT time TO PARTITION(sales_prt_template) EQ 'P3'<br /><br />" Keep data at the day level.<br />LIMIT time KEEP time_levelrel 'DAY'<br /><br />" Set status of other dimensions.<br />LIMIT product TO product_levelrel 'ITEM'<br />LIMIT geography TO geography_levelrel 'STATE_PROVINCE'<br />LIMIT store TO store_levelrel 'STORE'<br /><br />" Acquire the partition for write.<br />ACQUIRE RESYNC sales_stored_stored (PARTITION p3)<br /><br />" OLAP DML assignments.<br />sales_stored(sales_measure_dim 'DOLLAR_SALES') = sales_stored(sales_measure_dim 'QUANTITY') * price_stored(price_measure_dim 'PRICE') across sales_stored_prt_template<br /><br />" Update and commit to save work.<br />UPDATE multi<br />COMMIT<br /><br />" Release the partition.<br />RELEASE sales_stored (partition p3)<br /><br />" Detach the AW<br />AW DETACH global_business<br /></span><br /><br />The important points are:<br /><ul><li>The attach mode is MULTI</li><li>Use ACQUIRE to attach the appropriate partition.</li><li>The PARTITION function can be used to set status of the partitioned dimension to the dimension members in the partition.</li><li>Use the MULTI keyword with UPDATE.</li><li>RELEASE the partition.</li><li>While it doesn't have anything to do with running this in parallel, "across sales_stored_prt_template" will cause the OLAP DML to loop the local composite index of the partition rather than loop over the base dimensions.</li></ul><p>Repeat this code (or use parameters) for each partition. To run in parallel, run these commands in seperate database sessions for each partition. The above code sample is pure OLAP DML. In practice, it makes sense to execute this in the context of PL/SQL that you can call in separate SQL Plus sessions. E.g., </p><p><span style="font-family:courier new;">BEGIN</span></p><p><span style="font-family:courier new;">-- Attach the AW in multi-write model.<br /></span><span style="font-family:courier new;">dbms_aw.execute('AW ATTACH global_business multi');</span></p><p><span style="font-family:courier new;">-- Set status of time to match the partition.<br /></span><span style="font-family:courier new;">dbms_aw.execute('LIMIT time TO PARTITION sales_prt_template) EQ ''P1''');<br /></span><span style="font-family:courier new;">dbms_aw.execute('LIMIT time KEEP time_levelrel ''DAY'''); </span></p><p><span style="font-family:courier new;">-- Set status of other dimensions.<br /></span><span style="font-family:courier new;">dbms_aw.execute('LIMIT product TO product_levelrel ''ITEM''');<br /></span><span style="font-family:courier new;">dbms_aw.execute('LIMIT geography TO geography_levelrel ''STATE_PROVINCE''');<br /></span><span style="font-family:courier new;">dbms_aw.execute('LIMIT store TO store_levelrel ''STORE''');</span></p><p><span style="font-family:courier new;">-- Aquire the partition for write.<br /></span><span style="font-family:courier new;">dbms_aw.execute('ACQUIRE RESYNC sales_stored_stored (PARTITION p1)');</span></p><p><span style="font-family:courier new;">-- OLAP DML assigment.<br /></span><span style="font-family:courier new;">dbms_aw.execute('sales_stored(sales_measure_dim ''DOLLAR_SALES'') = sales_stored(sales_measure_dim ''QUANTITY'') * price_stored(price_measure_dim ''PRICE'') across sales_prt_template'); </span></p><p><span style="font-family:courier new;">-- Update to save work.<br /></span><span style="font-family:courier new;">dbms_aw.execute('UPDATE multi');</span></p><p><span style="font-family:courier new;">-- Release the partition.<br /></span><span style="font-family:courier new;">dbms_aw.execute('RELEASE sales_stored_stored (partition p1)');</span></p><p><span style="font-family:courier new;">-- Detach the AW<br />dbms_aw.execute('AW DETACH global_business');</span></p><p><span style="font-family:courier new;">END;<br /></span><span style="font-family:courier new;">/</span></p><p><span style="font-family:courier new;">COMMIT;</span></p><p>If this is done with PL/SQL, then you can run a shell script such as this to run all partitions in parallel.</p><p><span style="font-family:courier new;">sqlplus olaptrain/***** @set_sales_cost_p1.sql &<br /></span><span style="font-family:courier new;">sqlplus olaptrain/***** @set_sales_cost_p2.sql &<br /></span><span style="font-family:courier new;">sqlplus olaptrain/***** @set_sales_cost_p3.sql &<br /></span><span style="font-family:courier new;">sqlplus olaptrain/***** @set_sales_cost_p4.sql &</span></p><p></p>Bud Endresshttp://www.blogger.com/profile/02590149552658898625noreply@blogger.com0tag:blogger.com,1999:blog-3820031471524503731.post-11165492161875763592010-02-23T06:41:00.001-08:002010-02-23T07:08:46.323-08:00Excel and Oracle OLAP - Reporting No-Agg MeasuresI've run into this a few times recently, so here's a quick tip related to using Excel with Oracle OLAP (via the Simba MDX Provider for Oracle OLAP, of course).<br /><br />Here's a situation that's been reported as a bug, but you really just need to know the right Excel Pivot Table option to choose. Consider a cube that has measures that do not aggregate but is dimensioned by a dimension with a hierarchy. In this case, there is a cube with a Store dimension with levels Store > Store Type > All Stores. The stores are located in different countries and sell in local currencies. There is a Local Currency measure, with sales reported in whatever the local currencies might be (Euros, Dollars, Yen, etc.) and a Dollar Sales measure with the U.S. Dollar conversation. As a common currency, Dollars can be aggregated. Local currencies can't be aggregated.<br /><br />Here's a sample report in Excel.<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjNEBoKqCBQ1ejtCCvT20rDLSlNVi2CzUOhEjiB41D2VLhHEgYF8gJ-zEBjVTR_OdsJAXL0jmwLANO-zL4OxaBRXtsuLmyQa0o0vW7gAseIEdj9q2WoeVQuvMQE4bYBEtjbcEwcogrlEu2x/s1600-h/EXCEL_1.JPG"><img style="WIDTH: 316px; HEIGHT: 400px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5441452097737612114" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjNEBoKqCBQ1ejtCCvT20rDLSlNVi2CzUOhEjiB41D2VLhHEgYF8gJ-zEBjVTR_OdsJAXL0jmwLANO-zL4OxaBRXtsuLmyQa0o0vW7gAseIEdj9q2WoeVQuvMQE4bYBEtjbcEwcogrlEu2x/s400/EXCEL_1.JPG" /></a><br /><br />Note that Dollar Sales is reported for Direct and Indirect but Local Sales is not. That's correct because Local Sales doesn't aggregate.<br /><br />But what if I happen to select only Local Sales (which is null at the aggregate members Direct and Indirect). By default, Excel will display the report as shown below.<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj6gZ_mVlRy-bGcRBYSY8lvGpvVmtZBrZq26FKlI6BUCsKIm8QSDLf1zanGnsFID7BESv_q8uuj-fefek9gKM21XuXkjZwXAIsZ4eTyEvqkduOxVENhRK_SjbvuxcME9WwsXAix2Cvs3pSz/s1600-h/excel_2.JPG"><img style="WIDTH: 279px; HEIGHT: 308px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5441452954260709586" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj6gZ_mVlRy-bGcRBYSY8lvGpvVmtZBrZq26FKlI6BUCsKIm8QSDLf1zanGnsFID7BESv_q8uuj-fefek9gKM21XuXkjZwXAIsZ4eTyEvqkduOxVENhRK_SjbvuxcME9WwsXAix2Cvs3pSz/s400/excel_2.JPG" /></a><br /><br />This isn't very useful because I can't drill down on the Direct member to get at the stores. The solution is simple, but a lot of people seem to miss it. Just choose the Show items with no data in rows PivotTable option.<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhS5C2SOKHn8UbHQmJaxzwUX6NYYB_xQAT3VYxoirI7vcM840_K5ucDm_yY2QFXXnFgTF1W6YAPEBTgpCvn23TCV-M7ceI5qfsqdfFgF6M00aHIQrVn08jUqnTdKumXZRJRT9ZvRmg0Tw1B/s1600-h/excel_3.JPG"><img style="WIDTH: 385px; HEIGHT: 400px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5441453658651179090" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhS5C2SOKHn8UbHQmJaxzwUX6NYYB_xQAT3VYxoirI7vcM840_K5ucDm_yY2QFXXnFgTF1W6YAPEBTgpCvn23TCV-M7ceI5qfsqdfFgF6M00aHIQrVn08jUqnTdKumXZRJRT9ZvRmg0Tw1B/s400/excel_3.JPG" /></a><br /><br />Now you will be able to see the Direct and Indirect members, allowing you to drill to stores.<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgSEeCMHDzVQc5qvbpSktXnaEZTCQJfB3eH2RXWZGtr-dMe1Rd2QMaqRw9KBMxt9UKojCZNNYWoA5QqaaZ1h76vDUPBvCX-bzkgXWROAiBXngjFEzcjaoWQkGMZRziRUcc52V0hpI5ZOdLs/s1600-h/excel_4.JPG"><img style="WIDTH: 288px; HEIGHT: 337px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5441454242033767410" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgSEeCMHDzVQc5qvbpSktXnaEZTCQJfB3eH2RXWZGtr-dMe1Rd2QMaqRw9KBMxt9UKojCZNNYWoA5QqaaZ1h76vDUPBvCX-bzkgXWROAiBXngjFEzcjaoWQkGMZRziRUcc52V0hpI5ZOdLs/s400/excel_4.JPG" /></a><br /><br />Now, after the drill.<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg25rgKUj2bR6KmCWsEc-NUKlpphtUQsHLRGVpNVIYtCWsDwrDU-55T_Yn3zwFZM-X7v0BUUTadcVpLlzo3jDzv6BEu-HmWws6CchGxRPQea0uH1y7CHQ6_XWOR6qX9pI8LwPhxjPuktGBN/s1600-h/excel_5.JPG"><img style="WIDTH: 279px; HEIGHT: 400px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5441454622166932834" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg25rgKUj2bR6KmCWsEc-NUKlpphtUQsHLRGVpNVIYtCWsDwrDU-55T_Yn3zwFZM-X7v0BUUTadcVpLlzo3jDzv6BEu-HmWws6CchGxRPQea0uH1y7CHQ6_XWOR6qX9pI8LwPhxjPuktGBN/s400/excel_5.JPG" /></a>Bud Endresshttp://www.blogger.com/profile/02590149552658898625noreply@blogger.com0