Thursday, October 18, 2018

Looker & LookML functioning in OBIEE

Looker & Features..           

Looker has a web-based interface that can be used to exploit the expertise of companies’ analytics teams. Using the system, teams can build and share reports in an instant helping their organization use data to boost business decisions and actions. The software is run in database, allowing limitless drill paths and queries. Looker does not depend of cubing of data, intermediary storage or ETL, making it easier to view and compile the data.       
Looker also lets users build their own analytic modules and design visualizations using a single code. Queries can be easily produced and optimized with the platform’s ML code. It can analyse both web-hosted and SQL data and is able to accommodate well over 25 data variations. This piece of software has been helping companies boost customer satisfaction and convert site traffic into eCommerce data.
Looker is best-known for developing an easier approach to data analytics, namely a brand-new database query language call LookML. The language operates like a simplified version of SQL which is reusable and modular, and thus, way easier to learn for first-time users. 

OBIEE RPD Development Speed Limitation..

Here in OBIEE, RPD development is entirely point-and-click within the admin tool which is somehow considered slow and old fashion in a world of scripting, code versioning and git merging. Several solutions are out in the market to enhance the agility of the development but still, the skills and the tool set required to develop new content makes it a purely IT manageable solution.
In order to overcome this limitation several tools like Tableau, QlikView or Oracle's Data Visualization (included in OAC or in the Desktop version) give all the power in the ends of the end-user: from data-sources to graphing, the tools allow an end-to-end data discovery to visualization journey. The problem with those tools is that there is no central definition of the KPI since it's demanded to every analyst. All those tools are addressing the problem by providing some sort of data source certification allowing a data source to be visible and reusable publicly only when it's validated centrally. Again, for most of those tools, the modelling is done in a visual format, which makes it difficult to debug, version control and automate.

What is LookML?.

LookML is a language for describing dimensions, aggregates, calculations and data relationships in a SQL database. The Looker app uses a model written in LookML to construct SQL queries against a particular database.

LookML in OBIEE..

LookML takes the best part of OBIEE: the idea of a modelling layer and democratizes it in order to be available to all business user with a simple language and set of concepts. Moreover, the code versioning is embedded in the tool, so there's no need to teach git branch, commit, push or pull to non-IT people.

LookML Concepts..       

Let's start from the basic of the RPD modelling: a database table. In LookML each table is represented by an object called View. Moreover, LookML's Views can be used not only to map existing database tables but also to create new tables based on existing content and a SQL definition, like the opaque views in OBIEE. On top of this LookML allows the phisicalization of those objects (into a table) and the definition of a schedule for the refresh. This concept is very useful when aggregates are needed, the aggregate definition (SQL) is defined within the LookML View together with the related refresh schedule.

The View itself defines only the source, a bit like the RPD's physical layer, the next step is defining how multiple Views interact within each other, or, in OBIEE terms, the Business Layer. 
In LookML there is an entity called Explores and is the place where we can define which Views we want to group together, and what's the linkage between them. Multiple Explores are defined in a Model, which should be unique per database. So, in OBIEE words, a Model can be compared to a Business Model with Explores being a subset of Facts and Dimensions grouped in a Subject Area.

Field Parameters in LookML & OBIEE.

As of the table all are having doubts about where do we set columns and aggregations? There both are drawn within a LookML View into Fields. The Fields is a common term which includes in both metrics and attributes, the field naming in both LookML and OBIEE are as below:

  • Dimensions: In LookML its a computed value based on some sort of column manipulation or combination, the dimension is the column itself while in OBIEE terms is the table. A Dimension can be a column value or a combination of multiple values (like OBIEE's BM Logical Sources formulas). A Dimension in LookML can't have any aggregation (as in OBIEE).
  • Measures: In LookML these are similar to aggregate functions in SQL (e.g. COUNT, SUM, AVG) and represent information about multiple rows. In OBIEE's terms this are metrics, the definition includes, the source formula in SQL syntax, the type of aggregation (min/max/count...) and the drill fields.
  • Filters: In LookML these for create a filter-only field, user can use to provide input to a templated filter or a conditional join. this is not something usually defined in OBIEE's RPD, filters are a way of passing a user choice based on a column value back to an RPD calculation formula, a bit like, for the OBIEE experts, overriding session variables with dashboard prompt values.
  • Parameters: In LookML this for create a filter-only field, users can use to provide input to a liquid {% parameter %} tag. In OBIEE's RPD, a Parameter as a way of setting up variables function. E.g. a Parameter with values SUM, AVG, MIN, MAX could be used to change how a certain Measure is aggregated.

1 comment:

  1. I like your post very much. It is very much useful for my research. I hope you to share more info about this. Keep posting Qlikview Training