[Tips] Python - Create A User-defined Exception

  

Scenario

Tableau - Relationships : How LoD Works

In Tableau’s new data model, a logical layer is introduced over the physical layer. Tables on the logical layer are called logical tables, which are related to each other through the fields specified on the canvas. The physical layer is what the model used to be, where we can join and union tables. A very critical concept we would like to place emphasis on is that relationships don’t join all logical tables together as one merged table, indeed, they are like contracts describing how tables are related to each other. The timing for joining tables is upon analysis, namely, creating a viz. At that time, the join type will be determined based on the dimensions and measures used in the viz, and only the relevant data will be queried from the relevant tables. When click on View Data button on Data panel, we can see data records from each independent table but can’t see the combined records across all tables. 

As Tableau Help states, “Relationships are a dynamic, flexible way to combine data from multiple tables for analysis.”

As to relationships, the references listed on the bottom of this blog give a complete view. So, we won’t try to cover all the details here, on the contrary, we will look into several typical scenarios and see how relationships work, especially from the perspective of level of detail.

The model stated below will be used for the demonstration. TblSubA and TblSubB, joining on physical layer, form TblMain that acts as the main logical table. TblMain is related to TblHigher and TblSame and TblLower that have a higher, same and lower granularity against TblMain, respectively.

Data Model

 

Physical Layer 

Relationship between TblMain and TblHigher

 

Relationship between TblMain and TblLower 

Relationship between TblMain and TblSame

Sample records from each table are shown below.

Each table contains a measure.
Each table contains at least one record unmatched with the related table. 

TblMain has a granularity of Region – Branch – Employee.

 

Data Sample in TblMain

 TblHigher’s granularity is at Region level. One note, Constant field is not used in this post.

Data Sample in TblHigher 

TblSame take the same granularity as TblMain.

Data Sample in TblSame 

TblLower goes down to Product Category level compared with TblMain. It has a granularity of Region – Branch – Employee – Product Category. 

Data Sample in TblLower

 

Scenario : TblMain – TblHigher

First, we take a look at a classical case, calculating the ratio of (Sum of Sales / Sum of Goal) per region, which is previously solved by LOD expression or data blending. The LoD of the view is defined by Region which we get from TblHigher. Goal measure comes from the same table as Region dimension. Sales measure is taken from more granular table TblMain (Region – Branch – Employee).

Sum of Sales per region is correctly computed. So is Sum of Goal, as the following viz indicates. 

Then we add Sum of Sales / Sum of Goal to Marks, as a result, we get the exact ratio as expected. It is quite straightforward. And more, this data source can be published as one to Tableau server. 

Here is the summary data used in the view. 

Let’s do one more test, adding Branch from TblMain to the view. View’s LoD goes down to Region – Branch. This causes that Goal is duplicated as many as number of branches in a region, as illustrated in the screenshot below.

 

Scenario : TblMain – TblSame

When the tables have the same granularity, any LoD of view won’t produce duplicate data. For example, we would like to discover whether there is any correlation between Sales and Discount.

We bring Employee ID into the view. So, the view shares the same LoD with the data source which is made of TblMain and TblSame in this case.

Let’s try to roll the LoD up to Region. It works perfectly without any surprise. 

Since TblMain contains a record not matched in TblSame, and vice versa. It is a good example for us to explore more about how this is handled.

We drag and drop a dimension and a measure from TblMain to the view. This simply queries data from only TblMain, as we can see, there are no null values in the view. 

 Then, we add a measure from TblSame into the view, let’s see what will change.

Drag and drop Discount on Detail box in Marks. Obviously, a null value is included in the view. The underlying data also says so.

 If we add a dimension instead of a measure from TblSame to the view, will this make a difference? Yes, we won’t see a null value popping up on the view.

XnoneA is not findable in TblSame, so its Name(TblSame) is null; however, this won’t affect how the view presents.

For null values, we can use measure filter to filter them out.

Scenario : TblMain – TblLower

