Drillbridge 3.4.6 Token Enhancements: Join Character and Line Removal

Drillbridge 3.4.6 will be publicly released soon. It contains a couple of enhancements to give developers even more control over the way their queries are generated.

For each token, you can now optionally specify the “token join text” as well as certain “line removal members”.

Token Join Text

Drillbridge has supported upper-level drill for many years – the ability to drill on an upper level such as YearTotal or Q1 or Total Entities and generate a SQL query based on all of the members below it. This has even worked with PBCS for years as well, providing an easy way to implement drill-through from upper-levels from PBCS too. How this generally works is that Drillbridge will fetch the level-0 descendants of the drilled member and then concatenate them together to use in you SQL IN clause. For example, consider the following Drillbridge query:

SELECT * FROM SAMPLE_BASIC_TRANSACTIONS WHERE
    STATE = '{{"name":"Market","expression":"#Market","sampleValue":"Washington"}}' 
    AND MEASURE = '{{"name":"Measures","expression":"#Measures","sampleValue":"Sales"}}'
    AND SCENARIO = '{{"name":"Scenario","expression":"#Scenario","sampleValue":"Actual"}}'
    AND PRODUCT = '{{"name":"Product","expression":"#Product","sampleValue":"100-10"}}'
    AND SUBSTRING(TX_DATE, 6, 2) IN {{"name":"Year","expression":"#monthToTwoDigits(#Year, 'en')","drillToBottom":true,"sampleValue":"Year","overflow":"OR TX_MONTH IN %%OVERFLOW%%","overflowAt":3}}
ORDER BY
    TX_DATE

There’s nothing too fancy going on in this query. Well, okay, a little bit. This query is based on our favorite database, Sample/Basic. You can see that the query from the SAMPLE_BASIC_TRANSACTIONS table takes into account the value from the Market, Measures, Scenario, Product, and Year dimensions (recall that in Sample/Basic, Year is the time dimension and its children are quarters).

This query has really simple mappings for all of the dimensions except the Year (time) dimension, which is where things get interesting. In this example, the contents of the TX_DATE column are values such as ‘2019-10-22’ – that is, a pretty standard year-month-day format with hyphens. The incoming values from the cube, however, will be month names such as Jan, Feb, Mar. This example uses the built-in Drillbridge function #monthToTwoDigits to convert the month names to two digits. This is a function that has been built-in since the earliest versions of Drillbridge. Yes, you can convert dates using SQL, but every database has its own particular syntax. Having this built-in makes report development much easier, particularly in this example where we are also doing a drill to bottom operation. Let’s take a look at what happens when we drill on some intersection that includes Jan for the member in the Year dimension:

SELECT * FROM SAMPLE_BASIC_TRANSACTIONS WHERE
    STATE = 'Washington' 
    AND MEASURE = 'Sales'
    AND SCENARIO = 'Actual'
    AND PRODUCT = '100-10'
    AND SUBSTRING(TX_DATE, 6, 2) IN ('01')
ORDER BY
    TX_DATE

Since drillToBottom is enabled for this token, Drillbridge gets the level-0 descendants of the drilled member (in this case it simply yields the drilled member itself as it has no children and is itself a level-0 member), applies the expression/function to each item (converting Jan to 01), surrounds each item with quotes, then surrounds the whole thing with parentheses. We end up with a valid SQL query that combines native SQL (the SUBSTRING function) on the TX_DATE column (extracting the two digit month) and checks if it’s IN the specified values. Now let’s drill on an upper-level member (Qtr1) and see what we get:

SELECT * FROM SAMPLE_BASIC_TRANSACTIONS WHERE
    STATE = 'Washington' 
    AND MEASURE = 'Sales'
    AND SCENARIO = 'Actual'
    AND PRODUCT = '100-10'
    AND SUBSTRING(TX_DATE, 6, 2) IN ('01', '02', '03')
ORDER BY
    TX_DATE

Drillbridge was given Qtr1, went out to the Essbase outline, saw that the level-0 descendants were Jan, Feb, Mar, applied the expression/function, then joined them all together. With the new custom join text option turned on (and some custom join text specified, such as a semicolon in the following example), we get this:

SELECT * FROM SAMPLE_BASIC_TRANSACTIONS WHERE
    STATE = 'Washington' 
    AND MEASURE = 'Sales'
    AND SCENARIO = 'Actual'
    AND PRODUCT = '100-10'
    AND SUBSTRING(TX_DATE, 6, 2) IN ('01'; '02'; '03')
ORDER BY
    TX_DATE

Historically, Drillbridge has always assumed the text to join things together with was a comma, because that’s what all SQL IN clauses take. The custom join text gives you full control over this now, though. This probably won’t be useful in SQL queries, but it is useful for the Forwarding Link report type in Drillbridge, which generates a URL to send the user to rather than a SQL query. This report type is generally used to send a request over to an image server, OBIEE, or some other system. Now that you can specify custom text, many more options are opened up and accommodated.

Line Removal Members

Building on the previous example, let’s say that you drilled on Year instead of Qtr1. You would get the following query:

SELECT * FROM SAMPLE_BASIC_TRANSACTIONS WHERE
    STATE = 'Washington' 
    AND MEASURE = 'Sales'
    AND SCENARIO = 'Actual'
    AND PRODUCT = '100-10'
    AND SUBSTRING(TX_DATE, 6, 2) IN ('01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12')
ORDER BY
    TX_DATE

As with before, Drillbridge went out to the outline, found the descendants, mapped them with the expression, and generated the query for us. The interesting thing about this case, though, is that now in this context, the filter on date is essentially useless, since all of the months are included in 01 through 12. I’ll point out that this example is for demonstration purposes and while it’s not incredibly contrived, I have seen many datasets with a 00 or 13 month (to represent starting/ending balances) that this wouldn’t work on – but the principle still applies. In any case, Drillbridge had to go to the work of consulting the outline for the members (not a huge deal), and then made the SQL database do the work of applying the IN clause. For only 12 members, this isn’t necessarily a huge performance consideration. But many organizations performing drill to bottom on doing it on dimensions with thousands of members.

Drillbridge now supports a new option per token called “line removal members”. The way it works is that if a member is drilled on is in the list of line removal members, then Drillbridge will remove the entire line with the token on it, as well as skip the member resolution process. For instance, considering the previous example, if the user drills on the Year member, then we just don’t even care about that particular clause in the WHERE statement. For instance, let’s say that the line removal members in the Year token is the comma-delimited list of “Year, Test, All Periods” – meaning if the drilled member in the Year dimension is either a member named Year, a member named Test, or a member named All Periods, then kill the whole line. And sure enough, if we drill on Year we get this query:

SELECT * FROM SAMPLE_BASIC_TRANSACTIONS WHERE
    STATE = 'Washington' 
    AND MEASURE = 'Sales'
    AND SCENARIO = 'Actual'
    AND PRODUCT = '100-10'
ORDER BY
    TX_DATE

Properly setting up this feature may involve crafting your query in a slightly different way than you might otherwise. Since the whole line is removed, you may need to consider the placement of your AND clause. In this case, since AND was the first part of the line, we’re good remove it (as opposed to AND being on the end of the prior line). Also, if we only had one predicate in the WHERE clause then it might be necessary to add the “dummy” clause (1=1) – this is a pretty common thing to see in SQL as it always evaluates to true.

The line removal members feature can only be used in certain circumstances but in cases where your users may be drilling from the “top of the house” it could offer potentially enormous performance benefits for your query execution time.

Oracle Essbase Docker June Update

The Oracle Essbase on Docker open source project continues to evolve and be refined. For some background, please check out the original announcement. Briefly, this open source project gives you the ability to combine Oracle’s Essbase installer files along with the Docker technology to build a lean, mean, fully configured Essbase server (for development and testing purposes).

The following improvements have been made:

  • Ability to apply patches
  • Out of the box configuration of SQL ODBC data source (for load rules)
  • Further reductions in image size and number of Docker layers
  • Add convenience variables

Patches

