BI-Quotient http://www.business-intelligence-quotient.com Intelligent BI. Sat, 15 Jun 2013 07:42:52 +0000 en-US hourly 1 http://wordpress.org/?v=3.4.2 ODI 11g Cookbook – The leading ETL tool now also has the best ETL book http://www.business-intelligence-quotient.com/?p=1919 http://www.business-intelligence-quotient.com/?p=1919#comments Sat, 15 Jun 2013 07:40:42 +0000 Uli Bethke http://www.business-intelligence-quotient.com/?p=1919 I don’t know too many books or movies where the sequel is better than the original. The ODI 11g cookbook is such a rare case. It is stuffed with 60 valuable recipes that every ODI developer should know. Apart from the value of these recipes in their own right they also showcase the flexibility of ODI and can be transfered to other problems.

My favourite chapters are; Knowledge Module Internals, Advanced Coding Techniques, Advanced Topology, and Using Variables.

A big thank you to the ODI product management team for sharing their insights.


Related posts

]]> http://www.business-intelligence-quotient.com/?feed=rss2&p=1919 0 Using ODI user functions to dynamically inject SQL into Interfaces http://www.business-intelligence-quotient.com/?p=1831 http://www.business-intelligence-quotient.com/?p=1831#comments Sun, 19 May 2013 22:42:06 +0000 Maciek http://www.business-intelligence-quotient.com/?p=1831 I’d like to share with you a recipe that demonstrates the power which the combination of ODI functions and Java BeanShell scripting techniques can provide.
Before I do so I will briefly describe the issue we recently had as a background for the use case.

Doesn’t matter which programming language or tool you use, it’s a widely known good practice not to hardcode static values into your code.
I’m talking here about various ‘configuration’ like values used within the code itself: thresholds, whitelists, blacklists, capex values etc..
During the code development phase those may very well seem static but later when product has gone live it is often required to adjust them which may even result in a hotfix case in the worst scenario.
To prevent it the values can be provided in many alternative ways including registry, ini and XML files or just anything sourced from network connection.
The ODI is no exception here and the generic purpose parameter table seem like the natural way to handle such situations.

The problem may occur when such value is to be used in an ODI interface directly.
In the case we had the large table had to be filtered using such metadata stored parameter.
One way to do this would be to pull in that table directly into the interface.
It could be then used in the join:


Unfortunately, as would really expect, the join condition:

DS.DAY_DATE>to_date(P.VALUE,‘YYYY-MM-DD’) AND KEY=‘LAST_PROGRESS_DATE’

doesn’t make a great query execution plan:


What we can alternatively do is to pass it as a regular filter in the SUBQUERY form:


DS.DAY_DATE > (SELECT to_date(value,‘YYYY-MM-DD’) FROM EDW.C_PARAMETER m WHERE KEY=‘LAST_PROGRESS_DATE’)

It doesn’t improve it a bit. Still the INDEX RANGE SCAN is performed.
Both queries take between 40minutes to 1hour.

I can almost hear you shouting “Why don’t you use an ODI variable for it?” and you’re damn right, this would solve the problem.
When static value is used the query filter

DS.DAY_DATE > #V_LAST_PROGRESS_DATE

changes the plan to simply do the FULL TABLE scan which it should do in the first place;


Using an ODI variable however has also some downsides.
Each parameter introduces an overhead of dedicated ODI variable. This in practice translates to wrapping interface in the Package with separate declaration and separate refresh step.
Very often such parameters would use the same source for it but it still requires separate refresh definition. What I have in mind here is the elegant solution seen in OBIEE where variables are just different columns which can then share the same execution block.
Another thing is that with the complex logic the number of parameters usually grows fast which simply makes it difficult to manage.
Last but not least is the fact that it makes debugging difficult. Variables by default are not seen in the operator.

This can make it even harder to find out what’s happened on Production at some point in time when log is the only thing you left with.

