Oracle OBIEE: Use Excel files as datasource

This post explains how to use Excel files as datasources for Oracle Business Intelligence repository.
As you know OBIEE supports a wide range of datasources to be imported in repositories and then processed for analytics. These comprise (well, obviously) Oracle Database, Essbase, SQL, and last but not least Excel.

To use Excel files as datasource a little modification is required to allow BI Administration Tool to properly recognize datasource tables and columns.

Let's pretend this is our source file.

As you may notice there are 2 tables with 3 columns each.

ID_TBL1, FIELD1_TBL1, FIELD2_TBL1 for TABLE1
ID_TBL2, FIELD1_TBL2, FIELD2_TBL2 for TABLE2

To allow OBIEE to correctly recognize these tables and rows we need to define the sheet area dedicated to each one by dragging cell delimiter to the area of our interest, right clicking a cell within this area and select Define Name. Credits for this go to John Minkjan and his blog post.

We then assign a name to the table. In this example I called it TABLE1.

Repeat the same for TABLE2.

Then save the file and transfer it over to your BI server.

Now login to OBIEE server and install Microsoft Access Database Engine 2010 Redistributable. This component is needed in order to allow the creation of system DSN based on Excel sheets (actually a few Office files are supported, not just Excel).

This guide assumes you are running OBIEE instance on a Windows Server machine. Although this is also feasible under Linux I cannot show you how to import Excel datasource into the repository since AFAIK BI Administration Tool is available only in Windows BI version.

Once you are done copying Excel files over to OBIEE server let's now define our datasource by clicking Start -> Administrative Tools -> Data Sources (ODBC) -> System DSN tab

Click Add -> Microsoft Excel Driver -> Finish

Choose a name for your datasource (DATA_FROM_EXCEL in this example) then click Select Workbook, choose the previously created Excel file and click OK twice.

Now Excel file is identified by the system as a proper datasource, so let's use it in OBIEE repository.

Open BI Administration Tool, load your existing repository if needed or simply create a new one, then click File -> Import Metadata

Select datasource you created in the previous step (DATA_FROM_EXCEL in this example) and press Next.
Check only Tables type and press Next.

Your tables will be correctly recognized and ready to be imported. Select what you need then press Finish.

Tables, rows and data will be imported in your Physical Layer ready to be used for your analytics.

That's all!!

VMware: MAC hash based LB

In this series of post regarding Load Balancing in VMware vSwitches it's now time to spend a few words on MAC hash based load balancing.

This load balancing algorithm chooses which vmnic utilize for sending traffic based on a calculation performed on source VM MAC address rather than destination IP address of a TCP/UDP conversation like load balancing based on IP hash or a Round Robin vmnic assignation like route based on originating port ID. Nevertheless this has some pros and cons. Positive it's undoubtly the fact that no configuration changes are needed at physical switch level but on negative side it is a rather deterministic and static vmnic assignation.

Route based on source MAC hash is set at vSwitch or PortGroup level.

Quoting VMware Virtual Networking Concepts guide:

Route based on source MAC hash choose an uplink based on a hash of the source Ethernet MAC address.
When you use this setting, traffic from a given virtual Ethernet adapter is consistently sent to the same physical adapter unless there is a failover to another adapter in the NIC team.Replies are received on the same physical adapter as the physical switch learns the port association. This setting provides an even distribution of traffic if the number of virtual Ethernet adapters is greater than the number of physical adapters.

Every VM's virtual network adapter has a MAC address assigned to it. This is assigned by default by VMware during virtual network adapter creation and this process has a regular pattern. First 24bits are 00:50:56. They represents MAC OUI (Organizationally Unique Identifiers) and are common for all VMware's created virtual network adapters.

The MAC address range observed by VMware is from 00:50:56:00:00:00 to 00:50:56:3F:FF:FF and MAC Address generation algorithm guarantees unique addresses within a VM and attempts to provide unique MAC addresses across VMs.

As referred by MAC Address Generation article last MAC 24 bits (3 octets) are assigned in this way:

SMBIOS UUID for the physical ESXi machine, and a hash based on the name of the entity that the MAC address is being generated for.

You can also manually specify a MAC address for your VM despite I suggest you to let VMware decide which MAC address assign to your virtual network adapter to prevent MAC address duplications across VMs.

Let's now see how this actually works:

vmnic used = HEX(VM virtual network adapter MAC Address) mod (Number of vmnics)

Where:

VM vNIC MAC Address is the MAC address of the VM's virtual network adapter assigned to a certain VM.