The pair works as similarly as TblMain – TblHigher does. When the LoD of view is kept not lower than TblMain’s, everything works well. Of course, null values show up in the view. But this is expected since we have measures from both tables in the view. 

LoD of View: Region

LoD of View: Region- Branch - Employee

What if we bring Product Category dimension to the view? LoD of view is less than that of TblMain. This leads to duplicated Sales (from TblMain), as the screenshot shows.

LoD of View: Region – Branch – Employee – Product Category

Scenario : TblMain – TblHigher – TblLower

This is multiple tables related scenario. In effect, we can imagine how they work based on the results of the above scenarios. So, we would like to test some cross-table calculations including LoD expressions here.

First, we look at the numbers at regional level, and we use text table for illustration this time. The measures are listed up as follows.

Goal from TblHigher (LoD: Region)

Sales from TblMain (LoD: Region – Branch – Employee)

Sales (TblLower) from TblLower (LoD: Region – Branch – Employee – Product Category) 

And, we also include a cross-table calculation into the view.

CompleteRatio = SUM([Sales]) / SUM([Goal]) 

The text table gives us the result exactly as expected. 

Subsequently, we start to add more dimensions to the view, in other words, lowering LoD. Let’s see what is going to happen. 

We add Branch (TblLower) to Rows shelf, as a subsequence, Goal is duplicated as Totals in chart tell because Goal comes from TblHigher.

Now, we create a new LoD calculation named by RegionalGoal and add it to the text table.

RegionalGoal = {FIXED [Region]:SUM([Goal])} / {FIXED [Region]:COUNT([Branch])} 

Total of column shows it removes the duplicates.

What if we add the following calculation to the view? 

RegionalGoal (TblLower) =

{FIXED [Region (TblLower)]:SUM([Goal])} / {FIXED [Region (TblLower)]:COUNT([Branch (TblLower)])}

 It gives a different result from RegionalGoal. 

What does cause the difference? Let’s examine the underlying data.

Divisors, {FIXED [Region]:SUM([Goal])} and {FIXED [Region (TblLower)]:SUM([Goal])}, produce the same result.

Dividends, {FIXED [Region] : COUNT([Branch])} and {FIXED [Region (TblLower)] : COUNT([Branch (TblLower)])}, produce different counts, as we can see from the below screenshots.

As we go down to TblLower, the number of branches associated with region Chubu is exactly same as that on the screenshot.

Noted LoD expressions behave differently against common calculations. Moreover, we can smell Order of Execution here. But we are not going to touch more in this blog.

For reference, if we modify the formula a little, using COUNTD in place of COUNT, it will give the same result as RegionalGoal. 

RegionalGoal (TblLower) =

{FIXED [Region (TblLower)]:SUM([Goal])} / {FIXED [Region (TblLower)]:COUNTD([Branch (TblLower)])}

 

Let’s move back to the track and see how the view looks if we add Name (Employee) and Product Category from TblLower to the view. So the view has the same LoD as TblLower.

After we checked out the text table, measures from TblLower or having TblLower’s LoD are computed correctly, as Totals indicate.

Regarding cross-table calculations, here is an excerpt from Tableau Help, for reference.

“Be aware that cross-table calculations use inner join query semantics by default. The calculation lives on top of the join between the logical tables that it references. Also, it is now possible to write row-level calculations across tables that result in outer joins.”

Scenario – Constant

 Let’s create a calculation called ConstantOne that is equal to 1 and add it to the view. The view simply shows 1 on the chart.

What about we drag and drop Branch from TblMain on Detail box in Marks? Sum of ConstantOne equals to the number of branches.

 Then, add Product Category from TblLower to the view. Sum of ConstantOne increases accordingly. 

So we can draw a conclusion, ConstantOne takes the LoD of dimensions in the view.