I said ‘by default’ as there are ways to achive it. Prior to ODI version 11.1.1.6 the variable could be only printed using tricks like throwing and ignoring exceptions or switching history for variable, neither one elegant. From that version onwards there’s variable tracking feature but again as this requires running code with log level higher than 6 something not enabled by default, especially in production environment.

It turns out that with a bit of Java BeanShell scripting such parameter values can be sourced in a run-time from the database and substituted in the right place just before the query runs.

To make the code reusable we will wrap the Java BeanShell code into the function with the syntax defined as

DS.DAY_DATE > #V_LAST_PROGRESS_DATE

The first parameter $(SQL) would just specify a full text of the query to return the parameter value. This is the equivalent of ODI Variable refresh SQL text.
The second one, $(AT) would specify which connection that refresh statement should be executed at. It should only take the values either “DEST”, “SRC” or “WORKREP”. Yes, it’s possible to get runtime repository values too!
The “SRC” might be very useful when “Command on Source/Command on Target” is used or simply when some different than Target schema is to be used for getting parameter value.

The implementation goes as following:

<?
java.sql.Connection targConnection = odiRef.getJDBCConnection(“$(AT)”);
java.sql.Statement s = targConnection.createStatement();
String query=“$(SQL)”;
java.sql.ResultSet rs = s.executeQuery(query);
if (rs.next())
     out.println(rs.getString(1));
else
     throw new Exception(“Query “+query+” in UDF failed.”);
s.close();
?>

Now the name for our function. As you can see from the screenshot above, it is different from the one in the syntax.
I didn’t mention it before but this is where another trick comes in.
We can actually make the second function parameter optional.

While this is directly possible in many programming languages it’s not in ODI and what we need to do here instead is to leverage another paradigm taken from programming world called function overloading.
This will be achieved by creating another function that takes one parameter and executes the original one passing it and hardcoding the second one.
The name used in the syntax for both will be the same but the syntax itself will be obviously different:

 

ODI function name

ODI function syntax

implementation
SUBST_QUERY_RESULT_VAL_AT SUBST_QUERY_RESULT_VAL($(SQL),$(AT)) does the heavy lifting
SUBST_QUERY_RESULT_VAL SUBST_QUERY_RESULT_VAL($(SQL)) executes the function above:SUBST_QUERY_RESULT_VAL($(SQL),DEST)
 

The second’s function implementation simply calls the first one transparently passing the same SQL and defaulting the $(AT) parameter with DEST value:

SUBST_QUERY_RESULT_VAL($(SQL),DEST)

Thanks to it, the user can specify the second parameter or skip it whereby the ‘DEST’ would be used instead.
ODI will analyze the function call format used in the Interface and based on that will substitute relevant function.

I should also mention here there’s an ODI bug that may prevent from linking to the  right function.
I discovered the order in which functions are created plays a role here (values of internal ODI Object IDs?).

This means if we create the the ‘wrapper’ one first, ODI will assume there’s a recurrence call even within the function even when the call doesn’t match the function syntax. It can be easily diagnosed by expanding and examining ‘uses’ contents under the function itself. The following indicates wrong recurrence:

If you created the functions in the order as described in this post you should see the calls are resolved properly:

Coming back to our use case, those functions then allow using the SQL queries directly in the interface. The SQL filter below:

DS.DAY_DATE > SUBST_QUERY_RESULT_VAL(SELECT ‘date”’||value||”” FROM C_PARAMETER WHERE KEY=‘LAST_PROGRESS_DATE’)

will be substituted nicely with

DS.DAY_DATE > date’2013-01-13′

which in brings down the query time to less than 3 minutes.

It’s not everything, the source schema for parameter table doesn’t need to be hardcoded either!
A function with nested substitution API call would work in the same way:

DS.DAY_DATE > SUBST_QUERY_RESULT_VAL(SELECT ‘date”’||value||”” FROM ”+odiRef.getObjectName(“C_PARAMETER”)+” WHERE KEY=‘LAST_PROGRESS_DATE’)

Please note that this ODI function call has one limitation though – The SQL parameter has to be all expressed within one line, doesn’t matter how long.
This however, with yet another trick can be lifted too.
You can learn about it and plenty of other useful ODI scripting techniques at the training we will have available soon.