This is the major feature in this release. The previous version of the Dockerfile would generate a perfect Essbase 11.1.2.4 server – version 11.1.2.4.000 to be precise. I’ve now added the ability to also include patches and have them be applied in an arbitrary order. For instance, the typical files that you need to combine with the scripts in this project would be Oracle’s installation media for EPM 11.1.2.4, which is Foundation-11124-linux64-Part1.zip, similar files for Parts 2, 3, 4, and updated Java JDK files. You can now include a folder named /patches with a subdirectory for a given patch level that contains the patches from Oracle. For example, here is my cloned docker-essbase Git repository to which I have added the EPM installation files from Oracle:

-rwxrwxr-x 1 jason jason        442 May 31 02:05 apply_patches.sh
 -rwxrwxr-x 1 jason jason       6057 May 31 02:15 config-and-start.sh
 -rw-rw-r-- 1 jason jason        966 Jan  7 20:57 deploy-landing.sh
 -rw-rw-r-- 1 jason jason       1020 May 17 02:25 docker-compose.yml
 -rw-rw-r-- 1 jason jason       7377 May 23 15:25 Dockerfile
 -rwxrwxr-x 1 jason jason 1368259501 Dec 28 21:25 Essbase-11124-linux64.zip
 -rw-rw-r-- 1 jason jason      13700 May 17 02:24 essbase-config.xml
 -rw-rw-r-- 1 jason jason        939 Jan  3 19:28 essbase-install.xml
 -rwxrwxr-x 1 jason jason         47 Jan  3 19:29 essbash.sh
 -rwxrwxr-x 1 jason jason        177 Jan  3 19:20 follow-essbase-logs.sh
 -rwxrwxr-x 1 jason jason 1174052554 Dec 28 21:22 Foundation-11124-linux64-Part1.zip
 -rwxrwxr-x 1 jason jason 1516370785 Dec 28 21:22 Foundation-11124-linux64-Part2.zip
 -rwxrwxr-x 1 jason jason  980505762 Dec 28 21:22 Foundation-11124-linux64-Part4.zip
 -rwxrwxr-x 1 jason jason 1529284475 Dec 28 21:22 Foundation-11124-Part3.zip
 -rw-r--r-- 1 jason jason  145798191 Feb  6 20:14 jdk-7u211-linux-x64.tar.gz
 -rw-r--r-- 1 jason jason  153530841 Jan  8 06:47 jdk-7u80-linux-x64.tar.gz
 -rwxr-xr-x 1 jason jason     611504 Dec 31 21:19 jtds12.jar
 -rw-rw-r-- 1 jason jason       1058 Mar 18 18:21 LICENSE
 -rw-rw-r-- 1 jason jason        607 Jan 15 05:42 load-sample-databases.msh
 -rw-r----- 1 jason jason       1312 May 31 02:16 odbc.ini
 drwxrwxr-x 4 jason jason       4096 May 14 21:50 patches
 -rw-rw-r-- 1 jason jason      14792 Mar 20 17:23 README.md
 -rwxrwxr-x 1 jason jason        523 Jan  9 19:03 restart.sh
 -rwxrwxr-x 1 jason jason         51 Jan  9 18:57 run.sh
 -rw-rw-r-- 1 jason jason       5049 Jan  9 19:48 SimpleJdbcRunner.java
 drwxrwxr-x 2 jason jason       4096 Mar  4 16:23 start_scripts
 -rw-rw-r-- 1 jason jason         80 Jan 14 20:03 start-scripts.sh
 -rw-rw-r-- 1 jason jason        148 May 31 02:19 welcome.sh

Notice the /patches folder. In my case it contains two subfolders:

drwxrwxr-x 2 jason jason 4096 May 14 21:50 000
drwxrwxr-x 2 jason jason 4096 May 31 02:01 031

Inside the 031 folder I have the patches for EPM 11.1.2.4.031 downloaded from Oracle, although I have prepended them with 01-, 02-, 03- and so on in order to force them to be applied in a particular order. There are multiple patches because in total they update the Essbase server itself, EAS, APS, and runtime files. Here is the contents of my 031 folder:

-rw-r--r-- 1 jason jason 61727995 May  7 15:12 01-p29260080_111240_Linux-x86-64.zip
 -rw-r--r-- 1 jason jason  9542502 May  7 15:12 02-p29260133_111240_Generic.zip
 -rw-r--r-- 1 jason jason 71810346 May  7 15:12 03-p29260160_111240_Linux-x86-64.zip
 -rw-r--r-- 1 jason jason 13241287 May  7 15:57 04-p29260067_111240_Linux-x86-64.zip
 -rw-rw-r-- 1 jason jason      629 May 31 02:01 README.txt

The Dockerfile now has a build argument called PATCH_LEVEL that accepts a three-digit code to indicate which folder of patches to apply. So to build the Essbase image for patch level 031, I can run the following:

docker build --build-arg PATCH_LEVEL=031

How we’re using this internally is that we now create the image for any given patch level and then just tag it accordingly. For instance, we can run any given version of Essbase by using a container name of essbase:11.1.2.4.031 or just essbase:latest as the case may be (which currently points to .031).

Out of the box SQL ODBC Configuration

In order for Essbase load rules to load data from a SQL data source (especially on Linux) a little bit of file configuration needs to be performed. This is the odbc.ini file. Now, on startup, this file is configured with the proper database name and credentials, allowing you to use it in a load rule. For the time being I just configured the default database to be the HSS database that is created during configuration. This gives you a working database and configuration that works for testing or playing around and you can use it as the basis of a ‘real’ ODBC connection.

Further Image Size Reductions

The very original version of the Docker Essbase image from two years ago (and from my first presentation on this!) was around 40GB (!). This was the old ‘monolithic’ image that included a full install of an Oracle RDBMS as well as taking an image size hit because of copying over the large Essbase installation zip files. When this project went open source, it was after one of the major improvements was made to it, which was to use docker-compose to separate out the relational database from the Essbase server. Further, I configured the image to use an ‘off the shelf’ Microsoft SQL Server (on Linux) Docker image, which simplified setup/configuration since there isn’t/wasn’t a comparable Oracle Docker image. This got the image size down to 10GB or so. Also as part of the open source project I used Docker’s relatively new ‘multi-stage build’ feature in order to break the image build in to two major pieces. This allowed me to avoid the penalty from copying over the Essbase installer .zip files. All this got the image size down to around 6GB.

From there the image size has slowly been whittled down a bit here and a bit there. The entire system has been consolidated down to a single Java JDK (versus three full copies) and numerous files and directories that are obviously unneeded (probably) have been deleted. In this latest version of the image, a few more things are tossed overboard in the interest of having a smaller image size. The overall image size has been sitting around 4GB for the last few months but I found a few more patch storage locations that aren’t needed. That’s kind of the great thing about Docker: these images are meant to be used and thrown away. So it’s not like we need to keep around some inventory of patches in case we need to rollback (rollback? More like roll out a new image!).

Summary / See You At Kscope

I’ll be presenting on Docker & Essbase at Kscope19 in Seattle later this month. I’ll cover how to get up and running with your own Docker Essbase instance in the easiest way possible, as well as general updates and other fun things going on in this realm. Hope to see you there!

Simple Way to Check if Essbase XMLA Provider Is Working

A colleague of mine (my favorite Oracle infrastructure specialist, Joe Malewicki of iArch Solutions) that specializes in infrastructure contacted me the other day with a somewhat unusual request. Is there an easy way to “prove” that the Essbase XMLA provider is indeed up and running?

Sure – hit it with curl! The following script shows a simple example that will execute an MDX query on Sample.Basic by using curl to post an XMLA request with an MDX query. I have parameterized the URL, username, and password although you could hard-code them if you need to:

#!/bin/bash
   
 URL="http://docker1:9000/aps/XMLA"
 USER=admin
 PASSWORD=password1
 
 curl --header "Content-Type: text/xml;charset=UTF-8" \
 --header "SOAPAction:urn:schemas-microsoft-com:xml-analysis#Execute" \
 --user ${USER}:${PASSWORD} --data @- ${URL} <<EOF
 <SOAP-ENV:Envelope
  xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
  xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance"
  xmlns:xsd="http://www.w3.org/2001/XMLSchema">
 
 <SOAP-ENV:Body>
  <Execute xmlns="urn:schemas-microsoft-com:xml-analysis"
   SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
   <Command>
    <Statement>
     SELECT  CrossJoin([Measures].CHILDREN , [Market].CHILDREN)
     on columns,  [Product].Members on rows
     from Sample.Basic
    </Statement>
   </Command>
   <Properties>
    <PropertyList>
     <DataSourceInfo>
      Provider=Essbase;Data Source=localhost
     </DataSourceInfo>
     <Catalog>Sample</Catalog>
     <Format>Multidimensional</Format>
     <AxisFormat>TupleFormat</AxisFormat>
     <Content>SchemaData</Content>
    </PropertyList></Properties>
   </Execute>
  </SOAP-ENV:Body>
 </SOAP-ENV:Envelope>
 EOF

After a moment, the results come back. This example is a bit long but I’ve included the whole thing for completeness:

?xml version="1.0"?>  <SOAP-ENV:Envelope  xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"  SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">  <SOAP-ENV:Body>  <m:ExecuteResponse   xmlns:m="urn:schemas-microsoft-com:xml-analysis">  <m:return  SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">  <root xmlns="urn:schemas-microsoft-com:xml-analysis:mddataset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">  <xsd:schema xmlns="urn:schemas-microsoft-com:xml-analysis:mddataset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="urn:schemas-microsoft-com:xml-analysis:mddataset" elementFormDefault="qualified">  <xsd:complexType name="MemberType">  <xsd:sequence>  <xsd:element name="UName" type="xsd:string" />   <xsd:element name="Caption" type="xsd:string" />   <xsd:element name="LName" type="xsd:string" />   <xsd:element name="LNum" type="xsd:unsignedInt" />   <xsd:element name="DisplayInfo" type="xsd:unsignedInt" />   <xsd:sequence maxOccurs="unbounded" minOccurs="0">  <xsd:any processContents="lax" maxOccurs="unbounded" />   </xsd:sequence>  </xsd:sequence>  <xsd:attribute name="Hierarchy" type="xsd:string" />   </xsd:complexType>  <xsd:complexType name="PropType">  <xsd:attribute name="name" type="xsd:string" />   </xsd:complexType>  <xsd:complexType name="TupleType">  <xsd:sequence maxOccurs="unbounded">  <xsd:element name="Member" type="MemberType" />   </xsd:sequence>  <xsd:attribute name="Ordinal" type="xsd:unsignedInt" />   </xsd:complexType>  <xsd:complexType name="MembersType">  <xsd:sequence maxOccurs="unbounded">  <xsd:element name="Member" type="MemberType" />   </xsd:sequence>  <xsd:attribute name="Hierarchy" type="xsd:string" />   </xsd:complexType>  <xsd:complexType name="TuplesType">  <xsd:sequence maxOccurs="unbounded">  <xsd:element name="Tuple" type="TupleType" />   </xsd:sequence>  </xsd:complexType>  <xsd:complexType name="CrossProductType">  <xsd:choice minOccurs="0" maxOccurs="unbounded">  <xsd:element name="Members" type="MembersType" />   </xsd:choice>  <xsd:attribute name="Size" type="xsd:unsignedInt" />   </xsd:complexType>  <xsd:complexType name="OlapInfo">  <xsd:sequence maxOccurs="unbounded">  <xsd:element name="AxesInfo">  <xsd:complexType>  <xsd:sequence maxOccurs="unbounded">  <xsd:element name="AxisInfo">  <xsd:complexType>  <xsd:sequence maxOccurs="unbounded">  <xsd:element name="HierarchyInfo">  <xsd:complexType>  <xsd:sequence>  <xsd:sequence maxOccurs="unbounded">  <xsd:element name="UName" type="PropType" />   <xsd:element name="Caption" type="PropType" />   <xsd:element name="LName" type="PropType" />   <xsd:element name="LNum" type="PropType" />   <xsd:element name="DisplayInfo" type="PropType" />   </xsd:sequence>  <xsd:sequence maxOccurs="unbounded" minOccurs="0">  <xsd:any processContents="lax" maxOccurs="unbounded" />   </xsd:sequence>  </xsd:sequence>  <xsd:attribute name="name" type="xsd:string" />   </xsd:complexType>  </xsd:element>  </xsd:sequence>  <xsd:attribute name="name" type="xsd:string" />   </xsd:complexType>  </xsd:element>  </xsd:sequence>  </xsd:complexType>  </xsd:element>  <xsd:element name="CellInfo">  <xsd:complexType>  <xsd:sequence>  <xsd:sequence maxOccurs="unbounded">  <xsd:choice>  <xsd:element name="CellOrdinal" type="PropType" />   <xsd:element name="Value" type="PropType" />   <xsd:element name="FmtValue" type="PropType" />   <xsd:element name="BackColor" type="PropType" />   <xsd:element name="ForeColor" type="PropType" />   </xsd:choice>  </xsd:sequence>  <xsd:sequence maxOccurs="unbounded" minOccurs="0">  <xsd:any processContents="lax" maxOccurs="unbounded" />   </xsd:sequence>  </xsd:sequence>  </xsd:complexType>  </xsd:element>  </xsd:sequence>  </xsd:complexType>  <xsd:complexType name="Axes">  <xsd:sequence maxOccurs="unbounded">  <xsd:element name="Axis">  <xsd:complexType>  <xsd:choice minOccurs="0" maxOccurs="unbounded">  <xsd:element name="CrossProduct" type="CrossProductType" />   <xsd:element name="Tuples" type="TuplesType" />   <xsd:element name="Members" type="MembersType" />   </xsd:choice>  <xsd:attribute name="name" type="xsd:string" />   </xsd:complexType>  </xsd:element>  </xsd:sequence>  </xsd:complexType>  <xsd:complexType name="CellData">  <xsd:sequence maxOccurs="unbounded">  <xsd:element name="Cell">  <xsd:complexType>  <xsd:sequence maxOccurs="unbounded">  <xsd:choice>  <xsd:element name="Value" />   <xsd:element name="FmtValue" type="xsd:string" />   <xsd:element name="BackColor" type="xsd:unsignedInt" />   <xsd:element name="ForeColor" type="xsd:unsignedInt" />   </xsd:choice>  </xsd:sequence>  <xsd:attribute name="CellOrdinal" type="xsd:unsignedInt" />   </xsd:complexType>  </xsd:element>  </xsd:sequence>  </xsd:complexType>  <xsd:element name="root">  <xsd:complexType>  <xsd:sequence maxOccurs="unbounded">  <xsd:element name="OlapInfo" type="OlapInfo" />   <xsd:element name="Axes" type="Axes" />   <xsd:element name="CellData" type="CellData" />   </xsd:sequence>  </xsd:complexType>  </xsd:element>  </xsd:schema>  <OlapInfo>  <CubeInfo>  <Cube>  <CubeName>Sample.Basic</CubeName>  </Cube>  </CubeInfo>  <AxesInfo>  <AxisInfo name="Axis0">  <HierarchyInfo name="[Measures]">  <UName name="[Measures].[MEMBER_UNIQUE_NAME]"/>  <Caption name="[Measures].[MEMBER_CAPTION]"/>  <LName name="[Measures].[LEVEL_UNIQUE_NAME]"/>  <LNum name="[Measures].[LEVEL_NUMBER]"/>  <DisplayInfo name="[Measures].[DISPLAY_INFO]"/>  </HierarchyInfo>  <HierarchyInfo name="[Market]">  <UName name="[Market].[MEMBER_UNIQUE_NAME]"/>  <Caption name="[Market].[MEMBER_CAPTION]"/>  <LName name="[Market].[LEVEL_UNIQUE_NAME]"/>  <LNum name="[Market].[LEVEL_NUMBER]"/>  <DisplayInfo name="[Market].[DISPLAY_INFO]"/>  </HierarchyInfo>  </AxisInfo>  <AxisInfo name="Axis1">  <HierarchyInfo name="[Product]">  <UName name="[Product].[MEMBER_UNIQUE_NAME]"/>  <Caption name="[Product].[MEMBER_CAPTION]"/>  <LName name="[Product].[LEVEL_UNIQUE_NAME]"/>  <LNum name="[Product].[LEVEL_NUMBER]"/>  <DisplayInfo name="[Product].[DISPLAY_INFO]"/>  </HierarchyInfo>  </AxisInfo>  <AxisInfo name="SlicerAxis">  <HierarchyInfo name="[Year]">  <UName name="[Year].[MEMBER_UNIQUE_NAME]"/>  <Caption name="[Year].[MEMBER_CAPTION]"/>  <LName name="[Year].[LEVEL_UNIQUE_NAME]"/>  <LNum name="[Year].[LEVEL_NUMBER]"/>  <DisplayInfo name="[Year].[DISPLAY_INFO]"/>  </HierarchyInfo>  <HierarchyInfo name="[Scenario]">  <UName name="[Scenario].[MEMBER_UNIQUE_NAME]"/>  <Caption name="[Scenario].[MEMBER_CAPTION]"/>  <LName name="[Scenario].[LEVEL_UNIQUE_NAME]"/>  <LNum name="[Scenario].[LEVEL_NUMBER]"/>  <DisplayInfo name="[Scenario].[DISPLAY_INFO]"/>  </HierarchyInfo>  </AxisInfo>  </AxesInfo>  <CellInfo>  <CellOrdinal name="CELL_ORDINAL"/>  <Value name="VALUE"/>  <FmtValue name="FORMATTED_VALUE"/>  <BackColor name="BACK_COLOR"/>  <ForeColor name="FORE_COLOR"/>  </CellInfo>  </OlapInfo>  <Axes>  <Axis name="Axis0">  <Tuples>  <Tuple Ordinal="0">  <Member Hierarchy="[Measures]">  <UName>[Profit]</UName>  <Caption>Profit</Caption>  <LName>[Measures].Levels(1)</LName>  <LNum>1</LNum>  <DisplayInfo>2</DisplayInfo>  </Member>  <Member Hierarchy="[Market]">  <UName>[East]</UName>  <Caption>East</Caption>  <LName>[Market].Levels(1)</LName>  <LNum>1</LNum>  <DisplayInfo>5</DisplayInfo>  </Member>  </Tuple>  <Tuple Ordinal="1">  <Member Hierarchy="[Measures]">  <UName>[Profit]</UName>  <Caption>Profit</Caption>  <LName>[Measures].Levels(1)</LName>  <LNum>1</LNum>  <DisplayInfo>2</DisplayInfo>  </Member>  <Member Hierarchy="[Market]">  <UName>[West]</UName>  <Caption>West</Caption>  <LName>[Market].Levels(1)</LName>  <LNum>1</LNum>  <DisplayInfo>5</DisplayInfo>  </Member>  </Tuple>  <Tuple Ordinal="2">  <Member Hierarchy="[Measures]">  <UName>[Profit]</UName>  <Caption>Profit</Caption>  <LName>[Measures].Levels(1)</LName>  <LNum>1</LNum>  <DisplayInfo>2</DisplayInfo>  </Member>  <Member Hierarchy="[Market]">  <UName>[South]</UName>  <Caption>South</Caption>  <LName>[Market].Levels(1)</LName>  <LNum>1</LNum>  <DisplayInfo>4</DisplayInfo>  </Member>  </Tuple>  <Tuple Ordinal="3">  <Member Hierarchy="[Measures]">  <UName>[Profit]</UName>  <Caption>Profit</Caption>  <LName>[Measures].Levels(1)</LName>  <LNum>1</LNum>  <DisplayInfo>2</DisplayInfo>  </Member>  <Member Hierarchy="[Market]">  <UName>[Central]</UName>  <Caption>Central</Caption>  <LName>[Market].Levels(1)</LName>  <LNum>1</LNum>  <DisplayInfo>6</DisplayInfo>  </Member>  </Tuple>  <Tuple Ordinal="4">  <Member Hierarchy="[Measures]">  <UName>[Inventory]</UName>  <Caption>Inventory</Caption>  <LName>[Measures].Levels(1)</LName>  <LNum>1</LNum>  <DisplayInfo>3</DisplayInfo>  </Member>  <Member Hierarchy="[Market]">  <UName>[East]</UName>  <Caption>East</Caption>  <LName>[Market].Levels(1)</LName>  <LNum>1</LNum>  <DisplayInfo>5</DisplayInfo>  </Member>  </Tuple>  <Tuple Ordinal="5">  <Member Hierarchy="[Measures]">  <UName>[Inventory]</UName>  <Caption>Inventory</Caption>  <LName>[Measures].Levels(1)</LName>  <LNum>1</LNum>  <DisplayInfo>3</DisplayInfo>  </Member>  <Member Hierarchy="[Market]">  <UName>[West]</UName>  <Caption>West</Caption>  <LName>[Market].Levels(1)</LName>  <LNum>1</LNum>  <DisplayInfo>5</DisplayInfo>  </Member>  </Tuple>  <Tuple Ordinal="6">  <Member Hierarchy="[Measures]">  <UName>[Inventory]</UName>  <Caption>Inventory</Caption>  <LName>[Measures].Levels(1)</LName>  <LNum>1</LNum>  <DisplayInfo>3</DisplayInfo>  </Member>  <Member Hierarchy="[Market]">  <UName>[South]</UName>  <Caption>South</Caption>  <LName>[Market].Levels(1)</LName>  <LNum>1</LNum>  <DisplayInfo>4</DisplayInfo>  </Member>  </Tuple>  <Tuple Ordinal="7">  <Member Hierarchy="[Measures]">  <UName>[Inventory]</UName>  <Caption>Inventory</Caption>  <LName>[Measures].Levels(1)</LName>  <LNum>1</LNum>  <DisplayInfo>3</DisplayInfo>  </Member>  <Member Hierarchy="[Market]">  <UName>[Central]</UName>  <Caption>Central</Caption>  <LName>[Market].Levels(1)</LName>  <LNum>1</LNum>  <DisplayInfo>6</DisplayInfo>  </Member>  </Tuple>  <Tuple Ordinal="8">  <Member Hierarchy="[Measures]">  <UName>[Ratios]</UName>  <Caption>Ratios</Caption>  <LName>[Measures].Levels(1)</LName>  <LNum>1</LNum>  <DisplayInfo>3</DisplayInfo>  </Member>  <Member Hierarchy="[Market]">  <UName>[East]</UName>  <Caption>East</Caption>  <LName>[Market].Levels(1)</LName>  <LNum>1</LNum>  <DisplayInfo>5</DisplayInfo>  </Member>  </Tuple>  <Tuple Ordinal="9">  <Member Hierarchy="[Measures]">  <UName>[Ratios]</UName>  <Caption>Ratios</Caption>  <LName>[Measures].Levels(1)</LName>  <LNum>1</LNum>  <DisplayInfo>3</DisplayInfo>  </Member>  <Member Hierarchy="[Market]">  <UName>[West]</UName>  <Caption>West</Caption>  <LName>[Market].Levels(1)</LName>  <LNum>1</LNum>  <DisplayInfo>5</DisplayInfo>  </Member>  </Tuple>  <Tuple Ordinal="10">  <Member Hierarchy="[Measures]">  <UName>[Ratios]</UName>  <Caption>Ratios</Caption>  <LName>[Measures].Levels(1)</LName>  <LNum>1</LNum>  <DisplayInfo>3</DisplayInfo>  </Member>  <Member Hierarchy="[Market]">  <UName>[South]</UName>  <Caption>South</Caption>  <LName>[Market].Levels(1)</LName>  <LNum>1</LNum>  <DisplayInfo>4</DisplayInfo>  </Member>  </Tuple>  <Tuple Ordinal="11">  <Member Hierarchy="[Measures]">  <UName>[Ratios]</UName>  <Caption>Ratios</Caption>  <LName>[Measures].Levels(1)</LName>  <LNum>1</LNum>  <DisplayInfo>3</DisplayInfo>  </Member>  <Member Hierarchy="[Market]">  <UName>[Central]</UName>  <Caption>Central</Caption>  <LName>[Market].Levels(1)</LName>  <LNum>1</LNum>  <DisplayInfo>6</DisplayInfo>  </Member>  </Tuple>  </Tuples>  </Axis>  <Axis name="Axis1">  <Tuples>  <Tuple Ordinal="0">  <Member Hierarchy="[Product]">  <UName>[Product]</UName>  <Caption>Product</Caption>  <LName>[Product].Levels(0)</LName>  <LNum>0</LNum>  <DisplayInfo>5</DisplayInfo>  </Member>  </Tuple>  <Tuple Ordinal="1">  <Member Hierarchy="[Product]">  <UName>[100]</UName>  <Caption>Colas</Caption>  <LName>[Product].[Family]</LName>  <LNum>1</LNum>  <DisplayInfo>3</DisplayInfo>  </Member>  </Tuple>  <Tuple Ordinal="2">  <Member Hierarchy="[Product]">  <UName>[100-10]</UName>  <Caption>Cola</Caption>  <LName>[Product].[SKU]</LName>  <LNum>2</LNum>  <DisplayInfo>0</DisplayInfo>  </Member>  </Tuple>  <Tuple Ordinal="3">  <Member Hierarchy="[Product]">  <UName>[100-20]</UName>  <Caption>Diet Cola</Caption>  <LName>[Product].[SKU]</LName>  <LNum>2</LNum>  <DisplayInfo>0</DisplayInfo>  </Member>  </Tuple>  <Tuple Ordinal="4">  <Member Hierarchy="[Product]">  <UName>[100-30]</UName>  <Caption>Caffeine Free Cola</Caption>  <LName>[Product].[SKU]</LName>  <LNum>2</LNum>  <DisplayInfo>0</DisplayInfo>  </Member>  </Tuple>  <Tuple Ordinal="5">  <Member Hierarchy="[Product]">  <UName>[200]</UName>  <Caption>Root Beer</Caption>  <LName>[Product].[Family]</LName>  <LNum>1</LNum>  <DisplayInfo>4</DisplayInfo>  </Member>  </Tuple>  <Tuple Ordinal="6">  <Member Hierarchy="[Product]">  <UName>[200-10]</UName>  <Caption>Old Fashioned</Caption>  <LName>[Product].[SKU]</LName>  <LNum>2</LNum>  <DisplayInfo>0</DisplayInfo>  </Member>  </Tuple>  <Tuple Ordinal="7">  <Member Hierarchy="[Product]">  <UName>[200-20]</UName>  <Caption>Diet Root Beer</Caption>  <LName>[Product].[SKU]</LName>  <LNum>2</LNum>  <DisplayInfo>0</DisplayInfo>  </Member>  </Tuple>  <Tuple Ordinal="8">  <Member Hierarchy="[Product]">  <UName>[200-30]</UName>  <Caption>Sasparilla</Caption>  <LName>[Product].[SKU]</LName>  <LNum>2</LNum>  <DisplayInfo>0</DisplayInfo>  </Member>  </Tuple>  <Tuple Ordinal="9">  <Member Hierarchy="[Product]">  <UName>[200-40]</UName>  <Caption>Birch Beer</Caption>  <LName>[Product].[SKU]</LName>  <LNum>2</LNum>  <DisplayInfo>0</DisplayInfo>  </Member>  </Tuple>  <Tuple Ordinal="10">  <Member Hierarchy="[Product]">  <UName>[300]</UName>  <Caption>Cream Soda</Caption>  <LName>[Product].[Family]</LName>  <LNum>1</LNum>  <DisplayInfo>3</DisplayInfo>  </Member>  </Tuple>  <Tuple Ordinal="11">  <Member Hierarchy="[Product]">  <UName>[300-10]</UName>  <Caption>Dark Cream</Caption>  <LName>[Product].[SKU]</LName>  <LNum>2</LNum>  <DisplayInfo>0</DisplayInfo>  </Member>  </Tuple>  <Tuple Ordinal="12">  <Member Hierarchy="[Product]">  <UName>[300-20]</UName>  <Caption>Vanilla Cream</Caption>  <LName>[Product].[SKU]</LName>  <LNum>2</LNum>  <DisplayInfo>0</DisplayInfo>  </Member>  </Tuple>  <Tuple Ordinal="13">  <Member Hierarchy="[Product]">  <UName>[300-30]</UName>  <Caption>Diet Cream</Caption>  <LName>[Product].[SKU]</LName>  <LNum>2</LNum>  <DisplayInfo>0</DisplayInfo>  </Member>  </Tuple>  <Tuple Ordinal="14">  <Member Hierarchy="[Product]">  <UName>[400]</UName>  <Caption>Fruit Soda</Caption>  <LName>[Product].[Family]</LName>  <LNum>1</LNum>  <DisplayInfo>3</DisplayInfo>  </Member>  </Tuple>  <Tuple Ordinal="15">  <Member Hierarchy="[Product]">  <UName>[400-10]</UName>  <Caption>Grape</Caption>  <LName>[Product].[SKU]</LName>  <LNum>2</LNum>  <DisplayInfo>0</DisplayInfo>  </Member>  </Tuple>  <Tuple Ordinal="16">  <Member Hierarchy="[Product]">  <UName>[400-20]</UName>  <Caption>Orange</Caption>  <LName>[Product].[SKU]</LName>  <LNum>2</LNum>  <DisplayInfo>0</DisplayInfo>  </Member>  </Tuple>  <Tuple Ordinal="17">  <Member Hierarchy="[Product]">  <UName>[400-30]</UName>  <Caption>Strawberry</Caption>  <LName>[Product].[SKU]</LName>  <LNum>2</LNum>  <DisplayInfo>0</DisplayInfo>  </Member>  </Tuple>  <Tuple Ordinal="18">  <Member Hierarchy="[Product]">  <UName>[Diet]</UName>  <Caption>Diet Drinks</Caption>  <LName>[Product].[Family]</LName>  <LNum>1</LNum>  <DisplayInfo>3</DisplayInfo>  </Member>  </Tuple>  <Tuple Ordinal="19">  <Member Hierarchy="[Product]">  <UName>[100-20]</UName>  <Caption>Diet Cola</Caption>  <LName>[Product].[SKU]</LName>  <LNum>2</LNum>  <DisplayInfo>0</DisplayInfo>  </Member>  </Tuple>  <Tuple Ordinal="20">  <Member Hierarchy="[Product]">  <UName>[200-20]</UName>  <Caption>Diet Root Beer</Caption>  <LName>[Product].[SKU]</LName>  <LNum>2</LNum>  <DisplayInfo>0</DisplayInfo>  </Member>  </Tuple>  <Tuple Ordinal="21">  <Member Hierarchy="[Product]">  <UName>[300-30]</UName>  <Caption>Diet Cream</Caption>  <LName>[Product].[SKU]</LName>  <LNum>2</LNum>  <DisplayInfo>0</DisplayInfo>  </Member>  </Tuple>  </Tuples>  </Axis>  <Axis name="SlicerAxis">  <Tuples>  <Tuple Ordinal="0">  <Member Hierarchy="[Year]">  <UName>[Year]</UName>  <Caption>Year</Caption>  <LName>[Year].Levels(0)</LName>  <LNum>0</LNum>  <DisplayInfo>4</DisplayInfo>  </Member>  <Member Hierarchy="[Scenario]">  <UName>[Scenario]</UName>  <Caption>Scenario</Caption>  <LName>[Scenario].Levels(0)</LName>  <LNum>0</LNum>  <DisplayInfo>4</DisplayInfo>  </Member>  </Tuple>  </Tuples>  </Axis>  </Axes>  <CellData>  <Cell CellOrdinal="0">  <Value>24161.000000</Value>  <FmtValue>24161.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="1">  <Value>29861.000000</Value>  <FmtValue>29861.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="2">  <Value>13238.000000</Value>  <FmtValue>13238.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="3">  <Value>38262.000000</Value>  <FmtValue>38262.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="4">  <Value>25744.000000</Value>  <FmtValue>25744.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="5">  <Value>38751.000000</Value>  <FmtValue>38751.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="6">  <Value>15285.000000</Value>  <FmtValue>15285.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="7">  <Value>37625.000000</Value>  <FmtValue>37625.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="8">  <Value>56.604270</Value>  <FmtValue>56.60</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="9">  <Value>52.678457</Value>  <FmtValue>52.68</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="10">  <Value>56.830429</Value>  <FmtValue>56.83</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="11">  <Value>56.389574</Value>  <FmtValue>56.39</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="12">  <Value>12656.000000</Value>  <FmtValue>12656.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="13">  <Value>3549.000000</Value>  <FmtValue>3549.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="14">  <Value>4773.000000</Value>  <FmtValue>4773.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="15">  <Value>9490.000000</Value>  <FmtValue>9490.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="16">  <Value>5384.000000</Value>  <FmtValue>5384.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="17">  <Value>8592.000000</Value>  <FmtValue>8592.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="18">  <Value>5483.000000</Value>  <FmtValue>5483.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="19">  <Value>9989.000000</Value>  <FmtValue>9989.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="20">  <Value>66.395097</Value>  <FmtValue>66.40</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="21">  <Value>49.950541</Value>  <FmtValue>49.95</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="22">  <Value>53.464373</Value>  <FmtValue>53.46</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="23">  <Value>57.752603</Value>  <FmtValue>57.75</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="24">  <Value>11129.000000</Value>  <FmtValue>11129.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="25">  <Value>4593.000000</Value>  <FmtValue>4593.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="26">  <Value>3576.000000</Value>  <FmtValue>3576.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="27">  <Value>3479.000000</Value>  <FmtValue>3479.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="28">  <Value>4643.000000</Value>  <FmtValue>4643.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="29">  <Value>3348.000000</Value>  <FmtValue>3348.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="30">  <Value>2737.000000</Value>  <FmtValue>2737.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="31">  <Value>3859.000000</Value>  <FmtValue>3859.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="32">  <Value>67.925016</Value>  <FmtValue>67.93</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="33">  <Value>59.231597</Value>  <FmtValue>59.23</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="34">  <Value>55.988306</Value>  <FmtValue>55.99</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="35">  <Value>57.401258</Value>  <FmtValue>57.40</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="36">  <Value>1114.000000</Value>  <FmtValue>1114.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="37">  <Value>-534.000000</Value>  <FmtValue>-534.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="38">  <Value>1197.000000</Value>  <FmtValue>1197.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="39">  <Value>3931.000000</Value>  <FmtValue>3931.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="40">  <Value>500.000000</Value>  <FmtValue>500.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="41">  <Value>3236.000000</Value>  <FmtValue>3236.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="42">  <Value>2746.000000</Value>  <FmtValue>2746.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="43">  <Value>3884.000000</Value>  <FmtValue>3884.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="44">  <Value>58.181226</Value>  <FmtValue>58.18</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="45">  <Value>41.544324</Value>  <FmtValue>41.54</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="46">  <Value>48.749119</Value>  <FmtValue>48.75</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="47">  <Value>58.006561</Value>  <FmtValue>58.01</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="48">  <Value>413.000000</Value>  <FmtValue>413.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="49">  <Value>-510.000000</Value>  <FmtValue>-510.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="51">  <Value>2080.000000</Value>  <FmtValue>2080.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="52">  <Value>241.000000</Value>  <FmtValue>241.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="53">  <Value>2008.000000</Value>  <FmtValue>2008.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="55">  <Value>2246.000000</Value>  <FmtValue>2246.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="56">  <Value>59.372870</Value>  <FmtValue>59.37</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="57">  <Value>36.031851</Value>  <FmtValue>36.03</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="59">  <Value>58.003794</Value>  <FmtValue>58.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="60">  <Value>2534.000000</Value>  <FmtValue>2534.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="61">  <Value>9727.000000</Value>  <FmtValue>9727.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="62">  <Value>6115.000000</Value>  <FmtValue>6115.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="63">  <Value>9578.000000</Value>  <FmtValue>9578.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="64">  <Value>5957.000000</Value>  <FmtValue>5957.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="65">  <Value>11755.000000</Value>  <FmtValue>11755.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="66">  <Value>5336.000000</Value>  <FmtValue>5336.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="67">  <Value>9952.000000</Value>  <FmtValue>9952.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="68">  <Value>51.537682</Value>  <FmtValue>51.54</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="69">  <Value>55.719298</Value>  <FmtValue>55.72</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="70">  <Value>59.233006</Value>  <FmtValue>59.23</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="71">  <Value>55.789906</Value>  <FmtValue>55.79</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="72">  <Value>-2540.000000</Value>  <FmtValue>-2540.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="73">  <Value>1656.000000</Value>  <FmtValue>1656.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="74">  <Value>2116.000000</Value>  <FmtValue>2116.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="75">  <Value>5969.000000</Value>  <FmtValue>5969.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="76">  <Value>2494.000000</Value>  <FmtValue>2494.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="77">  <Value>2817.000000</Value>  <FmtValue>2817.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="78">  <Value>1894.000000</Value>  <FmtValue>1894.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="79">  <Value>5420.000000</Value>  <FmtValue>5420.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="80">  <Value>42.774708</Value>  <FmtValue>42.77</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="81">  <Value>57.303736</Value>  <FmtValue>57.30</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="82">  <Value>60.008666</Value>  <FmtValue>60.01</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="83">  <Value>55.770905</Value>  <FmtValue>55.77</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="84">  <Value>982.000000</Value>  <FmtValue>982.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="85">  <Value>5013.000000</Value>  <FmtValue>5013.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="86">  <Value>2421.000000</Value>  <FmtValue>2421.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="87">  <Value>3609.000000</Value>  <FmtValue>3609.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="88">  <Value>744.000000</Value>  <FmtValue>744.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="89">  <Value>4964.000000</Value>  <FmtValue>4964.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="90">  <Value>1693.000000</Value>  <FmtValue>1693.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="91">  <Value>4532.000000</Value>  <FmtValue>4532.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="92">  <Value>57.320625</Value>  <FmtValue>57.32</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="93">  <Value>55.245036</Value>  <FmtValue>55.25</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="94">  <Value>59.338909</Value>  <FmtValue>59.34</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="95">  <Value>55.817255</Value>  <FmtValue>55.82</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="97">  <Value>3058.000000</Value>  <FmtValue>3058.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="98">  <Value>1578.000000</Value>  <FmtValue>1578.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="101">  <Value>3974.000000</Value>  <FmtValue>3974.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="102">  <Value>1749.000000</Value>  <FmtValue>1749.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="105">  <Value>54.966027</Value>  <FmtValue>54.97</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="106">  <Value>58.416104</Value>  <FmtValue>58.42</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="108">  <Value>4092.000000</Value>  <FmtValue>4092.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="112">  <Value>2719.000000</Value>  <FmtValue>2719.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="116">  <Value>55.753191</Value>  <FmtValue>55.75</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="120">  <Value>2627.000000</Value>  <FmtValue>2627.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="121">  <Value>10731.000000</Value>  <FmtValue>10731.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="122">  <Value>2350.000000</Value>  <FmtValue>2350.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="123">  <Value>10091.000000</Value>  <FmtValue>10091.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="124">  <Value>6278.000000</Value>  <FmtValue>6278.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="125">  <Value>8880.000000</Value>  <FmtValue>8880.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="126">  <Value>4466.000000</Value>  <FmtValue>4466.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="127">  <Value>9241.000000</Value>  <FmtValue>9241.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="128">  <Value>45.980930</Value>  <FmtValue>45.98</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="129">  <Value>56.367438</Value>  <FmtValue>56.37</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="130">  <Value>56.983596</Value>  <FmtValue>56.98</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="131">  <Value>55.962667</Value>  <FmtValue>55.96</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="132">  <Value>3233.000000</Value>  <FmtValue>3233.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="133">  <Value>5354.000000</Value>  <FmtValue>5354.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="134">  <Value>1056.000000</Value>  <FmtValue>1056.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="135">  <Value>2552.000000</Value>  <FmtValue>2552.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="136">  <Value>2760.000000</Value>  <FmtValue>2760.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="137">  <Value>3599.000000</Value>  <FmtValue>3599.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="138">  <Value>2374.000000</Value>  <FmtValue>2374.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="139">  <Value>3789.000000</Value>  <FmtValue>3789.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="140">  <Value>55.886333</Value>  <FmtValue>55.89</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="141">  <Value>56.679112</Value>  <FmtValue>56.68</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="142">  <Value>57.171062</Value>  <FmtValue>57.17</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="143">  <Value>53.568773</Value>  <FmtValue>53.57</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="144">  <Value>-918.000000</Value>  <FmtValue>-918.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="145">  <Value>1769.000000</Value>  <FmtValue>1769.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="147">  <Value>1660.000000</Value>  <FmtValue>1660.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="148">  <Value>2895.000000</Value>  <FmtValue>2895.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="149">  <Value>1756.000000</Value>  <FmtValue>1756.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="151">  <Value>1157.000000</Value>  <FmtValue>1157.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="152">  <Value>21.585678</Value>  <FmtValue>21.59</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="153">  <Value>50.674237</Value>  <FmtValue>50.67</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="155">  <Value>58.621444</Value>  <FmtValue>58.62</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="156">  <Value>312.000000</Value>  <FmtValue>312.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="157">  <Value>3608.000000</Value>  <FmtValue>3608.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="158">  <Value>1294.000000</Value>  <FmtValue>1294.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="159">  <Value>5879.000000</Value>  <FmtValue>5879.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="160">  <Value>623.000000</Value>  <FmtValue>623.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="161">  <Value>3525.000000</Value>  <FmtValue>3525.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="162">  <Value>2092.000000</Value>  <FmtValue>2092.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="163">  <Value>4295.000000</Value>  <FmtValue>4295.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="164">  <Value>56.517691</Value>  <FmtValue>56.52</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="165">  <Value>59.172447</Value>  <FmtValue>59.17</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="166">  <Value>56.740443</Value>  <FmtValue>56.74</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="167">  <Value>56.723511</Value>  <FmtValue>56.72</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="168">  <Value>6344.000000</Value>  <FmtValue>6344.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="169">  <Value>5854.000000</Value>  <FmtValue>5854.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="171">  <Value>9103.000000</Value>  <FmtValue>9103.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="172">  <Value>8125.000000</Value>  <FmtValue>8125.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="173">  <Value>9524.000000</Value>  <FmtValue>9524.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="175">  <Value>8443.000000</Value>  <FmtValue>8443.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="176">  <Value>60.628771</Value>  <FmtValue>60.63</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="177">  <Value>48.187475</Value>  <FmtValue>48.19</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="179">  <Value>55.959463</Value>  <FmtValue>55.96</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="180">  <Value>2649.000000</Value>  <FmtValue>2649.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="181">  <Value>4800.000000</Value>  <FmtValue>4800.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="183">  <Value>4395.000000</Value>  <FmtValue>4395.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="184">  <Value>4105.000000</Value>  <FmtValue>4105.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="185">  <Value>3204.000000</Value>  <FmtValue>3204.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="187">  <Value>3490.000000</Value>  <FmtValue>3490.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="188">  <Value>59.437518</Value>  <FmtValue>59.44</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="189">  <Value>58.522202</Value>  <FmtValue>58.52</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="191">  <Value>55.274518</Value>  <FmtValue>55.27</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="192">  <Value>1388.000000</Value>  <FmtValue>1388.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="193">  <Value>4254.000000</Value>  <FmtValue>4254.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="195">  <Value>4209.000000</Value>  <FmtValue>4209.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="196">  <Value>777.000000</Value>  <FmtValue>777.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="197">  <Value>3152.000000</Value>  <FmtValue>3152.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="199">  <Value>4133.000000</Value>  <FmtValue>4133.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="200">  <Value>59.252278</Value>  <FmtValue>59.25</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="201">  <Value>55.265513</Value>  <FmtValue>55.27</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="203">  <Value>56.576902</Value>  <FmtValue>56.58</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="204">  <Value>2307.000000</Value>  <FmtValue>2307.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="205">  <Value>-3200.000000</Value>  <FmtValue>-3200.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="207">  <Value>499.000000</Value>  <FmtValue>499.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="208">  <Value>3243.000000</Value>  <FmtValue>3243.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="209">  <Value>3168.000000</Value>  <FmtValue>3168.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="211">  <Value>820.000000</Value>  <FmtValue>820.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="212">  <Value>62.853107</Value>  <FmtValue>62.85</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="213">  <Value>16.355450</Value>  <FmtValue>16.36</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="215">  <Value>56.048704</Value>  <FmtValue>56.05</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="216">  <Value>2408.000000</Value>  <FmtValue>2408.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="217">  <Value>8087.000000</Value>  <FmtValue>8087.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="218">  <Value>4912.000000</Value>  <FmtValue>4912.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="219">  <Value>13419.000000</Value>  <FmtValue>13419.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="220">  <Value>1867.000000</Value>  <FmtValue>1867.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="221">  <Value>11725.000000</Value>  <FmtValue>11725.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="222">  <Value>6531.000000</Value>  <FmtValue>6531.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="223">  <Value>12711.000000</Value>  <FmtValue>12711.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="224">  <Value>57.545145</Value>  <FmtValue>57.55</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="225">  <Value>53.241084</Value>  <FmtValue>53.24</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="226">  <Value>55.359820</Value>  <FmtValue>55.36</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="227">  <Value>56.854196</Value>  <FmtValue>56.85</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="228">  <Value>1114.000000</Value>  <FmtValue>1114.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="229">  <Value>-534.000000</Value>  <FmtValue>-534.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="230">  <Value>1197.000000</Value>  <FmtValue>1197.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="231">  <Value>3931.000000</Value>  <FmtValue>3931.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="232">  <Value>500.000000</Value>  <FmtValue>500.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="233">  <Value>3236.000000</Value>  <FmtValue>3236.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="234">  <Value>2746.000000</Value>  <FmtValue>2746.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="235">  <Value>3884.000000</Value>  <FmtValue>3884.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="236">  <Value>58.181226</Value>  <FmtValue>58.18</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="237">  <Value>41.544324</Value>  <FmtValue>41.54</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="238">  <Value>48.749119</Value>  <FmtValue>48.75</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="239">  <Value>58.006561</Value>  <FmtValue>58.01</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="240">  <Value>982.000000</Value>  <FmtValue>982.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="241">  <Value>5013.000000</Value>  <FmtValue>5013.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="242">  <Value>2421.000000</Value>  <FmtValue>2421.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="243">  <Value>3609.000000</Value>  <FmtValue>3609.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="244">  <Value>744.000000</Value>  <FmtValue>744.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="245">  <Value>4964.000000</Value>  <FmtValue>4964.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="246">  <Value>1693.000000</Value>  <FmtValue>1693.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="247">  <Value>4532.000000</Value>  <FmtValue>4532.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="248">  <Value>57.320625</Value>  <FmtValue>57.32</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="249">  <Value>55.245036</Value>  <FmtValue>55.25</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="250">  <Value>59.338909</Value>  <FmtValue>59.34</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="251">  <Value>55.817255</Value>  <FmtValue>55.82</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="252">  <Value>312.000000</Value>  <FmtValue>312.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="253">  <Value>3608.000000</Value>  <FmtValue>3608.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="254">  <Value>1294.000000</Value>  <FmtValue>1294.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="255">  <Value>5879.000000</Value>  <FmtValue>5879.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="256">  <Value>623.000000</Value>  <FmtValue>623.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="257">  <Value>3525.000000</Value>  <FmtValue>3525.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="258">  <Value>2092.000000</Value>  <FmtValue>2092.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="259">  <Value>4295.000000</Value>  <FmtValue>4295.00</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="260">  <Value>56.517691</Value>  <FmtValue>56.52</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="261">  <Value>59.172447</Value>  <FmtValue>59.17</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="262">  <Value>56.740443</Value>  <FmtValue>56.74</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  <Cell CellOrdinal="263">  <Value>56.723511</Value>  <FmtValue>56.72</FmtValue>  <BackColor>16777215</BackColor>  <ForeColor>0</ForeColor>  </Cell>  </CellData>  </root></m:return>  </m:ExecuteResponse>  </SOAP-ENV:Body>  </SOAP-ENV:Envelope>