Relationships are used when combining data from different LoD, and support full outer joins As well. The related fields are fixed, not like Data Blending, we can assign key fields for each view. We can also adjust Cardinality and Referential Integrity settings on Data Source tab to optimize queries. After related necessary logical tables, we can publish them as one data source. But we can’t relate published data sources, and we can’t use calculated fields or geographic fields for defining relationships.

References:

 Relate Your Data – Tableau Help

Relationships, part 1: Introducing new data modeling in Tableau

Relationships, part 2: Tips and Tricks

Relationships, part 3: Asking questions across multiple related tables

Machine Learning - Build A GradientBoostingRegressor Model

This post is going to demonstrate the entire process of building a machine learning model. Model deployment will not be covered in this blog.

About Model Selection

Model selection is a critical part of building a machine learning model. In practice, the candidate models need to be tested with the sufficient data and be properly evaluated. In this post, the Gradient Boosting Regressor model is selected based on comparison on paper. As an ensemble model, it gives the best result produced by the specified number of underlying decision trees. And decision tree is an ideal model that we can use grid search to find the best hyperparameters. This model probably can efficiently and effectively achieve the expected result.

Supervised learning will be selected for this exercise.

 

Data Preprocessing

The original temperature data is scraped from a mountain hiking website. But the scraping process is not included in this blog. The temperature data along with the coordinates of the mountains is loaded into the predefined table in the database. We could say the data engineering part is almost done here.

Data Samples

Encoding processing is not required as there are no categorial features used by the model in the dataset. Other data transformation and data conversion are not in scope as well. Considering the model will accept the coordinates and altitude inputted by user to make a temperature prediction after deployed, we don’t standardize the features in this exercise. As a reference, I tried standardized features and discovered the model produces the same output as the non-standardized does.

No need to mention, A few cleansing tasks must be done before it is fed to the machine learning model. Mainly they are missing data handling and column extraction. After examined the data, we found nulls in the temperature columns which cannot be simply replaced with a zero or mean value. The rule is to exclude them from the dataset. Additionally, the dataset contains a part of features which will not be used by the model, so that part will also be removed. The features in need are Latitude, Longitude, and Altitude, and the target is Avg (average temperature). Avg instead of Max or Min is chosen because it reduces the effect of an extremely high or low temperature corresponding to a particular weather at some point. 

    #
    # drop null data
    df = dforg.dropna()
    print(df[df["Avg"].isna()])
    #
    # extract the features and the target
    X = df[["Latitude", "Longitude", "Altitude"]]
    y = df["Avg"]

 

Dataset Preparation

To do this, we simply borrow train_test_split from sklearn.model_selection, with the proportion of test data as well as random state specified. 

    #
    # divide the dataset into training dataset and test dataset
    X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.3, random_state=123) 

Train the Model

After the data prepared, we create an instance of GradientBoostingRegressor, and then call its fit() member function to fit the model.

    #

    # create the model

    gbrd = GradientBoostingRegressor(random_state=123)

    #

    # train the model

    gbrd.fit(X_train, y_train)

 

Make a Prediction

We pass the test dataset to the fitted model and get the estimated temperatures in return. The predictions will be evaluated and visualized in later steps.

    #
    # make a prediction
    y_pred = gbrd.predict(X_test)

Model Evaluation

It's time to see how well the model performs now. The evaluation is carried out through comparing the score given by the model and root mean squared error (RMSE). The score computed by the model is in deed the same as the coefficient of determination (R2). Technically, the higher the score, the better the model. But we need to consider if the model is over-fitting at the same time. 

     #
    # evaluate the model
    scored = gbrd.score(X_test, y_test)
    #
    # save the predicted and actual temperatures
    tmp_pred = y_pred.reshape(np.size(y_pred), 1)
    tmp_test = y_test.values.reshape(np.size(y_test), 1)
    tempd = pd.DataFrame(np.hstack([tmp_test, tmp_pred]))
    rmsed_test = np.sqrt(mean_squared_error(y_test, y_pred))

  

Hyperparameter Optimization