Most Popular Posts

]]> http://www.business-intelligence-quotient.com/?feed=rss2&p=1831 7 Endeca text enrichment. Entities extraction, sentiment analysis, and text tagging with Lexalytics customer defined lists. I love this one! http://www.business-intelligence-quotient.com/?p=1801 http://www.business-intelligence-quotient.com/?p=1801#comments Thu, 18 Apr 2013 21:18:53 +0000 Uli Bethke http://www.business-intelligence-quotient.com/?p=1801 One of the interesting options of Endeca is its integration with text mining software Lexalytics (licensed separately). Lexalytics offers many text analysis functions such as sentiment analysis, document collection analysis, named entity extraction, theme and context extraction, summarization, document classification etc. Endeca exposes some of this functionality via its text enrichment component in Clover ETL. It is worthwhile noting that not all of the text analytics functionality is exposed via text enrichment and of those features that are exposed only a limited number of methods of the Lexalytics API are exposed (more on that later). A great way of learning more about Lexalytics is to visit their website, Wiki, and blog. I will post some more stuff on text analytics and Lexalytics in particular in one of my next posts.

Text tagging with Lexalytics

In my last post on Endeca we were using the text tagger component to tag a list of 16K Irish IT job records with 30K skills extracted from LinkedIn. If you remember we saw some dreadful performance and also realised that the text tagger component is not multi-threaded and maxed out at 25% on a quad-core CPU. The text enrichment component also offers text tagging and based on the documentation is also multi-threaded. So my expectation is that the tagging process is a lot quicker. Apart from the skills tagging exercise we will also perform some entity extraction on the jobs data and focus on People, Companies, and Products.

In a first step we download Lexalytics from edelivery.

We then add the license file that comes with the download to the Lexalytics root directory. In my case E:\Program Files (x86)\Lexalytics

We then add the salience.properties file to our project. This configuration file sets the various properties that we can make us of, e.g which types of entities we want to extract. As you can see from the screenshot below we will extract entities of type Person, Company, Product, and List. The interesting entity is the List entity. Through the List entity we can specify our own custom lists to the Lexalytics Salience engine.

Custom lists are made available to Lexalytics in the E:\Program Files (x86)\Lexalytics\data\user\salience\entities\lists folder as a tab separated Customer Defined List file, which contains various values and a label separated by Tab.

One big shortcoming of the Endeca text enrichment component is that it does not extract the label that can be defined in the customer defined list, e.g. The following entry has the tab separated label of Business Intelligence. The text enrichment component does not extract this even though this is exposed by the Lexalytics API. This also means that you can only define one customer defined list per text enrichment batch, as all of your CDLs will be dumped into the List field.

OBIEE, Cognos, Micro Strategy Busines Intelligence

Anyway, below is our tab separated custom list

Next we add the location of the Lexalytics license file and the data folder as variables to our project workspace parameter file.

As in the previous post we read the scraped data of Irish IT jobs from a MySQL database. The number now stands at 21K. The data flow is a lot simpler than what we had to do with the text tagger.

We are now ready to configure our text enrichment component. It’s all pretty straightforward. We supply:

Configuration file: This is the path to the salience properties file

Input file: This is the field in our recordset that we want to extract entities from and use for tagging.

Salience license file: Path to Lexalytics license file

Salience data path: Path to Lexalytics data folder

Number of threads: Hoooraaaay! This component is multi-threaded. During my test runs it used 1 core and maxed out at 25% CPU for 1 thread, 2 cores and maxed out at 50% CPU for 2 threads and so on.

In a last step we have to define the metadata and add it to the edge of the graph.

We are now ready to run the graph. If you read my last post on text tagging you will remember that tagging with the text tagger component took a whopping 12 hours to tag 16K records against 30K skills. With the text enrichment component it took 20 minutes to tag 21K records and on top of that we also extracted Person, Product, and Company entities and did a bit of sentiment analysis as well.

Here are some of the results