Oracle Essbase on Docker: What, How, Why

I am very pleased to announce the availability of the new open source docker-essbase project to use Essbase with Docker. I have been working with Docker over the last couple of years and have found it to be a very compelling technology. I gave a presentation on Docker & Essbase, and this year I will be presenting Docker & Essbase at Collaborate19 in April and at Kscope19 in June.

The docker-essbase project is an open source project licensed under the very friendly MIT open source license. You are free to use, copy, and modify the files in this project.

First of all, a little background on Docker: Docker is a “container” technology. You’re most likely already familiar with virtual machines that emulate an entire operating system. You can think of Docker as a virtual machine for a single application. If you’ve ever worked with VMs then you might see some immediate appeal in this approach. When I first came into contact with Docker, Essbase immediately came to mind as a use case because of all of the quirks I’ve experienced using Essbase VMs.

Continue Reading…

A REST API Primer for EPM Users & Developers

There’s a lot of excitement in the EPM world these days when it comes to REST APIs – and rightfully so. As a developer heavily invested in the EPM space I am excited about some of the possibilities these new APIs offer – and what they will offer in the future. But all of this great new REST API stuff can be quite daunting – how does it work, why should you care, where does it fit in with your overall architecture, and so on. And with ODTUG‘s Kscope18 just around the corner I thought it might be useful to write a primer – or a crash course of sorts – for the EPM professional on what all this REST API business is about. Also be sure to check out one of my presentations at Kscope this year as I will be discussing the OAC Essbase REST API, how to use it, what it does, and more. Continue Reading…