A combination of Grid Search and Cross Validation is employed to search for the best parameters. The result may vary according to the parameter grid we pass to GridSearchCV. So, this optimization process is iterative until we can find the most practical ones. In this example, we first use the default parameters for the estimator, then give a try to the following parameters.

    parameters = {
        'n_estimators' : [3, 5, 10, 30, 50, 100],
        'max_features' : [1, 3, 5, 10],
        'random_state' : [123],
        'min_samples_split' : [3, 5, 10, 30, 50],
        'max_depth' : [3, 5, 10, 30, 50]
    }

As we can tell from the graph before and after optimization, the score goes up a little and the predicted values are more correlated with the actual ones. During the fitting, the random state is fixed as stated in the codes. An unfixed random state is also tested but it turned out that a fixed one has a better performance. 

best params: {'max_depth': 3, 'max_features': 3, 'min_samples_split': 3, 'n_estimators': 30, 'random_state': 123}

  

Visualization

A BI tool can be used for visualization. In data analysis, the estimated results in fact can be fed back into the BI tool in many ways, for example, the results are written into a database table where the tool will read the data, or the BI tool can integrate with the Python program through an engine. Here we use matplotlib to visualize the results. The embedded codes easily transform the data into a visual graph, costly effective.

Predicted vs Actual

    #

    # visualize the results

    plts.use('ggplot')

    fig, ax = plt.subplots()

    #

    # default parameters

    ax.scatter(tempd.iloc[:, 0], tempd.iloc[:, 1], color='darkblue', label='default')

    #

    # optimized parameters

    ax.scatter(tempb.iloc[:, 0], tempb.iloc[:, 1], marker='x', color='crimson', label='optimized')

    #

    # reference

    xtmp = np.array([np.min(tempd.iloc[:, 1]), np.max(tempd.iloc[:, 1])])

    ytmp = xtmp.copy()

    ax.plot(xtmp, ytmp, label='reference', color='gray')

    #

    # show scores and correlation rates

    strd = 'score: ' + str(scored) + '   rmse: ' + str(rmsed_test)

    strb = 'score: ' + str(scoreb) + '   rmse: ' + str(rmseb_test)

    ax.text(xtmp.min(), ytmp.max(), strd, color='darkblue')

    ax.text(xtmp.min(), ytmp.max()-1,strb, color='crimson')

    #

    # graphical setting

    ax.legend(loc='lower right')

    ax.set_xlabel('actual temperature')

    ax.set_ylabel('predicted temperature')

    fig.suptitle('mountain temperature prediction')

    plt.show()

 

Save the Model

The trained model with optimized hyperparameters assigned is saved as a binary file. The pickle library is perfectly suited to process this.

     with open('MountTempModel.pkl', mode='wb') as f:

        pickle.dump(gbrb, f)

To deploy the model, we can call pickle.load to instantiate it. We'll discuss it in another blog.

 

A Note

If we only look at the scores, the model seems to perform well for this particular case. The optimization finds a better estimator as we expected. Later, I used the model to get the predicted temperature for the training data, and calculated its RMSE as well. The RMSE is by far smaller than the test data's RMSE. We could came to a conclusion that the model is actually over-fitting.

I am thinking to come up with a post which will build and compare a series of selected models for a specified dataset. So how well they perform will be visualized on the same graph. Hopefully I could streamline the process as simple as that we feed the input dataset and then get the visual evaluation results for all those models. (Please refer to Machine Learning - Build And Compare Regression Models)

The complete Python program is attached below.

import pandas as pd

import numpy as np

import pickle

from datetime import datetime

import oracledb

from sklearn.model_selection import train_test_split

from sklearn.ensemble import GradientBoostingRegressor

from sklearn.model_selection import GridSearchCV

import matplotlib.pyplot as plt

import matplotlib.style as plts

from sklearn.preprocessing import StandardScaler

from sklearn.metrics import mean_squared_error