Conclusion

  • If you have Lexalytics licensed as part of your Endeca install use it for text tagging rather than the Text Tagger component, which is pretty…, well,  lame.
  • Unlike the Text Tagger component the Text Enrichment component (thanks to the underlying Lexalytics salience engine) is fine piece of software engineering. Unlike the text tagger it is multi-threaded and increasing the number of threads to 4 increased my CPU usage to 100%. It processes the incoming records in batches and it was a joy to watch how it cleared out the memory properly after each batch.
  • The text enrichment component only exposes a subset of the Lexalytics functionality. If you want to make use of the full potential of the Salience engine you need to write your own custom code.
  • The text enrichment component offers a lot of room for improvement (1) It does not expose the full feature set of Lexalytics (2) The implementation of CDLs should include Label extraction. If you want to make use of the full Lexalytics functionality you would need to write a bit of custom code, which looks pretty straightforward to me.
    • [/list_check]

    In the next posts we will load the data into the MDEX engine and start our discovery process. I wonder what we will find out???

     


    Related posts

    ]]>     http://www.business-intelligence-quotient.com/?feed=rss2&p=1801 0     Endeca text tagging: Tagging unstructured IT jobs data against a whitelist of LinkedIn skills http://www.business-intelligence-quotient.com/?p=1774 http://www.business-intelligence-quotient.com/?p=1774#comments Mon, 01 Apr 2013 14:44:22 +0000 Uli Bethke http://www.business-intelligence-quotient.com/?p=1774 A couple of days ago I have started to look at Endeca. So far it looks like a great tool and I believe we will see and hear a lot more from it over the next couple of months and years.

    I am currently still learning a lot and in the next couple of weeks I will document my progress in a couple of blog posts. In this first post we will tag some unstructured data and then create a multi value attribute, which is one of the great features of Endeca.

    In this example we look at some Irish IT job offerings data that has been extracted from the web. One of the fields of this data set is a description field. We will tag this unstructured data with a whitelist of skills that were extracted from LinkedIn. There are 30K skills right now on LinkedIn, which resulted in some performance issues when using the text tagger in Clover ETL. More on that later

    Irish IT jobs data set (about 16K records collected over the last 3 months).

    LinkedIn skills data set

    The Irish jobs data sits on a MySQL server. The skills data set is in an Excel sheet. The end result of our ETL will look like this.

    Note: You need to name the input fields SearchTerm and TagValue respectively. Any other value will throw an error when loading via the Text Tagger component.

    We first add a connection to the MySQL database

    Next we create a graph and add the Irish Jobs and LinkedIn metadata to it.

    Note: A data flow/mapping in CloverETL is named graph.

    And the LinkedIn metadata

    Next we add a DB_INPUT_TABLE and an XLS_DataReader and configure them

    Next we add the Text Tagger – Whitelist component from the Discovery palette and connect the Irishjobs_IE input table and the XLSDataReader for the LinkedInSkills to it. The Text Tagger takes two parameters as input. The unstructured text field that needs to be tagged and the name of the output field. In our case the Source Field Name is Description and the Target Field Name is DescriptionSkills. We still need to set up the latter as Metadata in the next step.

    Note: I have set the multi-assign delimiter to “,”. This will separate the tagged skills as a comma separated list. In the Endeca Server we can treat this then as a multi-value attribute.

    Next we need to create the Metadata for the output from the Text Tagger and add the DescriptionSkills field to it. We do this duplicating the metadata from the irishjobs_ie input table and manually adding the field DescriptionSkills to it.

    Next we add a Bulk Add/Replace Records component to the graph, connect the Text Tagger to it, and add the newly created metadata to it. In the properties of the Bulk Add/Replace Records component we specifiy the Spec Attribute (unique key) and the multi-assign delimiter. In our case that is a “,” (as specified earlier in the text tagger. The multi-assign delimiter is used to split the chain of comma separated values. What I find strange here is that you need to define a delimiter for all of your attributes. I would really expect to specify this at the attribute level rather than the dataset level.

    ETL Performance problems

    Once this is done we can run the ETL. When I first ran this with 30K records in the skills whitelist the whole thing was crawling. For the 16K records it took 12 hours. I noticed that Clover was only using one core of my quad-core CPU at a time (maxed out at 25% CPU usage). An indication to me that multi-threading is not implemented. Either in the Text Tagger component itself or in Clover. I had a look on the Clover website and it stated there that it is multi-threaded so my conclusion for the moment is that the Text Tagger component is not multi-threaded or that the Desktop version of Clover is not multi-threaded. Can anyone please shed some light on this?

    I was then experimenting a bit. What I found was that splitting the whitelist file into multiple smaller ones and then sending the jobs data through the Text Tagger sausage machine improved performance significantly. What that means is that Text Tagger performance is not linear, i.e. doubling the number of whitelist records does more than double elapsed time for tagging.

    Below you see how the split is implemented in Clover. This should give you an idea how this can be done. I am sure this can be implemented in a more elegant way using splitters and loops and stuff. The take away though is that in order to get the best performance you need to play around with your whitelist sizes.

    Note: The Reformat component is used to concatenate the resulting skills output fields from the Text Tagger components.

    The result for 200 sample jobs for a subset of the skills (letters A-E) is as follows.


    Related posts

    ]]>     http://www.business-intelligence-quotient.com/?feed=rss2&p=1774 0     Oracle User Group Dublin, 12 March 2013. It’s FREE. http://www.business-intelligence-quotient.com/?p=1769 http://www.business-intelligence-quotient.com/?p=1769#comments Thu, 14 Feb 2013 17:51:38 +0000 Uli Bethke http://www.business-intelligence-quotient.com/?p=1769 The annual Oracle User Group meeting in Dublin is this year on a special day. 12th of March. That’s my birthday.

    The agenda is live now. Unfortunately, there is only one data warehouse/business intelligence stream this year. A lot of interesting presentations had to be left out.

    I will present with Maciek Kocon on “Oracle Data Integrator 11g Best Practices. Busting your performance, deployment, and scheduling headaches”.

    Other presentations include Mark Rittman on deploying OBIEE in the enterprise. Peak Indicators will be there to present on the concept and case study of a BI Competency Centre (BICC), and there will be a presentation on best practices in migrating from OWB to ODI.

    Oracle themselves also have a stream on the 12c database.

    If you are not interested in any of the above you may be interested in the free lunch.

    Register now. Only a few places remain.

    See you there!

    Most Popular Posts

    ]]>     http://www.business-intelligence-quotient.com/?feed=rss2&p=1769 0     Extreme re-usability in ODI 11g (I can’t believe I am giving away this trick). http://www.business-intelligence-quotient.com/?p=1754 http://www.business-intelligence-quotient.com/?p=1754#comments Mon, 07 Jan 2013 18:17:04 +0000 Uli Bethke http://www.business-intelligence-quotient.com/?p=1754 Another Christmas and a second baby under my belt it’s time to get back to blogging.

    There were recently some good posts by David Allan and Gurcan Orhan on the power of ODI functions. David mentions correctly that ODI functions are the most underrated feature in ODI. Whenever you think of re-usability in ODI think of user functions. Functions can be used anywhere in ODI where you need to write something once and apply it many times. This is not limited to using functions inside interfaces to load columns or the use of wrapping and parameterizing common functionality. You could just write a snippet of SQL, e.g. a commonly used filter in a WHERE clause as a function and reuse it many times. The nice thing about functions is that you can use substitution method API calls in a function, use Java or the ODI tools.

    Use case for re-use of user functions

    One of the things that have puzzled me about ODI and Knowledge Modules is that some of the steps in the KMs are repeated and re-used over and over again without some central placeholder. Why not write the step once and re-use it many times across the Knowledge Modules? This is exactly what I will show you. We will take table stats gathering as an example. One of the steps in Knowledge Modules is to gather table stats once the target table has been loaded. Why not create a user function that gathers table stats on a target table and takes the estimate percent size as a parameter?

    In a first step let’s create the function. As a recommendation I would suggest to prefix your functions with a convention. In a first implementation I had GATHER_TABLE_STATS for the syntax, which wreaked havoc with any KMs that are using the Oracle GATHER_TABLE_STATS procedure in package dbms_stats.

    And the implementation

    BEGIN

