Wednesday, October 19, 2016

Descriptive Statistics And Clustering With Data Miner

Ok, this is my third post in a series of predictive analytics for Essbase/Planning. 

  1. In the first one, “Essbase Meets R”, I described options to integrate Essbase with open source software R.
  2. In the second, Essbase Meets R: Next Steps: ODM And ORE, we walk through steps to configure Oracle Data Miner (ODM) and Oracle R Enterprise (ORE).
  3. Here we’ll see how to use descriptive statistics and run clustering models in ODM. Results of the clustering will be used by the forecasting models, and will be covered in the next posts.
First, let me say this is not supposed to teach you math/statistics. This is simply me playing with ODM and a demonstration how you could use various models. They may or may not be suitable for your particular problem. Consult with a statistician or a data scientist if you consider using them. 

Descriptive Statistics

In the previous post we saw how to create a basic workflow that consists of a data source and "Explore" node.
Once we ran the workflow we can right click Explore Data node and select “View Data”. There we can see some nice histograms by dimension.



Histograms are especially useful when we just start examining our data. We can see how it is distributed across attributes (or dimension members in other words). In “Essbase Meets R” we tried to identify optimal sample dimensionality, or how to choose the appropriate levels from our hierarchies. When we run our model, we don’t want to mix up samples that behave completely differently (like Travel Expense of Sales cost centers and IT cost centers).


Histograms can be helpful in that respect, although in the next sections we’ll review more accurate methods, such as clustering and association rules.


At the first glance we can see that for some dimensions like BusinessUnit or Entity the data is concentrated in just a handful of members. 

Different data density is also an indication of different underlying business processes, relationships between the variables, and different distribution of the forecasted accounts. 




What if we change the number of bins from the default 10 to 100? In case of the Business Units we still have most of the data concentrated in a single member. 




But in case of the Cost Centers we have a different picture:



This means the data is distributed more evenly across cost centers. Does it mean we can combine all samples of individual cost centers? Not necessarily.  So far we were referring to level-0 and top node of the dimension. We don’t have dimensional information in this fact table. It contains only level 0 members of the hierarchy. Dimensional structures are stored in a separate table, and each dimension has an associated view. At least in this example. What if we wanted to examine histograms that include certain levels of cost center hierarchy? For example we want to see how level-0 cost centers are distributed within the 3rd generation of Cost Center dimension, or withing VP groupings of the hierarchy?


For that purpose we can create a view that will join a fact table with dimension view and will return data points with CostCenter ancestors - specifically the third generation member from the cost center hierarchy. And let's add that ancestor as an attribute of “Explore Data” step.




We can see that the data is concentrated differently in different VP cost centers. But does different data concentration necessarily mean that it is distributed differently as well (has different relationships between the variables)? The answer is no. You may simply have a different number of child cost centers under each parent, but the data in each child could be distributed similarly. We would need to take into account the number of children in each upper level cost center. 


Somewhat better picture of the data would be given by histogram with groupings by  level-0 cost centers. You can immediately see that some level-0 cost centers have different data concentration, as part of upper level VP cost centers. 






At this point we cannot really identify specific hierarchy level or subcubes in multidimensional space that should be used to combine samples. We can probably say our models should not be run neither for level-0 cost centers nor  for combined sample of all cost centers, and that we need to run additional analysis.


Another problem with this approach is that if you are planning to investigate histograms of many dimension-generation combinations, that’s a lot of nodes in the workflow, and a lot of manual work. 


The bottom line - “Explore Data” node of Oracle Data Miner can be useful for a quick data exploration, easy to use, and will give a quick insight about the data to the business user. But it would be hardly sufficient to support exploration across multiple levels of multiple dimensions in a systematic and repeatable way. That’s where clustering models come into picture. 



Clustering Models

First,  what exactly clustering models are? I’ll refer to Wiki for definition:


Cluster analysis or clustering is the task of grouping a set of objects in such a way that objects in the same group (called a cluster) are more similar (in some sense or another) to each other than to those in other groups (clusters). It is a main task of exploratory data mining, and a common technique for statistical data analysis, used in many fields, including machine learning, pattern recognition, image analysis, information retrieval, bioinformatics, data compression, and computer graphics.

ODM provides 3 clustering algorithms: K-Means, O-Cluster, and Expectation Maximization. As a first step let's consider clustering of our raw level-0 data. That is when we select all attributes from the fact table. 




The outlined part of the demo workflow has query node that selects columns from data source node. It also contains CASE_ID column which is a concatenation of all level-0 members. This unique Case_ID is needed to run the clustering model. 