def PredictMountTemp():

    #

    # load the dataset from the database

    sqlstr = 'select * from mount_temp'

    columns = ['No', 'Mountain', 'Latitude', 'Longitude', 'Altitude', 'Max', 'Avg', 'Min', 'Prefecture']

    try:

        with oracledb.connect(user="test", password='1234', dsn="localhost/xepdb1") as conn:

            with conn.cursor() as cursor:

                dforg = pd.DataFrame(cursor.execute(sqlstr), columns=columns)

    except oracledb.Error as e:

        print(f'Failed to fetch data from the database ({str(e)})')

        return

    dforg = dforg.set_index('No')

    #

    # drop null data

    df = dforg.dropna()

    print(df[df["Avg"].isna()])

    #

    # extract the features and the target

    X = df[["Latitude", "Longitude", "Altitude"]]

    y = df["Avg"]

    print(pd.concat([X, y], axis=1))

    #

    # divide the dataset into training dataset and test dataset

    X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.3, random_state=123)

    print(X_train.shape, X_test.shape, y_train.shape, y_test.shape)

    #

    # create the model

    gbrd = GradientBoostingRegressor(random_state=123)

    #

    # train the model

    gbrd.fit(X_train, y_train)

    #

    # make a prediction

    y_pred = gbrd.predict(X_test)

    #

    # evaluate the model

    scored = gbrd.score(X_test, y_test)

    #

    # save the predicted and actual temperatures

    tmp_pred = y_pred.reshape(np.size(y_pred), 1)

    tmp_test = y_test.values.reshape(np.size(y_test), 1)

    tempd = pd.DataFrame(np.hstack([tmp_test, tmp_pred]))

    rmsed_test = np.sqrt(mean_squared_error(y_test, y_pred))

    #

    # using Grid Search to find the best estimator

    starttime = datetime.now()

    print('Grid search, starting from: ', starttime.isoformat())

    parameters = {

        'n_estimators' : [3, 5, 10, 30, 50, 100],

        'max_features' : [1, 3, 5, 10],

        'random_state' : [123],

        'min_samples_split' : [3, 5, 10, 30, 50],

        'max_depth' : [3, 5, 10, 30, 50]

    }

    gbrb = GridSearchCV(estimator=GradientBoostingRegressor(), param_grid=parameters, cv=10)

    gbrb.fit(X_train, y_train)

    #

    # time consumed

    endtime = datetime.now()

    print('Grid search, ending at: ', endtime.isoformat())

    print('Time consumed for optimization: ', (endtime-starttime))

    #

    # best estimator

    print('Best params: {0}'.format(gbrb.best_params_))

    print('Best estimator: {0}'.format(gbrb.best_estimator_))

    y_pred = gbrb.predict(X_test)

    scoreb = gbrb.score(X_test, y_test)

    #

    # save the predicted and actual temperatures

    tmp_pred = y_pred.reshape(np.size(y_pred), 1)

    tmp_test = y_test.values.reshape(np.size(y_test), 1)

    tempb = pd.DataFrame(np.hstack([tmp_test, tmp_pred]))

    rmseb_test = np.sqrt(mean_squared_error(y_test, y_pred))

    #

    # visualize the results

    plts.use('ggplot')

    fig, ax = plt.subplots()

    #

    # default parameters

    ax.scatter(tempd.iloc[:, 0], tempd.iloc[:, 1], color='darkblue', label='default')

    #

    # optimized parameters

    ax.scatter(tempb.iloc[:, 0], tempb.iloc[:, 1], marker='x', color='crimson', label='optimized')

    #

    # reference

    xtmp = np.array([np.min(tempd.iloc[:, 1]), np.max(tempd.iloc[:, 1])])

    ytmp = xtmp.copy()

    ax.plot(xtmp, ytmp, label='reference', color='gray')

    #

    # show scores and correlation rates

    strd = 'score: ' + str(scored) + '   rmse: ' + str(rmsed_test)

    strb = 'score: ' + str(scoreb) + '   rmse: ' + str(rmseb_test)

    ax.text(xtmp.min(), ytmp.max(), strd, color='darkblue')

    ax.text(xtmp.min(), ytmp.max()-1,strb, color='crimson')

    #

    # graphical setting

    ax.legend(loc='lower right')

    ax.set_xlabel('actual temperature')

    ax.set_ylabel('predicted temperature')

    fig.suptitle('mountain temperature prediction')

    plt.show()

    #

    # save the trained model

    with open('MountTempModel.pkl', mode='wb') as f:

        pickle.dump(gbrb, f)