dbms_stats.gather_table_stats ( ownname => '<%=odiRef.getInfo( "DEST_SCHEMA" )%>', tabname => '<%=odiRef.getTargetTable("RES_NAME")%>', degree => DBMS_STATS.AUTO_DEGREE, estimate_percent => $(sample_size), cascade => TRUE  ) ;

END;

    Next we will use the function as a step in a (global) Knowledge Module and pass in 10 as a parameter for the estimate_percent part of the stats gathering piece. In this particular case I have modified the IKM SQL Control Append.

    Then we create and execute an interface that uses this IKM

    As you can see the function was substituted at runtime and stats were gathered on the target table.

    This is just one example where you can benefit from user function re-usability. Be creative and think out of the box and you will see user function written over everything.


    Related posts

    ]]>     http://www.business-intelligence-quotient.com/?feed=rss2&p=1754 1     How you can launch an ODI scenario through a web service call? http://www.business-intelligence-quotient.com/?p=1739 http://www.business-intelligence-quotient.com/?p=1739#comments Wed, 12 Dec 2012 18:10:09 +0000 Uli Bethke http://www.business-intelligence-quotient.com/?p=1739 ODI and Jetty

    The ODI 11g standalone agent now ships with its own lightweight application server (Jetty). The main reason this was included is to make it easier to execute scenarios via web service calls. In the past this was quite painful as you needed a separate application server (OC4J). The other limitation was that it was difficult (read work around) to implement asynchronous web service calls. Luckily, this has all changed with ODI 11g and Jetty. However, I don’t believe that you can run the ODI console in Jetty, which is unfortunate.

    Asynchronous web service calls

    The WSDL can be found at http://<standaloneagentname>:<port>/oraclediagent/OdiInvoke?wsdl

     

    We can call the web service methods from any SOAP Client, e.g. the OdiInvokeWebService tool built into ODI.

    We need to provide our login, the work repository for execution, the name of the scenario, its version, and the context. You can also specify if you want the scenario to be executed synchronously or asynchronously. If you set Synchronous to false then control will be handed back to the client immediately. If you set it to true it will execute and then return control. In asynchronous scenarios you can use the session ID from the response file and the getSessionStatus method to return the status of your scenario execution.

    The other thing you have to be aware of is that when you launch a scenario through a web service call it will fork out a new agent based on your odiparams.bat. You need to plan for the extra memory used up.

    You can now easily orchestrate your process flows in BEPL or launch them from OBIEE.


    Related posts

    ]]>     http://www.business-intelligence-quotient.com/?feed=rss2&p=1739 0     Exadata, Exalytics, SAP HANA and the multi billion dollar question. http://www.business-intelligence-quotient.com/?p=1727 http://www.business-intelligence-quotient.com/?p=1727#comments Tue, 27 Nov 2012 22:51:32 +0000 Uli Bethke http://www.business-intelligence-quotient.com/?p=1727 Recently there has been a lot of noise around in memory databases and how Exadata, Exalytics and SAP HANA compare to each other. Here are my two cents on the debate.

    Why you can’t compare Exalytics to SAP Hana

    It is the vision of SAP Hana to be used for both OLTP and analytics. As the name already suggests Exalytics just caters for analytics. Exalytics needs to be loaded from the data warehouse or transactional systems. Not needed for Hana. Everything already sits in memory. While Exalytics is near near realtime. Hana is realtime. However, currently there are not too many OLTP applications running on Hana. The problem is that applications need to be adapted or rewritten to make full use of HANA’s new architecture.This seems about to change. SAP has recently released a service pack for HANA that will allow it to do just that. However, the claim of being able to run OLTP and Analytics in the same in memory database remains somewhat unproven.

    Why you can’t compare Exadata to SAP Hana

    Exadata V3 now ships with 4 TB of RAM. Contrary to the claims of Oracle this does not make it an in-memory database. It lacks two important features:

    - Most of the data still sits on disk. Mostly SSD. Still disk
    - It lacks the optimized algorithms and design features (in memory indexes etc.) that have specifically been designed for in memory access.

    How does HANA compare to Exadata/Exalytics then?

    Well, it doesn’t. Oracle (or anyone else for that matter) still has to come up with a product that can be compared to HANA.

    Use cases of SAP HANA

    The number 1 use case for SAP HANA is for realtime operational business intelligence. True realtime BI now seems a distinct possibility. In this use case HANA represents the Interactive Sector as defined by Inmon et al. in their DW 2.0 book.

    The other use case is similar to what Exalytics is used for. A performance booster for the data warehouse. In this scenario we load or replicate data into the in-memory database either directly from our OLTP systems or the data warehouse. This is what we have predominantly seen HANA being used for so far. However, this falls well short of the vision and long term strategy, namely to change the way we do databases.

    The third use case of course and the end game for HANA is to run OLTP, data warehouse, and analytics all on HANA. Not very realistic at the moment. DRAM is still too costly.

    What happens next?

    This is the billion dollar question. Curt Monash thinks:

    “Putting all that together, the analytic case for SAP HANA seems decently substantiated, there are years of experience with the technology and its antecedents, and column stores (including in-memory) are well-established for analytics via multiple vendors. The OLTP case for HANA, however, remains largely unproven. It will be interesting to see how it plays out.”

    It will indeed be interesting to see how this plays out. SAP are currently heavily promoting HANA. There is a developer license available. You can rent an instance on AWS. SAP are pushing it hard for start-ups. I am sure that it will replace Oracle in many SAP implementations as the underlying database. It remains to be seen, however, if it can eat into the wider database market (the ultimate SAP objective). The other interesting question is: when will Oracle come up with a product that can compete head on with HANA? I believe there is plenty of time. The game hasn’t really started yet.

    In the meantime Microsoft have also announced an in memory database named Hekaton for release in 2014/15. It seems to be for OLTP only and from what I read a bit of a joke. Interesting times indeed though.

    The wider picture and Google

    At the moment we are seeing some tectonic shifts in the technology space that we haven’t seen in a generation. New technologies like Hadoop, Impala, IMDBs, NoSQL, Cloud etc are emerging that can handle ever bigger data at an ever faster speed. These innovations will have knock on effects on the way we architect information systems and on enterprise architecture in general. I even believe that they will ultimately change the way we do business. Unknown to many, a lot of these innovations are pioneered by Google. Papers on MapReduce  and the Google File System kicked off Hadoop. Google BigTable  inspired a lot of the NoSQL databases we have seen recently. You may have heard of Impala from Cloudera. Again a brainchild of Google. Based on Google Spanner and probably more so their in-house RDBMS F1.

    I would expect more innovation to come from that corner. After all Google is at the epicentre of the Big Data problem. They already have their own offering named BigQuery, which recently left the Beta phase. Doesn’t look like much right now but I expect them to up their game.

    Of course you can ignore those trends but you do so at your own peril.

    If you want to find out more about IMDBs and SAP Hana I recommend to read In-Memory Data Management: Technology and Applications by Hasso Plattner, one of the co-founders of SAP.


    Related posts

    ]]>     http://www.business-intelligence-quotient.com/?feed=rss2&p=1727 2     Tricks with SQL: Beware of the Predicate in the Outer Join http://www.business-intelligence-quotient.com/?p=1714 http://www.business-intelligence-quotient.com/?p=1714#comments Wed, 21 Nov 2012 17:57:54 +0000 Uli Bethke http://www.business-intelligence-quotient.com/?p=1714 Today we will have a look what happens when we place a filter condition into an outer join. We will also have a look at how this compares to placing the filter condition into the WHERE clause. Finally we’ll have a look where this could be useful.

    Let’s first create some sample data.

    create table customer (
cust_id number,
cust_desc varchar2(50)
);