SELECT
"T_EPMA_FACT_N$10001"."ACCOUNT"||
"T_EPMA_FACT_N$10001"."BUSINESSUNIT"||
"T_EPMA_FACT_N$10001"."COSTCENTER"||
"T_EPMA_FACT_N$10001"."CURRENCY"||
"T_EPMA_FACT_N$10001"."ENTITY"||
"T_EPMA_FACT_N$10001"."MNUM"||
"T_EPMA_FACT_N$10001"."PCC"||
"T_EPMA_FACT_N$10001"."YEAR" CASE_ID
,"T_EPMA_FACT_N$10001"."DATA",
"T_EPMA_FACT_N$10001"."ACCOUNT",
"T_EPMA_FACT_N$10001"."BUSINESSUNIT",
"T_EPMA_FACT_N$10001"."COSTCENTER",
"T_EPMA_FACT_N$10001"."CURRENCY",
"T_EPMA_FACT_N$10001"."ENTITY",
"T_EPMA_FACT_N$10001"."MNUM",
"T_EPMA_FACT_N$10001"."PCC"
FROM "T_EPMA_FACT_N$10001"


Below are the input columns. We changed the automatic inputs to allow MNUM (month number) to be a categorical input, and not data.




Once the node finished running let's review the results. For each of the algorithms we get a tree that depicts the clusters:


K-Mean (a partial diagram - it actually ends up with 19 clusters, 10 of which are leaves):




Each leaf cluster is essentially a subcube, but members from each dimension do not need to be continuous level-0 members. It’s like a multidimensional jigsaw puzzle, when each piece does not overlap with other pieces.

Each cluster has a rule such as:



If
BUSINESSUNIT In ("B_xxxx")
And
COSTCENTER In ("C_0001", "C_0034", "C_0932", ...)
And
ACCOUNT In ("675675", "345565", "345346", "xxxxx",...)
And
PCC In ("P_xxx", "P_xxy", "P_fgh",...)
And
ENTITY In ("E_657", "E_345", "E_xxx", ...)
Then
Cluster is: 12

You can also see the histograms of members within each cluster:




Clusters created by different algorithms are not the same. For example Expectation Maximization tree would look like this:




And O-cluster algorithm will generate the following tree:




So which clustering method should we use, and are their better/alternative algorithms that are not available in ODM? Different methods use different optimization techniques. For example the idea behind K-means clustering is that a good clustering is one for which the within-cluster variation is as small as possible. - Gareth James, Daniela Witten, Trevor Hastie Robert Tibshirani, “An Introduction to Statistical Learning with Applications in R”. Expectation maximization uses iterative method for finding maximum likelihood estimates of parameters. The theory behind that is beyond the scope of this post, so at this point let's assume we'll choose the algorithm based on the accuracy it produces, when we run prediction models for each cluster. 


Now, let’s recall that we run a clustering algorithm on the raw fact data that contains only level-0 members. Would it make sense to consider hierarchies? Hierarchies and different levels in them are already a form of grouping (clustering) of members by some business criteria. So if we can incorporate that “hierarchical pre-clustering” in our clustering algorithm, we would at least save time running clustering models. What about cluster accuracy? Let's review the following example.


To incorporate hierarchical information we would need to run clustering on a query that joins fact table with dimension view and returns ancestor members from different dimension levels. This is similar to how we analyzed histograms with different generations of cost center dimension.


Let’s add 2nd and the 3rd generations  of Cost Center dimension to clustering node. If we compare confidence % of K-means clusters we can see that usually the higher we go up the hierarchy the lower is the confidence. Horizontal axis represents level-0 clusters.




This makes sense once you consider what confidence % means: “Confidence is the probability that a case described by this rule will actually be assigned to the cluster.” Assume the rule for COSTCENTER in cluster 6 is {IF “C_001”, “C_002”, “C_003” THEN CLUSTER6} and it has confidence of 100%. Meaning if an observation is in either of those cost centers then it is definitely in cluster 6. Assume also that all 3 cost centers belong to a common parent VP_010,  but VP_010 also has a child C_004. What is the confidence of the rule {IF "VP_010" THEN CLUSTER6}? If an observation has attribute VP_010 it may not belong to CLUSTER6, since there is a positive probability that the level-0 Cost Center of that observation is C_004. That's why the confidence decreases when we go up the hierarchy.


So can we say that using level-0 members will always bring better results than using higher level parents? Before we answer this let's recognize the following:



  • Clustering methods utilize heuristic algorithms to converge to local optimums.
  • In case of K-means we predefine the number of clusters, which is 10 by default.
  • Clustering algorithms are computationally expensive.


So how can we be sure that 10 clusters will really generate the best accuracy of our forecast in the end? Or is it more like 100 clusters? Or 347? The bottom line we’ll need to experiment and compare the results. But since each iteration of clustering may take quite some time, we may want to go up the hierarchy knowing that it will reduce the confidence, but will allow us to include more iterations and run the algorithm for larger number clusters, given the same time constraint.