PBCS Scripting with Groovy using the PBJ REST API Library

I was talking to a colleague the other day that wants to do some scripting with PBCS using Groovy. Of course, since PBCS has a REST API, we can do scripting with pretty much any modern language. There are even some excellent examples of scripting with PBCS using Groovy out there.

However, since Groovy runs on the JVM (Java Virtual Machine), we can actually leverage any existing Java library that we want to – including the already existing PBJ library that provides a super clean domain specific language for working with PBCS via its REST API. To make things nice and simple, PBJ can even be packaged as an “uber jar” – a self-contained JAR that contains all of its dependency JARs. This can make things a little simpler to manage, especially in cases where PBJ is used in places like ODI.

For this example I’m going to take the PBJ library uber jar, add it to a new Groovy project (in the IntelliJ IDE), then write some code to connect, fetch the list of applications, then iterate over those and print out the list of jobs in each application.

Continue Reading…

Evolution of Essbase: new URL-based drill-through showed up in 11.1.1.3

Continuing on with the idea of getting insight into the Essbase feature set over time, as viewed through the lens of its Essbase Java API evolution, you can quite clearly see that the open/URL-style drill-through (as opposed to classic LRO-based drill-through) showed up in version 11.1.1.3, which in fact is pretty much the only thing that seemed to get added to this particular release, Java API-wise, along with some ancillary drill-through methods/functionality in some related classes.