ALTER TABLE customer
add CONSTRAINT pk_customer PRIMARY KEY (cust_id);

create table customer_loc (
customer_loc_id NUMBER,
customer_id NUMBER,
customer_loc_desc VARCHAR2(50)
);

ALTER TABLE customer_loc
add CONSTRAINT pk_customer_loc PRIMARY KEY (customer_loc_id);

insert into customer values (1,'Gold');
insert into customer values (2,'Gold');
insert into customer values (3,'Silver');

insert into customer_loc values (1,1,'Dublin');
insert into customer_loc values (2,2,'Paris');
insert into customer_loc values (3,4,'Berlin');

commit;

analyze table customer compute statistics;
analyze table customer_loc compute statistics;

    Let’s run our first outer join query where we put the filter condition into the WHERE clause. All is as expected. The query just returns customer_id 1.

    Now let’s run the same query, but we put the filter condition into the join. This may be different from what you may have expected. What happens is that the query returns all of the rows in the customer table and those rows of table customer_loc where the join condition is met, i.e. those rows where customer_id = 1.

    Let’s verify what we have just seen with another query. This time we will put customer_id = 4 into the Join condition. There are no rows in table customer_loc that match this. As expected the query returns all rows for table customer but now rows for table customer_loc

    What could this be useful for? One use case would be some limited form of data densification whereby you need to return all of the tables in one table but only a subset of rows in another table. Typically this can only be done using some sort of subselect. An example:

    The query below is run in the SH schema and returns all of the customers, but only those sales transactions that are larger than 1000 in sales_amount.

    select a.cust_id,amount_sold
