<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Allan Ouko on Medium]]></title>
        <description><![CDATA[Stories by Allan Ouko on Medium]]></description>
        <link>https://medium.com/@allanouko17?source=rss-a86d3ccfa1f3------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*bbdF9dmJn0pPaqti5QcorQ.jpeg</url>
            <title>Stories by Allan Ouko on Medium</title>
            <link>https://medium.com/@allanouko17?source=rss-a86d3ccfa1f3------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Fri, 20 Nov 2026 10:06:03 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@allanouko17/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Customer Churn Prediction Using Machine Learning]]></title>
            <link>https://medium.com/@allanouko17/customer-churn-prediction-using-machine-learning-ddf4cd7c9fd4?source=rss-a86d3ccfa1f3------2</link>
            <guid isPermaLink="false">https://medium.com/p/ddf4cd7c9fd4</guid>
            <category><![CDATA[python]]></category>
            <category><![CDATA[machine-learning]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[churn-prediction]]></category>
            <category><![CDATA[predictive-analytics]]></category>
            <dc:creator><![CDATA[Allan Ouko]]></dc:creator>
            <pubDate>Tue, 21 May 2024 09:49:29 GMT</pubDate>
            <atom:updated>2024-12-10T21:01:24.437Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*WZdoYPpmiIk1AcPQ1YHWug.png" /><figcaption>Churn</figcaption></figure><h3>Customer churn prediction overview</h3><p>Customer churn prediction predicts the likelihood of customers canceling a company’s products or services. In most cases, businesses with repeat clients or clients under subscriptions strive to maintain the customer base. Therefore, it is important to keep track of the customers who cancel their subscription plan and those who continue with the service. This approach requires the organization to know and understand their client’s behavior and the attributes that lead to the risk of the client leaving. I will explain the steps necessary in creating and deploying a churn prediction machine-learning model.</p><h4><strong>Why Predict Customer Churn?</strong></h4><p>It is important for any organization dealing with repeat clients to find ways to retain existing ones. The approach is crucial since customer churn is expensive, and acquiring new clients is more expensive than retaining existing ones. Consider an internet service (ISP) provider who has acquired a new user. They will need technicians and hardware to connect the latest client to their service. The client will only be required to pay the subscription fee to continue using their plan. If the user fails to renew their service, the company will most likely be at a loss, especially if the trend continues for several customers. The monthly recurring revenue (MRR) for such an institution will likely be low; hence, it will be unable to sustain the business. Thus, a reliable churn prediction model should help companies stay afloat as they scale up and attract more customers.</p><h4><strong>Case Study of Customer Churn Prediction Model</strong></h4><p>Creating churn prediction models involves using historical customer data to predict the likelihood of the current customer leaving or continuing with a particular service/product. The data used for the predictive models include product usage data and direct customer feedback. Besides, the predictive models identify the different trends and patterns in the data to forecast customer churn.</p><p><strong>Scenario</strong></p><p>Consider an e-commerce company with historical data on how its clients have interacted with its services. The company wants to know the likelihood of customers churning so it can launch targeted marketing campaigns.</p><p><strong>Data source and GitHub repo</strong></p><p>The data is available here, <a href="https://www.kaggle.com/datasets/ankitverma2010/ecommerce-customer-churn-analysis-and-prediction/data">Kaggle</a>.</p><p>The <a href="https://github.com/AllanOuko/customer-churn-prediction-application">GitHub </a>repo for the whole project</p><p><strong>About the dataset</strong></p><p>The data is in .xlsx format with the following features:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/850/1*ngWCh7GbYAzmDPRtMJiMpQ.png" /></figure><p>The dataset had no duplicate rows. Dropping the CustomerID column, which is each customer&#39;s unique identifier, has no effect on predicting churn.</p><p><strong>EDA and Plots</strong></p><p>Visualizing the categorical columns indicates:<br>1. About 83.2 % of the customers were retained, while 16.8% churned.</p><p>2. The company also has more male than female clients.</p><p>3. Most clients prefer logging in from their mobile phones to their phones and computers.</p><p>4. Most clients spend an average of 2 and 4 hours on the company’s app.</p><p>5. Most customers have about 3 or 4 devices registered for the retailer’s app.</p><p>6. Most customers prefer debit and credit cards to make payments.</p><p>7. City tier 2 has the lowest number of customers.</p><p>From the figure, notice that many customers placed their first and second orders, and the number reduced in subsequent orders. Additionally, the number of customers reduced a week after their last order.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*asou0LtTUdnX7ZIhmQ_VCQ.png" /></figure><p><strong>Churn Distribution</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*x9pgnoqeQGi-ezimhh70Hw.png" /></figure><h4>Choosing the Right Machine Learning Model</h4><p>You need to build a machine-learning model to predict customer churn. Therefore, you must choose the appropriate classification model since the target class (churn) consists of a discrete class of features (Yes and No). The classification model is suitable because it is a supervised model that uses historical data to find patterns in customer churn behavior. However, you should use regression models if the data has continuous values on the target class.</p><h4>Data Preprocessing</h4><p>To build a machine learning model with high accuracy, one needs to preprocess the data to reduce its complexity. Since the data had missing values, there is a need to impute these values appropriately.</p><pre>from sklearn.impute import SimpleImputer  # Imports SimpleImputer for handling missing data with basic strategies.<br>from sklearn.experimental import enable_iterative_imputer  # Enables the experimental IterativeImputer in scikit-learn.<br>from sklearn.impute import IterativeImputer  # Imports IterativeImputer for advanced imputation techniques using iterative models.<br>from sklearn.ensemble import RandomForestRegressor  # Imports RandomForestRegressor for regression tasks using ensemble methods.<br>import pandas as pd  # Imports the pandas library for data manipulation and analysis.<br><br>def fill_missing_values(df, random_state=None):<br>    # Step 1: Identify numeric and categorical columns<br>    numeric_columns = df.select_dtypes(include=[&#39;float64&#39;, &#39;int64&#39;]).columns.tolist()<br>    categorical_columns = df.select_dtypes(include=[&#39;object&#39;]).columns.tolist()  # Include both string and category data<br><br>    # Step 2: Impute numeric columns<br>    numeric_imputer = SimpleImputer(strategy=&#39;mean&#39;)<br>    df[numeric_columns] = numeric_imputer.fit_transform(df[numeric_columns])<br><br>    # Step 3: Handle categorical columns<br>    for col in categorical_columns:<br>        if df[col].dtype == &#39;object&#39;:<br>            # Convert categorical column to one-hot encoded representation<br>            encoded_cols = pd.get_dummies(df[col], prefix=col)<br>            # Concatenate one-hot encoded columns<br>            df = pd.concat([df.drop(col, axis=1), encoded_cols], axis=1)<br><br>    # Step 4: Random Forest Iterative Imputer for the entire DataFrame<br>    rf_imputer = IterativeImputer(estimator=RandomForestRegressor(random_state=random_state))<br>    df = pd.DataFrame(rf_imputer.fit_transform(df), columns=df.columns)<br><br>    return df<br><br># Call the function to fill missing values<br>df = fill_missing_values(df, random_state=42)</pre><p>The data had mixed data types, so the Random Forest Iterative Imputer was appropriate for filling in the missing values due to its high accuracy. The first imputation step involved identifying the dataset’s numeric and categorical columns to impute separately. After that, the missing values in the numerical columns were imputed using the mean. The categorical columns were converted to a one-hot encoded representation and then concatenated. The final step involved initiating the Random Forest Iterative Imputer for the entire data frame.</p><p><strong>Splitting Data to Training and Testing Dataset</strong></p><pre># Split model into training and test set<br>X = df.drop(columns=[&quot;churn&quot;])<br>y = df[&quot;churn&quot;]<br><br>X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=0)</pre><p>This step ensures there are different datasets for training and testing the machine learning models.</p><p><strong>Oversampling using SMOTE</strong></p><p>Since the target variable ‘Churn’ was highly imbalanced, balancing this target feature on the training dataset is important. The Synthetic Minority Over-sampling Technique (SMOTE) is the appropriate method to generate synthetic samples of the minority class to balance the target variable and improve model performance during model training.</p><pre>from imblearn.over_sampling import SMOTE<br><br>print(&#39;Before upsampling count of label 0 {}&#39;.format(sum(y_train==0)))<br>print(&#39;Before upsampling count of label 1 {}&#39;.format(sum(y_train==1)))<br># Minority Over Sampling Technique<br>sm = SMOTE(sampling_strategy = 1, random_state=1)   <br>X_train_s, y_train_s = sm.fit_resample(X_train, y_train.ravel())<br>                                         <br>print(&#39;After upsampling count of label 0 {}&#39;.format(sum(y_train_s==0)))<br>print(&#39;After upsampling count of label 1 {}&#39;.format(sum(y_train_s==1)))</pre><h4>Models Evaluation</h4><p>The different classification models included the following, ranked from the highest to the least performing.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/619/1*bsC8zGebxn-WFQtw3Bte9g.png" /><figcaption>Models Evaluation</figcaption></figure><p>The information shows that the XGBoost classifier was the best performer with high test accuracy and f1 score. Therefore, the final customer churn prediction model would be built based on the XGBoost classifier.</p><p>Below is a visualization of the feature weights, which indicate the importance of each feature in predicting customer churn.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*oNaCtbvmT5iyjwDpHiLMKA.png" /><figcaption>Feature weights</figcaption></figure><p>Therefore, the features that will be used for the final deployment model are:</p><ol><li>Tenure</li></ol><p>2. Cashback amount</p><p>3. City tier</p><p>4. Warehouse to home</p><p>5. Order amount hike from last year</p><p>6. Days since last order</p><p>7. Satisfaction score</p><p>8. Number of addresses</p><p>9. Number of devices registered</p><p>10. Complain</p><p>11. Order count</p><p>12. hourspendonapp</p><p>13. Marital status</p><p>14. Coupon used</p><p>15. Gender</p><p>Drop the unwanted columns from the training and testing dataset in this scenario.</p><p>Since the final deployment model is the XGBoost classifier, convert the Pandas data frame to a NumPy array. The conversion helps load and predict values in the Flask app without encountering an error.</p><pre># Convert the Pandas datafram to NumPy array for the XGBoost classifier. <br># The conversion helps in loading and predicting values in the flask app.<br><br>X_test = X_test.values<br>X_train = X_train.values</pre><pre># Run the model<br>final_model=XGBClassifier()<br>final_model.fit(X_train, y_train)<br>train_pred = final_model.predict(X_train)<br>test_pred = final_model.predict(X_test)<br><br>final_model.score(X_test, y_test)</pre><h4>Saving the Model</h4><p>Now save the model as a pickle file. Also, save the data columns as a JSON file.</p><pre>import pickle<br>pickle.dump(final_model,open(&#39;end_to_end_deployment/models/churn_prediction_model.pkl&#39;,&#39;wb&#39;))</pre><pre>#save the data columns<br>import json<br><br>columns = {&#39;data_columns&#39; : [col.lower() for col in X.columns]}<br><br>with open(&quot;end_to_end_deployment/models/columns.json&quot;,&quot;w&quot;) as f:<br>    f.write(json.dumps(columns))    </pre><h4>Application Building</h4><p>Create the customer churn prediction app using Flask. The input and output interfaces are shown below.</p><p>Given the available customer data, the company can now use customer details to determine whether they would churn. This approach would help the organization determine how to engage with the customers and launch targeted campaigns for customer retention.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*IlbFXkmOwatIg4oD_VmGWQ.png" /><figcaption>Input page</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*dfXi-dh6l7VzQkMgRWRTiw.png" /><figcaption>Results page</figcaption></figure><p><a href="https://github.com/AllanOuko/customer-churn-prediction-application">GitHub </a>Link to the project.</p><p>I recommend exploring the ‘<a href="https://usermotion.com/blog/churn-prediction-model">Building Churn Prediction Model</a>’ blog by UserMotion to learn how to develop personalized churn prediction model applications that enhance decision-making and boost customer retention with high accuracy.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ddf4cd7c9fd4" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Understanding DAX Functions in Power BI]]></title>
            <link>https://medium.com/@allanouko17/understanding-dax-functions-in-power-bi-a874a4ba2515?source=rss-a86d3ccfa1f3------2</link>
            <guid isPermaLink="false">https://medium.com/p/a874a4ba2515</guid>
            <category><![CDATA[data-transformation]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[dax]]></category>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[data-analysis]]></category>
            <dc:creator><![CDATA[Allan Ouko]]></dc:creator>
            <pubDate>Wed, 24 Apr 2024 10:25:30 GMT</pubDate>
            <atom:updated>2024-04-24T10:25:30.796Z</atom:updated>
            <content:encoded><![CDATA[<p>Power BI is a valuable data transformation, analysis, and visualization tool. Analysts prefer creating visual reports using Power BI to communicate insights to internal teams and relevant stakeholders. Therefore, it is essential to understand DAX formulas and Power Query functionalities for data analysis.</p><p>DAX formulas are used to create calculated columns and measures and to model data. However, Power Query loads data from different sources and transforms the data into the required format. Using a sample dataset, I will demonstrate how I used DAX and Power Query to analyze and create a custom report dashboard for a sales company.</p><h4>Loading and Transforming Data</h4><p>The given dataset consisted of four Excel sheets:</p><ol><li>Sales: The sales data contains information on orders, the order date, salesperson, manager, supervisor, quantity, and unit price.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*cIV655fysCxCXFV-X_0NEw.png" /></figure><p>2. Product: The product data contains information about the different products, including the product ID, product name, product group, and product category.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xOL1tJ0FaIcuv-c83-geLw.png" /></figure><p>3. Budget: The budget table consisted of the budget allocated for each salesperson every month for the three years.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*j9iMAeGyJCrA5SlutshMOQ.png" /></figure><p>When loading the sales data, I removed the first three rows and used the resulting first row as a header. I filtered and removed the null row and loaded the data. Similarly, I loaded the product’s table with no further transformations since the data was in the proper format. I loaded the three sheets for the 2019, 2020, and 2021 budget table. The transformations included removing the top null row, promoting the headers, and removing the total column and total rows. Additionally, I filtered the data to remove the duplicate rows. I then unpivote the other 12 columns with the month number to create a dataset, as shown below. I also used the merge queries option to merge the month and year columns into a date column.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2KWXmz2Y-xRMjo3j9sjkPA.png" /></figure><p>To ensure I created an interactive visualization, I duplicated the main sales table, removed other columns, and then retained the columns SalesperronKey, salesperson, supervisor, and manager. I also removed the duplicate rows on the new table to remain with unique rows.</p><h3>DAX Functions</h3><p>Since the main sales table contained a date column, I created a separate calendar table using the ‘CALENDARAUTO’ function. This function makes the date automatically by referencing the main sales table. Using the function, I included the date, year, month, month number, weekday, week number, and quarter.</p><pre>Calendar = <br>ADDCOLUMNS (<br>    CALENDARAUTO(),<br>    &quot;Year&quot;, YEAR([Date]),<br>    &quot;Month&quot;, FORMAT([Date], &quot;MMM&quot;),<br>    &quot;MonthNum&quot;, MONTH([Date]),<br>    &quot;Weekday&quot;, FORMAT([Date], &quot;ddd&quot;),<br>    &quot;WeekNum&quot;, WEEKDAY([Date]),<br>    &quot;Quarter&quot;, Format([Date], &quot;\QQ&quot;)<br>)</pre><p>Moreover, using the DAX function below, I calculated a column to indicate whether the day was a weekday or a weekend.</p><pre>Weekend/Weekday = <br>IF(&#39;Calendar&#39;[WeekNum] IN {1,7},<br>“Weekend”,<br>“Weekday”<br>)</pre><p>I created a year hierarchy to include the year, month, and quarter for visualization purposes. The hierarchy would be necessary when creating a drill-down for the line graphs.</p><p>I also used the modeling view to create a many-to-one relationship between the ‘OrderDate’ and ‘Date’ columns in the two tables.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/385/1*HChF2DMqI3VLXlfogeXG8g.png" /></figure><h4>Measures</h4><p>For further analysis, I created a table called ‘MeasureTable’ to include the various measures I would perform for aggregations and calculations.</p><p>I used the ‘COUNTROWS’ function to return the total number of rows in the sales table to show the number of orders for the company.</p><pre>Transactions = COUNTROWS(FactTable)</pre><p>Additionally, I used the ‘SUM’ function to return the total quantity of goods sold during the given period. This function summed the ‘Quantity’ column from the fact table.</p><pre>TotalQtySold = SUM(FactTable[Quantity])</pre><p>I also used the ‘SUMX’ function on the fact table (sales table) to return the total revenue of the goods sold over the period. This function evaluates the product of the ‘UnitPrice’ and ‘Quantity’ from each row and then sums the value evaluated from each row.</p><pre>TotalRevenue = SUMX(&#39;FactTable&#39;, &#39;FactTable&#39;[UnitPrice] * &#39;FactTable&#39;[Quantity])</pre><p>Using the budget table, I used the ‘SUM’ function to sum the budget amount.</p><pre>TotalBudget = SUM(FactTableBudget[Budget])</pre><p>To create the table below, I had to calculate the percentage of revenue and percent of quantity for the supervisor and salesperson.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/794/1*M4hmj9r0ftE4aVxS0OxmfQ.png" /></figure><p>To calculate the percent of revenue, I used the following DAX function where the variable ‘_FILTERED_VALUE’ creates a new variable that calculates and sums the total revenue regardless of the filters on the salesperson table. The resulting value is stored in the variable ‘_MEASURE_VALUE.’ The code ends with calculating the percentage of revenue by dividing the _MEASURE_VALUE (which is the revenue for a specific salesperson) by the _FILTERED_VALUE (which is the total revenue across all salespersons), using the DIVIDE function. This function handles cases where the divisor is zero, preventing division by zero errors.</p><pre>PctOfRev = VAR _FILTERED_VALUE = <br>CALCULATE(<br>    [TotalRevenue],<br>    REMOVEFILTERS(<br>        DimSalesperson)<br>    )<br>   <br>VAR _MEASURE_VALUE = [TotalRevenue]<br><br>    RETURN<br>    DIVIDE(<br>    _MEASURE_VALUE,<br>    _FILTERED_VALUE<br>    )</pre><p>I utilized the code below for the percentage of quantity (units) sold by each salesperson/manager. The code begins with creating a variable _FILTERED_VALUE, which calculates the total amount sold across all salespersons using the CALCULATE function, to sum up the [Quantity] column from the FactTable, disregarding the filters. Similarly, the code ends with calculating the percentage of quantity by dividing the _MEASURE_VALUE (which is the quantity sold by a specific salesperson) by the _FILTERED_VALUE (which is the total quantity sold across all salespersons), using the DIVIDE function. This function handles cases where the divisor is zero, preventing division by zero.</p><pre>PctOfQty = VAR _FILTERED_VALUE = CALCULATE(<br>    SUM(FactTable[Quantity]<br>    ),<br>    ALL(<br>        DimSalesperson<br>    )<br>)<br>    VAR _MEASURE_VALUE = SUM(FactTable[Quantity]<br>    )<br>    RETURN<br>    DIVIDE(<br>    _MEASURE_VALUE,<br>    _FILTERED_VALUE<br>    )</pre><h4>Visualizations</h4><p>Based on the given dataset, I created visualizations that would convey meaningful insights to the internal teams or stakeholders. I also ensured that the dashboard was as interactive as possible so that one could filter and compare different products by name, group, or salesperson orders.</p><p>The horizontal bar chart below shows the revenue ranking by salesperson in descending order. This chart shows only seven salespeople selected.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*ApdT69G2kCFCxGA-57X11w.png" /></figure><p>Similarly, the bar chart below shows the top 7 product groups selected in descending order. This order indicates the associated revenue of each product group.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*3Yf8H9z4BYXhlDXkFPW_kw.png" /></figure><p>I achieved the particular ranking through the DAX code below:</p><pre>Ranking = <br>VAR _TOP_Product = RANKX(ALL(DimProduct[ProductName]), [TotalRevenue],,DESC)<br>VAR _BOTTOM_Product = RANKX(ALL(DimProduct[ProductName]), [TotalRevenue],,ASC)<br><br>VAR _TOP_Salesperson = RANKX(ALL(DimSalesperson[Salesperson]), [TotalRevenue],,DESC)<br>VAR _BOTTOM_Salesperson = RANKX(ALL(DimSalesperson[Salesperson]), [TotalRevenue],,ASC)<br><br>VAR _TOP_ProductGroup = RANKX(ALL(DimProduct[ProductGroup]), [TotalRevenue],,DESC)<br>VAR _BOTTOM_ProductGroup = RANKX(ALL(DimProduct[ProductGroup]), [TotalRevenue],,ASC)<br><br>VAR _CheckRank = <br>    IF(CONTAINSSTRING(SELECTEDVALUE(SelectToRank[SelectToRank Fields]), &quot;ProductName&quot;),<br>    IF(SELECTEDVALUE(ChooseRankType[Select]) = &quot;Top&quot;, _TOP_Product, _BOTTOM_Product),<br><br>    IF(CONTAINSSTRING(SELECTEDVALUE(SelectToRank[SelectToRank Fields]), &quot;ProductGroup&quot;),<br>    IF(SELECTEDVALUE(ChooseRankType[Select]) = &quot;Top&quot;, _TOP_ProductGroup, _BOTTOM_ProductGroup),<br><br>    IF(SELECTEDVALUE(ChooseRankType[Select]) = &quot;Top&quot;, _TOP_Salesperson, _BOTTOM_Salesperson)<br>    )<br>    )<br><br>RETURN<br>IF(_CheckRank&lt;=&#39;Choose Rank&#39;[Choose Rank Value], [TotalRevenue])</pre><p>In the above code,</p><p>1. _TOP_Product, _BOTTOM_Product: These variables calculate the rank of products based on their total revenue. `_TOP_Product` ranks products in descending order (highest revenue first), while `_BOTTOM_Product` ranks products in ascending order (lowest revenue first). It uses the `RANKX` function, which ranks each product based on its revenue compared to all products in the `DimProduct` table.</p><p>2. _TOP_Salesperson, _BOTTOM_Salesperson: Similar to the previous step, these variables calculate the rank of salespersons based on their total revenue. `_TOP_Salesperson` ranks salespersons in descending order of revenue, while `_BOTTOM_Salesperson` ranks them in ascending order.</p><p>3. _TOP_ProductGroup, _BOTTOM_ProductGroup: These variables calculate the rank of product groups based on their total revenue, with one variable for descending order and one for ascending order.</p><p>4. _CheckRank: This variable checks which entity is selected for ranking and whether it’s for top or bottom ranking. It uses nested `IF` statements:</p><p>It first checks if the selected entity is a product. If so, it further checks if the user selected the “Top” ranking. If yes, it returns the rank calculated for the top products; otherwise, it returns the rank calculated for the bottom products.</p><p>If the selected entity is a product group, it follows a similar logic. If none of the above conditions are met, it assumes the entity is a salesperson and returns the top or bottom rank based on the user’s selection.</p><p>RETURN IF(_CheckRank&lt;= ‘Choose Rank’ [Choose Rank Value], [TotalRevenue]): This is the return statement of the DAX measure. It checks if the rank calculated in _CheckRank is less than or equal to the value selected in the “Choose Rank” column. If this condition is met, it returns the total revenue; otherwise, it returns blank. This function effectively filters the rows based on the selected rank.</p><p>Additionally, I used the following DAX code to create the dynamic subtitle in the bar charts.</p><pre>SubTitle = <br>    VAR _RankType = SELECTEDVALUE(ChooseRankType[Select])<br>    VAR _ChooseToRank = SELECTEDVALUE(&#39;Choose Rank&#39;[Choose Rank])<br><br>    VAR _SelectedRank = <br>        IF(SELECTEDVALUE(SelectToRank[SelectToRank Order]) = 0, &quot;Product&quot;,<br>        IF(SELECTEDVALUE(SelectToRank[SelectToRank Order]) = 1, &quot;ProductGroup&quot;,<br>        “Salesperson”<br>        )<br>    )<br><br>    RETURN<br>        _RankType&amp;&quot; - &quot;&amp;_ChooseToRank&amp;&quot; &quot;&amp;_SelectedRank&amp;&quot; Selected&quot;</pre><p>Where:</p><p>1. VAR _RankType = SELECTEDVALUE(ChooseRankType[Select]): This variable retrieves the selected value from the `[Select]` column of the `ChooseRankType` table.</p><p>2. VAR _ChooseToRank = SELECTEDVALUE(‘Choose Rank’[Choose Rank]): This variable retrieves the selected value from the `[Choose Rank]` column of the `’Choose Rank’` table.</p><p>3. VAR _SelectedRank =: This variable checks the selected order for ranking from the `SelectToRank` table and assigns a corresponding entity type (“Product,” “ProductGroup,” or “Salesperson”) to `_SelectedRank.`</p><p>4. RETURN _RankType&amp;” — “&amp;_ChooseToRank&amp;” “&amp;_SelectedRank&amp;” Selected”: Finally, this `RETURN` statement concatenates various parts of the subtitle using the `&amp;` operator:</p><ul><li>`_RankType` represents the ranking type (e.g., “Top”).</li><li>`_ChooseToRank` represents the selected rank (e.g., “5”).</li><li>`_SelectedRank` represents the selected entity type (e.g., “Product”).</li><li>The final subtitle format is “Top — 5 Product Selected”.</li></ul><p>The filters below are associated with selecting and visualizing product names, product groups, and salespersons.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/354/1*RNR8G_8ew1AxOsQWADrO5A.png" /></figure><p>The corresponding DAX codes are as follows:</p><ol><li>Choose Rank</li></ol><p>The code generates a table (Choose Rank) with a single column with a sequence of integers from 1 to 15. The values increase by 1. These values would help one select the number of products or salespersons they want to see visualized.</p><pre>Choose Rank = GENERATESERIES(1, 15, 1)</pre><p>2. Select Rank</p><p>The ‘Select Rank’ table consists of two columns and two rows, as shown below. The table would help visualize the best and worst performing products or salespersons.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/241/1*4Olz8DxcfnIzzW8SNNgzvg.png" /></figure><p>3. Select to Rank</p><p>I first created a table with the following code.</p><pre>SelectToRank = {<br>    (&quot;ProductName&quot;, NAMEOF(&#39;DimProduct&#39;[ProductName]), 0),<br>    (&quot;ProductGroup&quot;, NAMEOF(&#39;DimProduct&#39;[ProductGroup]), 1),<br>    (&quot;Salesperson&quot;, NAMEOF(&#39;DimSalesperson&#39;[Salesperson]), 2)<br>}</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/707/1*SCYH-CSdtIxzfwZA_LNH1Q.png" /></figure><p>The line and clustered column chart below shows the total revenue (bars) and total budget (line) by year and month. It is important to note there were months when the total budget exceeded the total revenue. Therefore, I applied conditional formatting to compare the values between the revenue and budget. If the income exceeds the budget, it assigns a red color (to indicate underperformance). If the revenue equals or exceeds the budget, it assigns the navy blue color (to indicate meeting or exceeding performance).</p><pre>ConditionalFormatting1 = <br>    VAR _Revenue = [TotalRevenue]<br>    VAR _Budget = [TotalBudget]<br>    VAR _Result =<br>        SWITCH(<br>            TRUE(),<br>            _Revenue&lt;_Budget,&quot;#F12B30&quot;,<br>            “#033773”<br>        )<br>    RETURN<br>        _Result</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*M60ljZv6-70CFpzUhTwknA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*M1OKQ4MtBYlJxfn7SP8nMg.png" /></figure><p>Under the color categories, I chose the format style as the field value and the conditional formatting as what the field should be based on.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*n1x0azpcssQnJHgA4E8brg.png" /></figure><p>Below is a screenshot of the final dashboard.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*u6t9Y4l_7wkyoG9el3apxA.png" /></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a874a4ba2515" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Exploratory Data Analysis Using Python]]></title>
            <link>https://medium.com/@allanouko17/exploratory-data-analysis-using-python-75878f8b2598?source=rss-a86d3ccfa1f3------2</link>
            <guid isPermaLink="false">https://medium.com/p/75878f8b2598</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[seaborn]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[exploratory-data-analysis]]></category>
            <category><![CDATA[descriptive-statistics]]></category>
            <dc:creator><![CDATA[Allan Ouko]]></dc:creator>
            <pubDate>Thu, 11 Apr 2024 13:22:06 GMT</pubDate>
            <atom:updated>2024-04-11T13:22:06.431Z</atom:updated>
            <content:encoded><![CDATA[<h3>Understanding EDA</h3><p>When working with data, there are different steps that one needs to take to understand the data. Usually, one would take the initiative to understand the business questions to understand the objectives and stakeholder requirements. Besides, it is essential to understand the data sources and how they are collected to comprehend the given scenario better. Nevertheless, conducting Exploratory Data Analysis (EDA) on given data would help a data scientist summarize the data’s main characteristics. Using this approach, a data scientist could identify underlying patterns or discover anomalies in data.</p><h4>Executive Sales Analysis</h4><p>I recently completed a project analyzing sales using an open-source dataset. I utilized visualization techniques in this project to check the data’s characteristics and the sales team’s KPIs. The dataset comprised three Excel files with the necessary information.</p><p>Here is the <a href="https://github.com/AllanOuko/eda-python-sales-analysis/tree/main">link </a>to the GitHub repository.</p><h4>About the Data</h4><p>The first file included the sales data with the necessary information about the different orders, salesperson, order quantity, supervisor, sales channel, and manager information. When importing the file using Python, I removed the first three redundant rows and set the fourth row (now the first row) as the header. I also changed the various columns to the respective data types.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*UEnulBVcxBG6eUXsBRb08w.png" /><figcaption>Raw sales data</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2P1BP6q7A_pDUc8aOB-iJQ.png" /><figcaption>Processed sales data</figcaption></figure><p>The second file was the budget data with three 2019, 2020, and 2021 sheets. I processed this file by setting the first row as a header, dropping null columns, unpivoting the data, and then combining the sheets to form one data frame. The third was the product file with the information about the products sold within the given period. The budget and the product tables were linked with the sales data using the ‘SalespersonID’ and the ‘ProductID.’</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/358/1*LPJaREGQB42HO5xKZLShjA.png" /><figcaption>Sample budget table</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/457/1*UO82S_Cw_Ic5-WGukHwnBA.png" /><figcaption>Sample product table</figcaption></figure><h3>Exploratory Data Analysis</h3><p>The first EDA was to determine the organization’s sales and budget metrics. Therefore, I conducted a univariate analysis to get the total number of transactions as 260,096 and the total number of quantities sold as 6,381,172. The total revenue from the total sales was calculated as 17,909,232.</p><p>The second type of analysis involved multivariate EDA using graphs. I created graphs using seaborn and matplotlib libraries in Python in this analysis. The first graph compares the total revenue generated by the different sales channels. The output indicated that the retail channel had the highest revenue, followed by the distributor, while the online channel had the lowest revenue.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/871/1*Ry6-Yq9d7Dkl2UjYqke9fg.png" /><figcaption>Total revenue by channel</figcaption></figure><p>Additionally, I investigated the total revenue generated by each salesperson from the organization. While calculating, I sorted the total revenue by salesperson in descending order. The top three salespersons who brought in the highest revenue were Carla Ferreira, Julio Lima, and Gustavo Gomes.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*MpkpXo01FgH5zK4DsBhnlg.png" /><figcaption>Total revenue by salesperson</figcaption></figure><p>Similarly, I graphed the total revenue by the supervisor to determine the supervisor’s performance in the organization. The output ranked the different supervisors as shown below, with the top two supervisors with the highest revenue being Diego Araujo and Diogo Carvalho.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*JVPSHqupm_RStjHd8d8Mog.png" /><figcaption>Total revenue by supervisor</figcaption></figure><p>Additionally, it was essential to investigate the revenue associated with the two managers from the organization. A bar chart comparing the two managers showed Gabriel Azevedo had higher revenue than Victor Castro.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*gqOcy9Ve3smc_8h1MQlOsA.png" /><figcaption>Total revenue by manager</figcaption></figure><p>While processing the data, I realized the budget indicated the expected amount from each salesperson. Therefore, I created a column and line chart to compare the expected and actual amounts as the revenue for the different years. The output indicated fluctuations in the expected and actual income in various months throughout the year.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*tE9VQlgDXGzAsA3hHhKMVQ.png" /><figcaption>Monthly total revenue vs expected revenue for 2019</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zYok5e1q-Zrv-xiVhjhTbQ.png" /><figcaption>Monthly total revenue vs expected revenue for 2020</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*wrM5WFL5Qvzy8FCDq9n2LA.png" /><figcaption>Monthly total revenue vs expected revenue for 2021</figcaption></figure><p>Further analysis of the different products sold by the company indicated that hard food products were sold more than drinks.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/984/1*JxfAW5KgHz-es5en36xATg.png" /><figcaption>Top products by product category</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/984/1*3zB-1hSDG5ZClOqOJb3v4w.png" /><figcaption>Top products by product group</figcaption></figure><h3>Conclusion</h3><p>Exploratory data analysis is an important step in data analysis as it helps check the different patterns in data for various anomalies or inconsistencies. Besides, EDA could be helpful in analysis by answering some key questions, especially when tracking KPIs. Although I used bar graphs to visualize the categorical data from this data, there are other visualizations that one can still utilize to gain more insights. They include box plots, line graphs, histograms, and scatter plots. Nevertheless, the EDA should act as the first step in conducting further analyses, such as predictive or time series analyses.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=75878f8b2598" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Azure End-to-End Data Engineering Project — Part 3]]></title>
            <link>https://medium.com/@allanouko17/azure-end-to-end-data-engineering-project-part-3-09f4375ea921?source=rss-a86d3ccfa1f3------2</link>
            <guid isPermaLink="false">https://medium.com/p/09f4375ea921</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[azure-sql-database]]></category>
            <category><![CDATA[azure-data-pipeline]]></category>
            <category><![CDATA[power-bi]]></category>
            <dc:creator><![CDATA[Allan Ouko]]></dc:creator>
            <pubDate>Thu, 01 Feb 2024 11:33:49 GMT</pubDate>
            <atom:updated>2024-02-01T11:33:49.061Z</atom:updated>
            <content:encoded><![CDATA[<h3>Azure End-to-End Data Engineering Project — Part 3</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1000/1*ZrtopElVD1PUjFuQizGqMA.png" /></figure><h3><strong>Data Reporting using Power BI</strong></h3><p>In this last section of the project, I explain how I used Power BI to connect to the Azure Synapse SQL serverless database, fetched the views in the database, and created an interactive dashboard to report some of the KPIs for Kiva.</p><p>I used the ‘Get data’ option on the Power BI ribbon for this scenario and selected Azure. Under the Azure data source, I selected ‘Azure Synapse Analytics SQL’ and hit the connect button. This action prompts one to input the server name and database.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*rZ0pPLiNbabFC3_8C0AYdQ.png" /></figure><p>One can get the server’s name by navigating to the Azure Synapse Analytics workspace, under the properties tab, and copying the ‘Serverless SQL endpoint’ as the server name. I also maintained the import data connectivity mode to load the data/tables in Power BI.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*ssTWx8Rfd3ulM9yyEWurcw.png" /></figure><p>I used my Microsoft account to sign in and connect to the database.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*C9wjOKo4Nd4B-iJrFJBfJw.png" /></figure><p>This action connected to the database and fetched the different views in the Azure SQL database as tables. I noticed an error in importing the files due to a conflict between the date format in the tables and my settings in Power BI. Therefore, I disabled the ‘Time Intelligence’ option under the ‘Options’ menu for the current file.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*oj42jYe1MtUb48vT4LUW6g.png" /></figure><p>I performed some transformations on the tables using Power Query to ensure each column was of the right data type. I also converted the date column of the main table to the appropriate data type.</p><p>Since I was to use the dates to filter the data for some insights, I created the date table separately using Bravo, an external tool for Power BI. Bravo generates the dates automatically and creates the date table.</p><p>Refer to this video for using Bravo: (https://www.youtube.com/watch?v=WybnTHDl-AM&amp;t=326s).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*JEx68YJnBEQyTp678VXzXA.png" /></figure><p>The resulting data table is shown below. The other tables remained the same.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*wmBL5jlSG5wHkGUSTy1zwA.png" /></figure><p>Before analyzing and building the visuals, I checked the modeling tab to ensure the tables had proper relations. One must manually create the relationship between the new date table and the date column of the ‘kiva_loans’table.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*Dg-l3nXeMY1l1Rm_Lpq35Q.png" /></figure><p>After confirming the tables had the appropriate relationships, I created the dashboard below for reporting.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*hN7tB-R1PEyJ582yD2TBoQ.png" /></figure><p>According to this dashboard, I used three cards to display the important metrics. Using the unique ID from the ‘kiva_loans’ table, I got the count of the total number of loans disbursed by Kiva. Besides, the total loan and total funded amounts are $565.4 million and $527.6 million, respectively. I also included a bar chart to show the percentage of loans borrowed by sector. This chart shows the sector where the loan borrowers intended to use the loans. Additionally, I included a doughnut chart to show the gender distribution of the borrowers. To achieve this chart, applied conditional formatting to the column borrowers’ gender and categorized the ‘male’ as male, ‘female’ as ‘female’, and any other combination as a group. Therefore, the chart shows females were the most borrowers while people in group borrowers were the least.</p><p>I also included a clustered column chart to show the loan repayment habits of the borrowers based on gender. The chart indicates most of the borrowers’ monthly repayments. However, some borrowers made irregular repayments making it the second-most repayment routine. Still, the bullet and weekly repayment intervals were the least preferred repayment intervals by the borrowers. I also included a table to show the loan distribution by country. The table is in descending order and shows the top two countries, the Philippines and Kenya. The line chart shows the total loan amount vs the funded amount from January 2014 to July 2017. There is a significant trend and difference between these two metrics over the years and different months.</p><p>From the data table, I included the ‘Year’ and ‘Month’ slicers to help filter the dashboard according to specific periods. This approach would allow users to check the different metrics and compare the trends throughout the various months.</p><h3><strong>Pipeline Testing</strong></h3><p>I tested this pipeline by adding 10 random rows to the main table and triggering the pipeline to run. I also created a scheduled trigger to run once daily as I added ten rows of data in the original table for five days. After the successful run of each trigger, I refreshed the Power BI report, which changed to reflect the new data according to the number of loans added. This approach showed the data engineering pipeline worked as scheduled.</p><h3><strong>Conclusion</strong></h3><p>According to this project, I have showcased how I used Microsoft Azure to create an end-to-end data engineering pipeline. This pipeline used data from the on-premise SQL server database and loaded the data in Azure Data Lake Gen 2 storage. I performed the necessary transformations using Azure Databricks and loaded the data in Azure SQL Database. Using two levels of transformations, I ensured the data was clean and ready for different use cases. Thus, I extracted the data using Power BI and created a report for presentation to relevant stakeholders. This report represents a practical use case of Azure in data engineering in gathering data and making it ready and reliable for use.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=09f4375ea921" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Azure End-to-End Data Engineering Project — Part 2]]></title>
            <link>https://medium.com/@allanouko17/azure-end-to-end-data-engineering-project-part-2-a77bb296ab2b?source=rss-a86d3ccfa1f3------2</link>
            <guid isPermaLink="false">https://medium.com/p/a77bb296ab2b</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[pyspark]]></category>
            <category><![CDATA[azure-sql-database]]></category>
            <category><![CDATA[azure-databricks]]></category>
            <category><![CDATA[azure-synapse-analytics]]></category>
            <dc:creator><![CDATA[Allan Ouko]]></dc:creator>
            <pubDate>Mon, 29 Jan 2024 11:37:31 GMT</pubDate>
            <atom:updated>2024-01-29T11:55:06.795Z</atom:updated>
            <content:encoded><![CDATA[<h3>Azure End-to-End Data Engineering Project — Part 2</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1000/1*ZrtopElVD1PUjFuQizGqMA.png" /></figure><h3><strong>Data Transformation Using Azure Databricks</strong></h3><h4><strong>Storage Mount</strong></h4><p>After loading data from the SQL server to the bronze container, I performed two levels of transformations of the datasets into cleaner versions. However, I needed to create a compute cluster to allow different jobs to run in the notebooks. To create the compute cluster, launch the Azure Databricks studio, click on the compute tab, and configure the compute instance according to your needs. I selected a single user since I was the only one using the cluster in the resource and selected the other default options. It is also important to note that I enabled ‘credential passthrough for user-level data access’ to allow Azure Databricks to connect to Azure Data Lake Gen 2.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*a-PoeD_4mM2TEnf9hc2a6g.png" /></figure><p>In the Databricks workspace, create a notebook called ‘storagemount’ to mount the storage account in Azure Databricks. This option will allow accessing the files in the storage account.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*6FrSPwH0UWOA_ervjs-5ig.png" /></figure><p>To mount the storage using credential passthrough, there are codes from this webpage (<a href="https://learn.microsoft.com/en-us/azure/databricks/archive/credential-passthrough/adls-passthrough">https://learn.microsoft.com/en-us/azure/databricks/archive/credential-passthrough/adls-passthrough</a>), and made some changes according to my configuration.</p><pre>configs = {<br>  &quot;fs.azure.account.auth.type&quot;: &quot;CustomAccessToken&quot;,<br>  &quot;fs.azure.account.custom.token.provider.class&quot;: spark.conf.get(&quot;spark.databricks.passthrough.adls.gen2.tokenProviderClassName&quot;)<br>}<br><br># Optionally, you can add &lt;directory-name&gt; to the source URI of your mount point.<br>dbutils.fs.mount(<br>  source = &quot;abfss://bronze@kivaloansfolder.dfs.core.windows.net/&quot;,<br>  mount_point = &quot;/mnt/bronze&quot;,<br>  extra_configs = configs)</pre><p>For this code, update the container and storage account names to match what is on the resource group. Also, change the mount point name with that of the container.</p><p>Similarly, I mounted both the silver and gold containers. This option allowed accessing data from these different containers. Furthermore, I could access the data by giving the full path for the containers since I had allowed credential passthrough.</p><pre>configs = {<br>  &quot;fs.azure.account.auth.type&quot;: &quot;CustomAccessToken&quot;,<br>  &quot;fs.azure.account.custom.token.provider.class&quot;: spark.conf.get(&quot;spark.databricks.passthrough.adls.gen2.tokenProviderClassName&quot;)<br>}<br><br># Optionally, you can add &lt;directory-name&gt; to the source URI of your mount point.<br>dbutils.fs.mount(<br>  source = &quot;abfss://silver@kivaloansfolder.dfs.core.windows.net/&quot;,<br>  mount_point = &quot;/mnt/silver&quot;,<br>  extra_configs = configs)</pre><pre>configs = {<br>  &quot;fs.azure.account.auth.type&quot;: &quot;CustomAccessToken&quot;,<br>  &quot;fs.azure.account.custom.token.provider.class&quot;: spark.conf.get(&quot;spark.databricks.passthrough.adls.gen2.tokenProviderClassName&quot;)<br>}<br><br># Optionally, you can add &lt;directory-name&gt; to the source URI of your mount point.<br>dbutils.fs.mount(<br>  source = &quot;abfss://gold@kivaloansfolder.dfs.core.windows.net/&quot;,<br>  mount_point = &quot;/mnt/gold&quot;,<br>  extra_configs = configs)</pre><h3><strong>Level 1 Data Transformation</strong></h3><p>Since the data looked relatively clean in the database, I focused the first level of transformation on changing the date format from date-time to date type. At this level, I wanted the transformation to apply to all the tables and the columns of the date data type.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*H3ZegjjLiTgAR4OORRphEg.png" /></figure><p>First, I created another notebook named ‘bronze_to_silver’ for this first transformation. I created an array to list the table names in the bronze container. This code iterates through the bronze container to get the directory name and append the directory name to the table name array.</p><pre>table_name  = []<br>for i in dbutils.fs.ls(&#39;mnt/bronze/dbo/&#39;):<br>    table_name.append(i.name.split(&#39;/&#39;)[0])</pre><p>The code below generates the input path for all the tables iteratively and then loads as a data frame. The code declares the variable column, which gets the column names from the data frame as a list. I ensured that the file type was specified as CSV and the header was set as true to return the tables with header names. The next loop in the code checks if any column name has the name ‘date’ or ‘time’ and then converts it to the date format only. The last part of the code generates the output path in the silver container using the table name and writing the transformed data in the data lake. I specified the file type as CSV and used the overwrite option to rewrite any data in the silver container with the new version.</p><pre>from pyspark.sql.functions import from_utc_timestamp, date_format<br>from pyspark.sql.types import StructType, StructField, StringType, TimestampType<br><br>for i in table_name:<br>    input_path = &#39;/mnt/bronze/dbo/&#39; + i + &#39;/&#39; + i +&#39;.csv&#39;<br>    df = spark.read.format(&#39;csv&#39;).option(&#39;header&#39;, &#39;true&#39;).load(input_path)<br><br>    for col in df.columns:<br>        if &quot;date&quot; in col or &quot;time&quot; in col:<br>            df = df.withColumn(col, date_format(from_utc_timestamp(df[col].cast(TimestampType()), &quot;UTC&quot;), &quot;yyyy-MM-dd&quot;))<br><br>    output_path = &#39;/mnt/silver/dbo/&#39; + i + &#39;/&#39;<br>    df.write.format(&#39;csv&#39;).mode(&quot;overwrite&quot;).option(&#39;header&#39;, &#39;true&#39;).save(output_path)</pre><h3><strong>Level 2 Data Transformation</strong></h3><p>After performing the first level of transformation to the data, the data in the silver container appeared cleaner but required further transformation to be ready for different use cases. Thus, I performed another transformation from data in the silver container by changing the column names to lowercase. This transformation was to ensure there is a uniform naming convention for the data across the different tables. The transformed data was then stored in different folders in the gold container with respective table names.</p><p>I created another notebook and named it ‘silver_to_gold.’ Similarly, I created an array listing table names in the silver container. This code iterates through the silver container to get the directory name and append the directory name to the table name array.</p><pre>table_name  = []<br>for i in dbutils.fs.ls(&#39;mnt/silver/dbo/&#39;):<br>    table_name.append(i.name.split(&#39;/&#39;)[0])</pre><p>The code below generates the input path for all the tables iteratively and then loads as a data frame. The code declares the variable column_names, which gets the column names from the data frame as a list. I ensured that the file type was specified as CSV and the header was set as true to return the tables with header names. The next loop in the code iterates through the column names and changes to lowercase. The last part of the code generates the output path in the gold container using the table name and writing the transformed data in the data lake. I specified the file type as CSV and used the overwrite option to rewrite any data in the gold container with the new version.</p><pre>for name in table_name:<br>    input_path = &#39;/mnt/silver/dbo/&#39; + name  # Fix the input_path variable<br>    print(input_path)<br>    df = spark.read.format(&#39;csv&#39;).option(&#39;header&#39;, &#39;true&#39;).load(input_path)<br><br>    # Get the list of column names<br>    column_names = df.columns<br><br>    for old_col_name in column_names:<br>        # Change the column names to lowercase<br>        new_col_name = old_col_name.lower()<br><br>        # Change the column name using withColumnRenamed<br>        df = df.withColumnRenamed(old_col_name, new_col_name)<br><br>    output_path = &#39;/mnt/gold/dbo/&#39; + name + &#39;/&#39;<br>    df.write.format(&#39;csv&#39;).mode(&quot;overwrite&quot;).option(&#39;header&#39;, &#39;true&#39;).save(output_path)</pre><h4><strong>Data Transformation Pipeline</strong></h4><p>Since the two notebooks perform the necessary transformation for data, I needed to add them to the Azure Data Factory pipeline to transform the data when triggered with daily additional data. To achieve the connection between Azure Databricks and Azure Data Factory, I created a new linked service and used an access token to authenticate the linked service.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*TLN2IIxihmSk6NH1rfgM4A.png" /></figure><p>After creating the linked service, I selected the author tab and looked for the pipeline I had created earlier to add the two notebooks. I added the Azure Databricks notebook item from this tab to add the new activity for the bronze to the silver notebook. I specified the path of the notebook and connected the output of the ForEach activity to the bronze-to-silver notebook. This link ensured the notebook would only run if the ForEach activity were successful. Similarly, I added another notebook item for the silver-to-gold activity to run after the successful run of the bronze-to-silver notebook.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*v36chLNBtn0hH64y2F9phA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*whqingAb2WoOIdlwniAWWg.png" /></figure><p>I published all the changes and used the debug option to run the pipeline. Similarly, I added a new trigger to run the pipeline to confirm if it properly worked, where I confirmed each activity ran successfully from data ingestion to processing.</p><h3><strong>Data Loading Using Azure Synapse</strong></h3><p>With the transformed data in the gold container, I needed to load the data in the Azure SQL database for access and reporting. Therefore, I created a serverless database to store the data and named the database ‘gold_db.’ The Azure Synapse Analytics workspace is linked to the Azure Data Lake Gen 2; hence, one can find data from the different containers in the linked datasets.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*aUqNfRNCk1_C6WB7r4SFow.png" /></figure><p>Since there were different tables in the gold container, I needed to create views for the Azure SQL database. Therefore, I created a stored procedure using a new SQL script to create views of the different tables.</p><pre>USE gold_db<br>GO<br><br>CREATE OR ALTER PROC CreateSQLServerlessView_gold @Viewname nvarchar(100)<br>AS<br>BEGIN<br><br>DECLARE @statement VARCHAR(MAX)<br><br> SET @statement = N&#39;CREATE OR ALTER VIEW &#39; + @Viewname + &#39; AS<br>  SELECT *<br>  FROM<br>   OPENROWSET(<br>   BULK &#39;&#39;https://kivaloansstg.dfs.core.windows.net/gold/dbo/&#39; + @Viewname + &#39;/&#39;&#39;,<br>   FORMAT = &#39;&#39;CSV&#39;&#39;<br>  ) AS [result]<br>  &#39;<br><br>EXEC (@statement)<br><br>END<br>GO</pre><p>I also needed to create a pipeline to use the stored procedure (SQL query) to create views in the SQL serverless database. Under the manage tab, I made a new linked service for the ‘Azure SQL Database’ to access the serverless SQL database. Note the integration runtime is automatically selected as “AutoResolveIntegrationRuntime’ since it is a cloud-based resource. Configure the account selection manually and use the ‘System Assigned Managed Identity’ authentication type.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*LUa2T1PCa9vlSAf1ZjLJYw.png" /></figure><p>After creating the linked service, I made a new pipeline and added the ‘Get Metadata’ item to get the table names from the gold container. Similarly, I added a ‘ForEach’ item to get each item from the metadata and selected the stored procedure name from the existing list.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*uNK6Gz6TKY1ZqwO8x0NAWw.png" /></figure><p>After that, I published all the changes and ran the pipeline using the debug option. This option added the different views from the tables in the gold container to the Azure SQL database.</p><p>In the next section, I will show how I connected the data to Power BI and created an interactive dashboard.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a77bb296ab2b" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Azure End-to-End Data Engineering Project — Part 1]]></title>
            <link>https://medium.com/@allanouko17/azure-end-to-end-data-engineering-project-part-1-d9067ba962b0?source=rss-a86d3ccfa1f3------2</link>
            <guid isPermaLink="false">https://medium.com/p/d9067ba962b0</guid>
            <category><![CDATA[azure]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[azure-data-factory]]></category>
            <category><![CDATA[sql-server]]></category>
            <dc:creator><![CDATA[Allan Ouko]]></dc:creator>
            <pubDate>Thu, 25 Jan 2024 12:13:11 GMT</pubDate>
            <atom:updated>2024-01-29T11:38:02.534Z</atom:updated>
            <content:encoded><![CDATA[<h3>Azure End-to-End Data Engineering Project — Part 1</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1000/1*ZrtopElVD1PUjFuQizGqMA.png" /></figure><h3><strong>What is Data Engineering?</strong></h3><p>Data engineering refers to designing and building systems that collect, process, store, and analyze data at different scales. This data usually comes from different sources at different times, hence the need to ensure proper cleaning and transformation before making it available to data scientists and analysts. The common task for data engineers involves creating data pipelines to allow streaming data for different use cases. The standard data pipeline phases include:</p><p><strong>1. Ingestion: </strong>This phase involves gathering data from various sources.</p><p><strong>2. Processing: </strong>This stage involves processing the data to desired data sets for different use cases.</p><p><strong>3. Storing:<em> </em></strong>The processed data is then stored for easy retrieval from the database.</p><p><strong>4. Access: </strong>The stored data is accessible to users for different use cases.</p><h3><strong>Azure End-to-End Data Engineering</strong></h3><p>I will use Microsoft Azure to demonstrate a real-world application of data engineering. I will also use an open-source dataset from the Kaggle competition “Data Science for Good: Kiva Crowdfunding” (<a href="https://www.kaggle.com/datasets/kiva/data-science-for-good-kiva-crowdfunding">https://www.kaggle.com/datasets/kiva/data-science-for-good-kiva-crowdfunding</a>). This data is in CSV format; hence, you need to load the data into the database for storage. I assumed I would be receiving data daily to add to the different tables in the database. I used the SQL server database as the data source for the project.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*-UWVqbyKxv140mJ15OWX3A.png" /></figure><p>Kiva is an online crowdfunding organization that offers loan services to underserved and unbanked populations worldwide. Some of the use cases of these loans include starting a business, paying school fees, and investing in farming. The datasets available at Kiva show the loans issued to borrowers between January 2014 and July 2017. This information will be valuable in defining some Key Performance Indicators (KPIs) to evaluate the organization’s performance over time.</p><h4><strong>The Data Architecture</strong></h4><p>I used data stored on our On-prem SQL server database. The project aimed to create a data pipeline that migrated the data into the cloud from the SQL server. Therefore, I would trigger this pipeline to run daily when new data is added to the database.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/850/1*FdYD_UomMTmpNPHrY5hCVg.jpeg" /></figure><p><strong>1. On-prem SQL Server Database:<em> </em></strong>I used data stored in our local SQL database in the project.</p><p><strong>2. Azure Data Factory: </strong>ADF is the ETL tool used for data ingestion. I connected the ADF to the SQL server to copy the required tables to the cloud.</p><p><strong>3. Azure Data Lake Gen 2: </strong>This is where all the data are stored.</p><p><strong>4. Azure Databricks: </strong>Azure Databricks transformed the data into the required format.</p><p><strong>5. Azure Synapse Analytics: </strong>This tool will load the Azure SQL database data.</p><p><strong>6. Power BI:</strong> To load data from Azure Synapse Analytics and Create reports for business use cases.</p><p><strong>7. Azure Active Directory and Azure Key Vault: </strong>To enhance security access to the different data in the cloud.</p><h3><strong>Environment Set-up</strong></h3><p>One requires a Microsoft Azure account for this project. Microsoft offers beginners a free 30-day trial period to access the Azure portal. Inside the portal, you need to create a resource group with a unique name. Furthermore, you need to create different resources in the resource group to enable the execution of the different data pipeline runs.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*raK0csAAGPFfE0Gqc8Ye8A.png" /></figure><h4><strong>Data Source</strong></h4><p>In this project, I created a username and password to allow access to the Azure database. Besides, I stored the username and password in the Azure Key vault for secure login to access the data.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*fK_kFSYC4psaBiIOP_4zRw.png" /></figure><p>I also granted the user access to the database to read all the tables with the prefix ‘dbo’ since I will read the tables with the ‘dbo’ schema to Azure. To check the user permissions, expand the security panel on the left side under the particular database, expand the user, and refresh to show the new user created. Right-click on the user name, under the properties, grant the ‘db_datareader’ for the database role membership.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*EwFOkpIb0_kgHVG4-7-3bQ.png" /></figure><h3><strong>Data Ingestion Using Azure Data Factory</strong></h3><h4><strong>Creating a Connection Between SQL Server and Azure Data Factory</strong></h4><p>Since there was no relationship between Azure Data Factory and the SQL server database, I needed to install a Self-Hosted Integration Runtime to create the link. Under the manage tab in the ADF, select integration runtime. Under this option, there will be an AutoResolveIntegrationRuntime already running. This AutoResolveIntegrationRuntime is usually used to connect to cloud-based resources. However, I needed to create an integration runtime to connect to the on-prem SQL database.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*uEC1-D9eSn0ZTO3O8JSGMg.png" /></figure><p>To create the SHIR, select the new button and select Azure, Self-Hosted setup, give a descriptive name, and create the runtime. For this project, I used the express setup option since I was installing it on my machine only. However, one can use the manual setup option when installing the SHIR on multiple machines for the same project.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*S7DcJDWymAmxpxAStAaMHw.png" /></figure><p>On successful installation, run the newly installed Microsoft Integration Runtime Configuration Manager to confirm if the connection is established. A successful installation should show the connection as:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*YqEYrJitbYsmWt0sHdzCTg.png" /></figure><h4><strong>Data Ingestion</strong></h4><p>For this project, I was interested in creating a pipeline that would read all the tables in the SQL server and load them in Azure Data Lake Gen 2. I created three storage containers, (gold, silver, and bronze) where the bronze container will store the raw data as it was from the source. The silver container would contain data from level 1 transformation from the bronze container while the gold container would contain the cleaned and transformed data from the silver container. To create a pipeline to read all the tables in the database, I used the lookup activity to look up all the tables from the SQL database. In this lookup activity, I use a query to list the tables to ingest in Azure Data Lake Gen 2.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*qpPnLMjqdVlyHrUwdhYqxw.png" /></figure><p>This query would list the tables available in the database with the given schema name.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*x9QnflU3FBMVE6l8CcrTyg.png" /></figure><p>Thereafter, I created a ForEach item in the pipeline to iterate from the Lookup activity to copy all the tables. The ForEach item would loop through all the tables and copy the items listed from the lookup table.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*4UOf0zwm-9GziPHRxyUNLQ.png" /></figure><p>Under the ForEach item, I created the Copy data item to copy data from the source. I configured the source of the data and the sink to save the data as CSV files. I also specified the path of the datasets so that each copied table would be stored separately in a folder and a path with the file name. Furthermore, the data ingestion pipeline was used to read and store the data from the SQL server to the bronze container, where the data was in the raw format.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*FtC1zY9V0JSoP6swInI-xQ.png" /></figure><p>After the successful configuration of the pipeline, I ran using the debug option to test whether it was working without any errors. Besides, I used the add trigger option to run the pipeline and tested with the current time. Since the pipeline was meant to fetch the tables from the on-prem SQL server every time it runs, every successful run would overwrite the existing folder in the bronze container. At the end of the data ingestion, the data was now stored in the bronze container ready for transformation.</p><p>In the next section, I will explain how I transformed the data using Azure Databricks and loaded the data in Azure SQL database.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d9067ba962b0" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>