Number of vmnics: the number of vmnics configured for portgroup on which VM resides.

Now let's examine a sample scenario:

Two VMs with two virtual network adapters on one ESXi host, each with following MAC address (automatically assigned by vSphere during creation):

Test1:
00:50:56:a3:a5:e6
00:50:56:a3:4a:a2

Test2:
00:50:56:a3:d6:d3
00:50:56:a3:17:4e

One VM with two virtual network adapters on another ESXi host.

Test3:
00:50:56:a3:c6:fb
00:50:56:a3:84:9e

VMs MAC address is not changed by vMotion. If we vMotion or cold migrate Test3 from ESXi2 to ESXi1 MAC addresses are still the same.

Let's check which vmnic will each VM use:

Test1:

00:50:56:a3:a5:e6 mod(4) -> 2 -> vmnic2
00:50:56:a3:4a:a2 mod(4) -> 2 -> vmnic2

Test2:

00:50:56:a3:d6:d3 mod(4) -> 3 -> vmnic3
00:50:56:a3:17:4e mod(4) -> 2 -> vmnic2

Test3:

00:50:56:a3:c6:fb mod(4) -> 3 -> vmnic3
00:50:56:a3:84:9e mod(4) -> 2 -> vmnic2

As you can see results from Test2 and Test3 confirm that every VM's virtual network adapter use a different vmnic for communicating outside vSwitch except Test1 which uses vmnic2 for both its virtual adapters.

Since all these MAC addresses were generated by VMware itself during VM network adapter creation this implies that MAC address generation process is not aware of load balancing technique used at vSwitch or PortGroup level so this could potentially affect in a negative way VM to vmnic mapping. If MAC hash LB is used it's advisable check to what vmnic each VM is assigned to.

This can be simply done by using esxtop (then press "n" for network screen)  in ESXi console.
In my case this is the result showing the MAC address "misconfiguration" for Test1 VM.

That's all!!

Oracle UCM: Searching content using logical operators

This post is about methods for content retrieval in Oracle WebCenter Content (formerly known as UCM) using common logical operators.
Searches among contents, as all of you know, can be performed using two search forms: Quick Search and Full Search. The thing that maybe not everyone knows is that both of them support the use of logical operators for content searches.

Quick search, as name suggests, is the quickest way for content search.

Full Search is the search form displayed, by default, in the upper menu and it is the most complete solution for content retrieval infact it allows users to search against every metadata defined by the system administrator.
It can also be easily customized by using check-in profiles restricting users to search only against metadata that are significant for a particular document type.

Methods for content retrieval can be applied to both Quick and Full Search and allow to increase the flexibility of your searches.

Here they are:


AND Operator:

Use: search_term_1 AND search_term_2

Equivalent to using SPACE between search terms:  search_term_1 search_term_2

Example: billing AND expenses

This returns all documents that contains billing AND expenses words.


OR Operator:

Use: search_term_1 OR search_term_2
Equivalent to using COMMA between search terms:  search_term_1, search_term_2

Example: billing OR expenses

This returns all documents that contains billing OR expenses words.


NOT Operator:

Use: search_term_1 NOT search_term_2
Equivalent to using MINUS between search terms:  search_term_1 -search_term_2

Example: billing NOT expenses

This returns only documents that contains billing word NOT documents containing expenses word.


MATCHES Operator:

Use: "search_term_1"

Example: "billing expenses"

This returns all documents that contains the exact billing expenses word.


Nested Operators:

Use: (search_term_1 [OPERATION] search_term_2) [OPERATION] search_term_x

Performs searches in the order specified by the parenthesis.

Example: (billing AND expenses AND june) NOT 2008

This searches first all documents that contains billing AND expenses AND june words then from all these documents it returns only the ones that does NOT contain the word 2008.

Note: By default Quick Search performs content retrieval against title, content ID, and full text. This behaviour, as Kyle suggests in his article, can be modified editing UCM configuration parameters (Administration -> Admin Server -> Server Configuration) by adding following line:

QuickSearchFields=dDocName|dDocTitle|dDocFullText|dDocAuthor|xYourCustomMetadata

Where xYourCustomMetadata is the name of your custom metadata you need to perform searches against. Obviously this line can be tuned according to your preferences if you for example want not to search among fulltext just remove it from configuration parameter.

QuickSearch search patterns can also be modified. Editing the same configuration as explained above you can customize them:

QuickSearchOperators=hasAsSubstring,hasAsSubstring,fullText,hasAsSubstring,hasAsSubstring,equals

That's all!!