More near to my heart: this is the exact functionality that paved the way for Drillbridge! Although it wasn’t available as a feature on day 1, subsequent versions of Drillbridge gained the ability to automatically deploy drill-through definitions to a given cube, and it uses exactly these API methods to accomplish it.

Speed up ASO SQL data loads by using multiple rules files

Just another quick post today about possibly speeding up data loads to an ASO database when loading from SQL. I got on a quick call with a former colleague that was looking to gain a little more performance on their load process to a massive ASO database, and the first thing that jumped out at me was that I recall you can do parallel loads with some native MaxL syntax.

Here’s a quick example of the syntax:


import database $APPLICATION.$DATABASE data
connect as $SQL_USER identified by $SQL_PW
using multiple rules_file $RULE1, $RULE2, $RULE3, $RULE4, $RULE5
to load_buffer_block starting with buffer_id 100 on error write to "errors.txt";

Basically, you provide multiple rules files (configured for your SQL datasource of course). The rules files are likely to be the same as each other but I suppose it’s possible you might want to partition the data in some logical way to try and speed things up even more.

For example, let’s say that in the code above, we are loading five years of data from a relational database. We might then make it so that each rule is set for this particular year by doing the following things:

  • Set the year in the data header
  • Remove that column from the list of SELECT columns
  • Put a filter/predicate in the WHERE clause on the query
  • Bonus points for using substitution variables in both the header definition and the where clause