[Tips] Oracle - MERGE

 

Scenario

[Tips] Oracle - Multiple Tables INSERT

Scenario

Tableau - LOD Expressions

 

Before we dive into LOD expressions, namely, FIXED, INCLUDE and EXCLUDE, let's take a look at data source level of detail and view level of detail.  To make it clear, data source refers to the dataset appearing on Data pane on the left side of Tableau window, rather than the one in a database. It may be processed through Tableau Prep or Physical layer on the data model. Be noted, relationship in the new data model combines data from more than one tables in a dynamic and flexible way. To demonstrate the concept of LOD expressions in an easier manner, we will not discuss relationship here.

Simply put, data source has the finest granularity which can only be rolled up in the visualization. Data in a view is summarized by dimensions integrated into the view. As shown on the picture below, any dimension dropped into the red boxes will affect the granularity of data used in the view. They all together consist of the view level of detail. (Noted the super store dataset is employed for the demonstration.)

• Country
• Month Of Order Date
• Product Sub Category
• Ship Mode

Figure 1: View Level of Detail

You can right click on the view and select View Data menu to check the data set behind, as shown below. The measure value is summed up by the above dimensions.

Figure 2: Underlying Data

On the other hand, the original data source contains more dimensions, for example, City, Customer Segment, Product Category etc., as shown on the following figure. 

After we built a concept of data source level of detail and view level of detail, it becomes easier to understand LOD expressions. In nature they bring a summarized measure value with a different LOD into the view. 

FIXED

The expression is to calculate the measure by the dimensions designated in the formula. The calculation will not be affected by the view level of detail.

EXCLUDE

EXCLUDE expression will take the specified dimension away from the view level of detail while calculating the measure. 

Let's take a look at the example below. The view shows the quarterly sales for each product category. The view LOD is defined by Order Date (Quarterly) and Product Category. In case that we want to present the total sum of all product categories at the same time, let's see how EXCLUDE expression can help us achieve this.

Drag and drop Sales to Detail box in Marks card.

Replace the aggregation function with the following expression.

{EXCLUDE [Product Category] : SUM([Sales])}

Change it from Measure to Attribute using the context menu.

Add it to Tooltips as shown below.

As expected, we get the total amount of sales per quarter..

One note, EXCLUDE expression is not suitable for row-level calculations, as Tableau Desktop Help states.

"EXCLUDE level of detail expression cannot be used in row-level expressions (where there are

no dimensions to omit), but can be used to modify either a view level calculation or anything in

between (that is, you can use an EXCLUDE calculation to remove dimension from some other

level of detail expression)." - Create Level of Detail Expressions in Tableau Section, Tableau Desktop and Web Authoring Help

INCLUDE

INCLUDE expression will do the calculation by integrating the specified dimensions into the view level of detail. So it will further down the granularity of data in the view.

The below bar graph shows the total sales per quarter. If we would like to display the average sales per category on this chart, INCLUDE expression is a good option for this. On the second thought, I wouldn't see much business meaning to do so, considering the point is to demonstrate how INCLUDE expression works, so let's start with building the formula.

Create a calculated field named by SalesPerCategory as below.

SalesPerCategory = {INCLUDE [Product Category] : SUM([Sales])}

Drag and Drop SalesPerCategory on Label box.

Change the aggregation type to average. You'll see the category averages on top of each bar.

[Tips] Python - Access Oracle

 

Scenario