from customers a 
left outer join sales b on a.cust_id = b.cust_id and amount_sold > 1000;


    Related posts

    • August 9, 2008 -- CSV to Rows
      I recently needed to convert a comma separated list of values in a table column to a column where ea...
    ]]>     http://www.business-intelligence-quotient.com/?feed=rss2&p=1714 2     Making use of ODI Flexfields to meet requirements http://www.business-intelligence-quotient.com/?p=1702 http://www.business-intelligence-quotient.com/?p=1702#comments Mon, 12 Nov 2012 18:12:38 +0000 Uli Bethke http://www.business-intelligence-quotient.com/?p=1702 What are ODI Flexfields?

    In ODI you can create user-defined fields on certain objects. You can think of these fields as additional attributes for certain objects. At design time you populate these attributes with values that are then used at runtime, e.g. by a Knowledge Module. There are various Flexfields defined out of the box for very specific requirements, e.g. there are Flexfields defined on the Datastore object for SAP and HIVE data integration tasks.

    Where do you create them?

    You create Flexfields in the Security module under the Objects accordion. You can’t create Flexfields for all of the objects. While you can create a Flexfield for an Interface you can’t create a Flexfield for an Interface Target Table.
    Once you have created the Flexfield you can then populate it with values in Designer.
    An example
    You have a requirement to log errors to an error table using a CKM. Based on the severity of the error you want to allow records through to your target table. DQ checks that result in minor errors are logged in the error table and go through to the target. Records with more sever issues only go to the error table and do not end up in the target table.
    In a first step we need to define a Numeric type Flexfield on the Condition object. We name this field Passthrough

    When you create your DQ Condition in Designer you can then set the value for the Flexfield. The value 1 will allow the record to go through to the target table even if there is a violation of the DQ check.

    In a next step we need to write some code in our CKM to make use of the Flexfield and implement the above logic.

    We store the value of the Flexfield in a variable passthrough and flag all of the records with the passthrough value.

    <? passthrough=”<%=odiRef.getFlexFieldValue(“” + odiRef.getCK(“ID”) + “”,”2500″,”PASSTHROUGH_COND”)%>”.replaceAll(“‘”,””””); ?>

    As you can see from the figure below, 2500 is the internal ID of object Condition in the ODI repository.

    Most Popular Posts

    ]]>     http://www.business-intelligence-quotient.com/?feed=rss2&p=1702 0