Performance in this particular use case went up substantially. It’s my understanding that data loads that were taking an hour are now cut down to 17 minutes. Your mileage may vary, of course.

Let’s Not Forget About Hybrid BSO

That said, I think this can be an effective strategy for trying to squeeze performance out of some ASO cubes that need a smaller load window and you don’t want to go changing a lot of the internals in play. If you’re doing new development, then I strongly, strongly recommend using hybrid BSO (or rather, BSO and making sure the cube is configured properly so as to get the hybrid BSO performance benefits). I have been seeing hybrid BSO cubes absolutely killing it in performance, what with their ability to leverage ASO technology for aggregates, and massive calculation improvements owing to the smaller block sizes and indexes you get from having so many dynamic calc members in dimensions. Plus, you of course get all of the classic/rich/awesome BSO functionality out of the box, like dynamic time series, expense tagging, time balance, and more. These were never very strong areas for ASO and often required a lot of non-optimal workarounds to make users happy.

New webpage for Essbase Java API evolution

A fair bit of my job is dealing with and building solutions around the Essbase Java API. For many years, the Java API has been the premier way to programmatically work with Essbase (compared to say, the C and VB APIs, which have fallen out of favor). As part of this development work, it’s often important to see when (in terms of version) a certain class, method, interface, or other object has been added, modified, removed, or deprecated.

As a bit of a side project, I have been working with a library for comparing Java JARs to each other (japicmp). By processing and interpreting the results of just about every single Essbase Java JAR from 7.0.1, through the 9.x series, multiple 11.x’s, and finally to version 12.2.x, I have come up with something of a master table that shows all of these changes. You can view the initial results of the Essbase JAPI JAR evolution analysis. I’ll probably refresh this and enhance the output as new library versions become available or as I determine that additional insights become useful.

Screenshot from the Essbase Java API evolution analyzer

Continue Reading…

New Drillbridge Plus feature: fetch attributes!

Drillbridge Plus has recently gained a new feature at the request of a customer. This one is kind of interesting and required a bit of deep thinking in terms of the best way to architect it. Here’s the deal: Smart View will let you drill-through on a data value where your grid is using attribute dimensions, but it won’t pass the attribute associations as part of the request. And as it turns out, there are instances where it’d be useful to have that attribute member so you can use it to dial in the SQL query that Drillbridge creates and executes.

What to do? Ask Drillbridge to go fetch those attribute member values for you anyway! In this post I’m going to walk through a use-case showing off the new feature, how to set it up, and I’m also going to show off some recent debugging enhancements that are really useful and have been around for awhile.

Let’s start. First, consider a normal Drillbridge report definition with a simple query:

A normal Drillbridge report definition (before adding attributes)

Continue Reading…