For now let’s just keep in mind that we’ll need to run multiple iterations of clustering that will give us sub-optimal number of clusters and generation numbers for each hierarchy. Obviously this will require a lot of automation and PL/SQL code. Trying to do this in Data Miner UI seems to be non-practical. I’ll cover automation in one of the following posts.









Tuesday, October 11, 2016

Essbase Meets R: Next Steps: ODM And ORE

Since I published “Essbase Meets R” a few major events have happened:

  • Kscope16 (obviously). There I attended sessions of Brendan Tierney, Mark Rittman, and other bright folks which greatly stimulated my curiosity and desire to try new things with tools like Oracle Advanced Analytics, Oracle Data Miner, and Oracle R Enterprise. 
  • I talked to a few customers and data scientists, and we were trying to gather additional use cases of integrating predictive analytics tools with Essbase. Also I tried to survey those customers in terms of which tool they use for different functional areas. For example workforce predictive analytics, or what some of them call HR analytics, automating and bringing planning/budgeting/forecasting to the next level in term of accuracy.


As a result I decided to build another POC with the focus on Oracle Advanced Analytics and Data Miner.  The business use case would be the same as in “Essbase Meets R”: automating P&L forecast with tools for predictive analytics. This time I will rely as much as possible on the built-in data miner and ORE  (a.k.a. Oracle R Enterprise) functionality, and will compare two processes. Among other things I will compare accuracy, performance, scalability and ease of use of two processes.  We’ll see how association models or clustering models of Oracle data miner can complement custom built process that integrates R with Essbase.

Another use case  I would really like to cover is utilizing different statistical models for workforce planning.  Specifically, with  classification models you can predict certain decisions an employee is going to make. 
If you can tailor a compensation and benefits package in a way that will minimize the probability of a specific employee leaving the company, you will achieve a few goals at the same time: minimize the cost related to hiring a new employee, have more accurate workforce planning, and probably will have a happy employee (or at least someone who will not want to leave). 

The difficulty with that kind of POC is that it would require a data set with real compensations, demographics and other HR data to test the predictions. Which is obviously very sensitive, requires approvals, etc. So, if you are interested in partnership and can get approvals for data access - please feel free to reach out.

Since the amount of material I am planning to cover is definitely not for a single post, I decided to break it into several:

  • Data Miner And ORE Introduction And Setup: my experience with installing and configuring ORE. I will refer to Brendan’s book (“Predictive Analytics Using Oracle Data Miner”) for step by step instructions, in my post I’ll just mention some results. Also, this is not an instruction of what you need to do in production environment, but simply a description of my experience, issues I encountered and how I solved them. It may or may not be relevant in other environments or make sense for a seasoned DBA. 
  • Descriptive Statistics With Data Miner
  • Defining Sample Dimensionality With Data Miner Clustering
  • Using Associations And Clustering Results In Regressions in Data miner
  • Dynamic Models Creation With PL/SQL 
  • Integrating Oracle Data Miner with Essbase
  • Comparison of custom Essbase-R integration with ODM-ORE-Essbase integration.



Data Miner And ORE Setup


As I mentioned, at Kscope16 I attended a session of Brendan Tierney, then bought his book “Predictive Analytics Using Oracle Data Miner”, and decided to see how it can complement/improve/replace a custom built process that integrates Essbase with R.   
Also, instead of using stand-alone R we are going to use ORE (Oracle R Enterprise) and some native Oracle data mining algorithms.

Among the advantages of using ORE are:

  1. Data stays in Oracle. No need to move it to R server. In the example I was showing earlier (Essbase Meets R) I was using ODBC adapter for R. You don’t necessarily have to do extract and transform data outside of Oracle. You can clean the data in the query or PL\SQL procedure, and supply a final data set to R. But you are still moving the data over the network (if your R framework is installed on a separate server - which is probably the case). And you do need to load the sample into R’s memory. 
  2. Native Oracle security. Again, if you had to extract the data into another target system, you would have to deal with security. But with ODBC connection you can use predefined Oracle accounts.


In order to use Data Miner you need to create ODM schema and DM repository. That’s the place where your workflows, nodes, results and other DM objects are stored. 

My first observations are about installing Oracle Advanced Analytics Option and making it work. I have to say that i am not a DBA, but simply worked as a developer with Oracle for quite some time, and i did install different flavors of Oracle database on linux and Windows machines. The VM environment i wanted to use for Oracle data mining has Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit. I didn’t want to have a clean database install since all my data marts and repositories from prior R integration were already in this database. Here are a few traps I fell into (again - if i worked with experienced DBA it would save me quite some time).

  • Oracle Data Mining repository can be created only in pluggable database. I tried to update the SQL scripts with “container” users (which was a bad idea regardless), but eventually got specific error telling that repository can only be created in a pluggable database.
  • I did not have Oracle Database Examples installed. This would prevent me using text mining algorithms. 
  • Once the data mining repository is created we can create a simple workflow (say a Data Source and Explore Data nodes). It would look like this:



You can notice little green checkboxes in the right top corner of each node. That meant the workflow was already run. It took me awhile to actually make it run, apparently since DB scheduler was turned off. If you run the following statement:

select value from v$parameter where name='job_queue_processes';

You may find that the value is 0. And as Oracle database reference mentions “If the value of JOB_QUEUE_PROCESSES is set to 0, then DBMS_JOB jobs and Oracle Scheduler jobs will not run on the instance.” To turn it ON, run the following command to update the value:

ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 100;

Once we ran the workflow we can right click Explore Data node and select “View Data”. There we can see some nice histograms by dimension. But we’ll see in the next post how those can be used.


Data Miner SQL Query

An interesting feature of SQL Query is that you can call R scripts if you have Oracle R Enterprise installed on the server. Technically, it’s not the feature of ODM, and you can call R scripts from SQL in general once the prerequisites are met. This document describes steps require to install ORE. Specifically on my VM i had to do the following:

Install Oracle R distribution (see documentation here)
Update public-yum-xxx.repo to include xxx_addons 
yum install R-Rversion
Download R enterprise server
Run ORE install (./server.sh -i)

At this point a new tab appeared in SQL Query node, and you can write your first embedded R script in ORE:



But as you can see, not so fact. Apparently we missed a step of installing supporting packaged. Although the admin guide says “On the server, the Oracle R Enterprise packages are installed automatically by the Oracle R Enterprise Server installation script”,  it does not relate to supporting packages, one of which is Cairo.

To install the package from R we can use install.packages(“Cairo”) command:


But it actually fails with “configure error: cannot find cairo.h”



If we install another supporting package mentioned in the admin guide - “arules” - it works just fine, so the problem is with that particular Cairo package.





After a couple of burned hours and some research i found that cairo-devel.x86_64 needs to be installed as well. 

yum install cairo-devel.x86_64

It moved us one step further, but we are not there yet. Now we get “xlib-backend.c:34:74: error: X11/Intrinsic.h: No such file or directory”



After installing the following packages finally Cairo package was installed successfully.

yum install libX11
yum install libX11-devel
yum install libXt
yum install libXt-devel



Lets validate our dataminer function now. This time we get:

Error in .ore.embed.initCairo(pngargs, imglst.name) :
 The package 'png' is required



Finally, we install png package
install.packages(“png”)
And we pass the validation:



Let me reiterate: we don’t have to use data miner workflow or any of its nodes to use the prebuilt R script. Once R and its packages are installed we can run the same query from SQLPlus or SQl developer query, or use it anywhere else in our code:


Below is an example of PNG output. The following query generates a graph which is stored in the database in CLOB field.  

select *
from table(rqTableEval(cursor(select DATA from V_FACT_CC_PAR3), NULL,
'PNG',
'RQG$hist'))



At this point we are ready to proceed to really cool stuff - integrating Essbase with Data Miner and ORE. 

Tuesday, July 26, 2016

Scrambler Update

A couple of weeks ago Cameron pointed out that the scrambler utility has a bug. If i try to save the outline from API, and the outline has warnings (say more than 100 children of dynamic member), it will throw an error and the outline will not be saved.
If you validate from EAS this is what you get:
And if you change the storage type of the problematic parent to “store” then the utility works fine.

In other words, I need to suppress the warning and force it to save the outline anyways.

The solution was rather trivial, and if you use API from time to time you probably know it. But for me it was somewhat counterintuitive, so i decided to post it in case someone gets the same issue.

If you use restructure option
otl.save(IEssCube.EEssRestructureOption.KEEP_ONLY_LEVEL0_DATA);

It automatically does outline verification, and then saves the outline with whatever restructure option. It doesn't have an option to ignore verification errors and save the outline.

I incorrectly assumed that if you could clear all the data and then save the outline without restructure/verification option, like this:

targetCube.clearAllData();
otl.save();

I saw that the verification warning is not displayed, but the outline is not saved either. Again, i incorrectly assumed that is because of the underlying error/warning.
Apparently, the outline did not save because even though i delete all the data, i still need to explicitly issue restructuring command, like this:

otl.save();
otl.restructureCube(IEssCube.EEssRestructureOption.KEEP_ONLY_LEVEL0_DATA);

If you use those two commands instead of previous one, verification errors will be ignored. Pretty simple.