<?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 Bharath Natarajan on Medium]]></title>
        <description><![CDATA[Stories by Bharath Natarajan on Medium]]></description>
        <link>https://medium.com/@bnatarajan?source=rss-d930152461ff------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/2*wszZYBAbqLbarazUG4WvWw.jpeg</url>
            <title>Stories by Bharath Natarajan on Medium</title>
            <link>https://medium.com/@bnatarajan?source=rss-d930152461ff------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 08:53:02 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@bnatarajan/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[Transitioning Beyond BI: Catapult Your Data Team and Business Outcomes with Machine Learning]]></title>
            <link>https://bnatarajan.medium.com/transitioning-beyond-bi-catapult-your-data-team-and-business-outcomes-with-machine-learning-7b0476251dbb?source=rss-d930152461ff------2</link>
            <guid isPermaLink="false">https://medium.com/p/7b0476251dbb</guid>
            <category><![CDATA[business-intelligence]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[machine-learning]]></category>
            <category><![CDATA[ai]]></category>
            <dc:creator><![CDATA[Bharath Natarajan]]></dc:creator>
            <pubDate>Wed, 31 Jan 2024 23:10:40 GMT</pubDate>
            <atom:updated>2024-01-31T23:10:40.695Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*WE8rv8jo6cSbyJ3wj84AhQ.jpeg" /><figcaption>Photo by Vlada Karpovich on Pexel</figcaption></figure><p>Advanced Business Intelligence (BI) teams excel in constructing robust data pipelines, data modeling, and empowering data analysts with self-service capabilities. As the modern data stack becomes commonplace, these capabilities are table stakes for data teams. The question emerges: What’s next?</p><p>While Machine Learning (ML) has been available for some time, many BI teams have yet to embrace it. Integrating ML not only enhances BI teams’ skill sets but also positions them as proactive agents of change, driving tangible business outcomes.</p><p><strong>So how can you get started with ML:</strong></p><p>The challenge lies in initiating ML adoption within your business teams, especially when business leaders may be unfamiliar with ML’s potential. To bridge this gap, focus on selecting specific use cases to showcase ML’s impact on business improvements.</p><p><strong>Here are a few examples:</strong></p><p>Predict the probability of opportunity closure using Salesforce funnel data.</p><p>Predict orders by leveraging historical order trends and funnel data.</p><p>Predict shipment volumes or time to deliver for manufacturing companies.</p><p>You can come up with many such examples for your company based on your industry like churn potential, propensity to buy, inventory predictions, fraud detection etc.</p><p><strong>How do you go about building your first model?</strong></p><p>Here are some high-level steps on how to build your first set of models -</p><p><strong>Understand the Data and Define Predictions:</strong></p><p>Thoroughly understand the data and define predictions in collaboration with business stakeholders to align with strategic goals.</p><p><strong>Prepare the Dataset:</strong></p><p>Cleanse, preprocess, and transform the dataset to enhance its quality and relevance for model training.</p><p><strong>Choose the Right Model:</strong></p><p>Select ML models based on the nature of the problem, such as decision trees, random forests, or gradient boosting.</p><p><strong>Train and Test the Data:</strong></p><p>Split the dataset, train the model, and evaluate its performance using testing sets. Adjust hyperparameters iteratively.</p><p><strong>Predict the Future:</strong></p><p>Apply the well-trained model to new data, evaluating accuracy and making refinements for reliable predictions.</p><p><strong>Store the Predictions:</strong></p><p>Integrate the ML model into the data infrastructure and store predictions in a database for accessibility and future analysis.</p><p><strong>Schedule to Run After Data Refreshes:</strong></p><p>Automate the ML model to run regularly after data refreshes, ensuring continuous relevance and accuracy.</p><p><strong>Visualize the Data and Predictions:</strong></p><p>Create meaningful visualizations to help both data teams and business users understand complex patterns in the dataset and predictions. Help the business users and executives to use the predictions in the form of the data visualizations in their daily processes and decision making.</p><p><strong>Snapshot the Dataset and Predictions:</strong></p><p>Capture a comprehensive snapshot of the dataset and predictions for showcasing how the prediction is trending over time. Users will ask what the prediction at the beginning of the quarter was. You cannot answer this without capturing daily snapshots.</p><p><strong>Socialize your model with Business Users:</strong></p><p>Demonstrate the benefits of utilizing ML by showcasing how its predictions can guide decision-making. This includes identifying optimal projects to work on, prioritizing the right customers to focus on, and streamlining resource allocation for maximum impact. Illustrate how ML-driven insights enhance precision in decision processes.</p><p><strong>Iterate to Tune the Model:</strong></p><p>Gather feedback from business users and iteratively fine-tune the model based on their insights, fostering continuous improvement and collaboration.</p><p><strong>What tools can you use to get started?</strong></p><p>Keep it very simple to get started.</p><p>Python and Jupyter Notebooks: Use these tools for data exploration, analysis, and initial model prototyping.</p><p>Matplotlib and Seaborn: Visualization libraries for creating insightful charts and graphs.</p><p>Scikit-learn: Offers tools for data preprocessing, such as handling missing values and feature scaling.</p><p>If you are a Snowflake customer once the model is built the deployment and scheduling is easy using Snowpark Python and ML features. You can store the model as a stored procedure and schedule it as a task within Snowflake and can trigger it after the data refreshes. I will write more about this in a later post.</p><p><strong>Summary</strong></p><p>In summary, as the next evolution for your data and BI team, harness the power of ML to provide your business users with actionable insights, aiding them in making data driven decisions. Moving into Machine Learning provides the opportunity for BI teams to build stronger relationships with your executives and stakeholders. Don’t wait for them to ask; take the first step, by proactively building models and showcasing your team’s capabilities to help drive your company’s business strategy.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=7b0476251dbb" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to organize your data team for success]]></title>
            <link>https://bnatarajan.medium.com/how-to-organize-your-data-team-for-success-ca8983729c20?source=rss-d930152461ff------2</link>
            <guid isPermaLink="false">https://medium.com/p/ca8983729c20</guid>
            <category><![CDATA[modern-data-stack]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[organizational-change]]></category>
            <category><![CDATA[business-intelligence]]></category>
            <dc:creator><![CDATA[Bharath Natarajan]]></dc:creator>
            <pubDate>Mon, 01 Aug 2022 05:22:25 GMT</pubDate>
            <atom:updated>2022-08-01T20:10:34.069Z</atom:updated>
            <content:encoded><![CDATA[<h3><strong>How to setup your data organization for success in the era of the full stack data engineers</strong></h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*fvyiHllGLTDTEHhHUUvzew.jpeg" /><figcaption>Photo by <a href="https://www.pexels.com/@kampus/">Kampus Prodution</a> on <a href="https://www.pexels.com/photo/woman-in-white-suit-discussing-stock-market-data-to-her-colleagues-8353802/">Pexels</a></figcaption></figure><p>I am sure today’s data leaders will agree — Running a data team in a large organization or even a startup is not for the faint of heart (I am only half kidding). Challenges are starting to appear from every direction. Here are a few –</p><p>1. Talent Shortage</p><p>2. Exploding number of data sources and data volume</p><p>3. Increasing cloud cost</p><p>4. Complexity of the vendor landscape</p><p>5. Internal Organization Complexities — Shadow BI teams creating alternate architectures</p><p>6. Rising user expectations and demands</p><p>7. Running reliable data operations across multiple tools while adding new capabilities all the time</p><p>That was just a short list, I can keep going on. One of things you must get right to mitigate these challenges is to setup your organization for success.</p><p>Traditional data and BI teams were organized were around 3 distinct skill sets –</p><p>1. ETL Team</p><p>2. Business Intelligence Team</p><p>3. Business Analyst/Project Manager</p><p>Traditional ETL products like Informatica are very difficult to pick up and will take many years to get good at. This very specialized skill was highly valued and there usually was a team lead or manager taking care of the funnel for this team and support for production loads.</p><p>The BI team specialized on building the data model and the dashboards in any legacy BI tool like OBIEE, Business Objects, MicroStrategy, Cognos among others. Then there is a third group of team members who specialize on understanding the source system data and help with translating the requirements from the business functions into ETL and BI designs. This role is optional and mostly present in large organizations.</p><p><strong>Traditional data organization worked for a long time</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4adfUKMailuOJQfTOes49A.png" /><figcaption>Fig. 1: Traditional Data Team Organization</figcaption></figure><p>This traditional data organization worked for almost 25 years from early 1990s till about 2015. It worked because number of source system in companies were limited — One or two ERPs and CRM systems with a few satellite applications. Business users were well trained to expect new requirements to take anywhere from 3–6 months for small changes and 9 months to 2 years for large new projects. From a design perspective star schema was the way to go and the data warehouse was designed for performance and storage optimization perspective on Oracle, Teradata and SQL Server.</p><p>With the traditional approach the project funnel was negotiated with the Director/Senior Manager of the data team and then business BI leads write a detailed functional requirement followed by a large project team building, testing and rolling out the solution over several months to years.</p><p><strong>Rise of shadow BI teams</strong></p><p>One of the fallouts of this approach was a huge development of shadow BI teams in the businesses who built their own solutions with data from the traditional BI tools as they could not wait for these large development turn arounds. This was primarily happening in the mid-2010s as the traditional data organization was not working any more to satisfy the increasing speed of the business data demands.</p><p><strong>Emergence of the modern data stack and rise of the full stack data engineer</strong></p><p>The modern data stack built around the cloud data warehouse like Snowflake, Redshift or Google Big Query started emerging as a viable option around 2018. Many easy-to-use tools for loading, transforming and visualizing data from these cloud data warehouses started to become mainstream. Think Fivetran, Matillion, Airbyte for loading, dbt, Snowflake Tasks etc. for transformation and Tableau, Power BI, Looker for visualization.</p><p>What this stack enables is that one developer can now fully build end to end solutions. Granted the developer has to be very talented but they can start with one strength like ELT/ETL, but they can certainly pick up the other skills like visualization etc. It is not the same as before with the hugely complex tools like Informatica, Cognos/MicroStrategy/OBIEE etc., and Oracle/Teradata databases where you need years of experience to be good at. What is essential to get started is very good knowledge of SQL and to some extent Python. This is the era of the full stack data engineers.</p><p><strong>The modern data organization</strong></p><p>What the rise of the full stack data engineer enables is that the data team can now be organized around business needs rather than around technical skills like ETL or BI tools. For example, if the company is large enough there will be data requirements from multiple functions like finance, supply chain/manufacturing, HR, sales, marketing, service, product, and engineering. There will be even sub-teams within each group like for example finance might have Tax, AP, AR, Revenue, Controllership, Audit/Compliance sub-teams having different data requirements. In addition, while data tools started becoming easier to use, the number of source systems, enterprise architecture and business data needs for each of these functions have increased in complexity. Hence the traditional organization needs to be reworked.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*TFNBTwKYBk1k7Gaw9ijYvA.png" /><figcaption><strong>Fig 2: The modern data organization</strong></figcaption></figure><p>The modern full stack data engineers need to be good with the data tools, but they also need to be more of functional experts than they were ever before. They need to be embedded within the business teams and have a seat at the table providing their input on how to solve a given business problem from data perspective for product, engineering, sales, or other functions. This is how companies who are leading edge in data expertise like Facebook organize their data teams.</p><p>This is a totally different method of engagement from the past where after all the business decisions which affect data are made and a requirements document comes to the data team which they take and deliver in 6–9 months by which time the business would have already changed. With the modern organization approach, the data engineer can learn more about the source systems whether it is payments, product usage, ERP, CRM at a deeper level, understand the business processes, understand the KPIs which drive the business. The modern full stack engineering team will be able to deliver continuous improvements and value to the functions they work with rather than wait to be told what to build.</p><p>In conclusion, Companies who want to make data driven decisions need to empower their data engineering organization by giving them a seat at the table during the entire decision making process. With easier to use and powerful data tools and the rise of the full stack data engineer, it is critical to setup the data organization correctly for success.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ca8983729c20" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to set up the team structure and governance model for a successful RPA journey]]></title>
            <link>https://bnatarajan.medium.com/how-to-set-up-the-team-structure-and-governance-model-for-a-successful-rpa-journey-153f29054dc9?source=rss-d930152461ff------2</link>
            <guid isPermaLink="false">https://medium.com/p/153f29054dc9</guid>
            <category><![CDATA[automation]]></category>
            <category><![CDATA[intelligent-automation]]></category>
            <category><![CDATA[uipath]]></category>
            <category><![CDATA[rap]]></category>
            <category><![CDATA[roboticprocessautomation]]></category>
            <dc:creator><![CDATA[Bharath Natarajan]]></dc:creator>
            <pubDate>Sat, 02 Jan 2021 03:58:25 GMT</pubDate>
            <atom:updated>2021-01-03T00:01:46.413Z</atom:updated>
            <content:encoded><![CDATA[<h3><strong>How to set up the right team structure and governance model for a successful RPA journey</strong></h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*SLsxk2-5VuftLfQc2e2E5w.jpeg" /></figure><p>Automation driven by RPA technology can enable operations teams in all departments within a company to scale by automating repetitive manual tasks. RPA is best suited for automating use cases where a human is performing same or similar tasks repetitively. ML/AI will be a key part of enhancing RPA capabilities by reading and understanding documents like purchase orders, invoices etc., chatbots, NLP and many other ways that we have not thought of yet.</p><p>While getting started on your RPA journey, one of the key factors for the success of the RPA program is how to structure the team in your company. You should be thinking about this while you are finishing up the POCs and selecting the right RPA tool for your company.</p><p>There are two ways to organize the RPA team structure -</p><p>1. <strong>Central COE Model</strong> — Centralize all RPA activities into one single central team</p><p>2. <strong>Federated COE Model</strong> — Certain activities like BOT funnel management, BOT development, and Support lie in the business function while the IT COE manages the overall infrastructure, development methodologies and governance across all functions.</p><p>In the Central COE model, a central RPA team in IT will handle RPA process intake, process design, development and on-going support of all processes. The advantage of this model is that it is easier to establish a common intake model and a standardized way of designing and building the bots. The big downside of this approach is the centralized intake and development process will create a bottleneck and queue for the centralized team. This will lead to a lot of unhappy departments whose bots could not be prioritized and built. It might lead to dissatisfaction towards the centralized RPA initiative and each department might eventually start their own RPA program leading to inefficiencies and risk to the systems.</p><p>In the Federated COE model, each function/department will handle the bot intake process, design, development and first level support of the bots. In this model, the IT COE will handle the central server infrastructure, relationship with the RPA vendor, licensing model, and common coding, governance and security standards. The IT COE will reside in IT and the Federated COE will reside in the various functions like finance, sales, HR, supply chain etc. The advantage of this model is the functions will be able to work on their own bots at their own pace and cost and will realize the benefits without depending on a central team to deliver their bots. The business functions understand their business processes best and they will be best equipped to build and maintain their own bots and change the bots if the business processes change. The disadvantage is that each function must build their own technical capabilities to build and maintain the bots in their own functions. This model will work well if there is tight collaboration between the IT COE and Federated COE teams and there are proper development, code review by the IT COE, security and governance standards established across the functions by the IT COE. In addition, there needs to be a committed steering committee across functions which meet regularly to manage and guide the RPA journey across the company.</p><p>For most medium to large enterprises the Federated COE model is the best way to structure the RPA team for success. This way the individual functions can move as aggressively as needed for their business and also the knowledge and responsibility get distributed across the company. If the business functions have skin in the game, they will put the required resourcing for the program to succeed over the long term. The Central COE model will work for a smaller company where the various functions do not have the resources to commit to create their own Federated COE team. Another way to go about this is even in a larger company the RPA program can begin with the Central COE model and then after a handful of successful bots in production they can move to the Federated COE model.</p><p>The below table (Figure 1) is a way to distribute the roles and responsibilities of the various teams involved in a Federated COE model. It might be slightly different setup in each company but this way of distributing work will keep all the stakeholders engaged and aligned towards the success of the RPA initiatives.</p><p><strong>Process Owner</strong> — This is the function within a department like finance responsible for executing a process like AP Invoice Processing or PO Creation etc.</p><p><strong>Federated COE</strong> — This is a technical team embedded within the department to help build and support RPA processes</p><p><strong>IT COE</strong> — This team is best embedded within the corporate IT team to help with vendor relationship, license management, infrastructure and common development and governance standards definitions.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/714/1*eJTTECJqLenWnH1T2ZouPQ.png" /><figcaption><strong>Figure 1: Federated COE Model — Roles &amp; Responsibilities</strong></figcaption></figure><p><strong>RPA Governance Model</strong></p><p>Once the Federated COE team structure is established, the next focus is to establish the RPA governance framework. This should include -</p><p>1. Establishing of a bot intake process</p><p>2. Development process</p><p>3. Support and maintenance processes including Business Continuity Plan (BCP)</p><p><strong>1.</strong> <strong>Bot Intake Process</strong></p><p>It is very important to ensure your company is working on the right processes to automate.<strong> </strong>In the federated COE model, the individual business functions decide which processes are best automated and the order of development. The below diagram (figure 2) shows how you can setup the intake process in your company.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/624/1*KnWwizjWX7jNYzQCeQn1hw.png" /><figcaption><strong>Figure 2: RPA Governance Model — Bot Intake Process</strong></figcaption></figure><p><strong>2.</strong> <strong>Bot Development Process</strong></p><p>Once the decision to build a bot is taken, the next step is to use the documentation provided by the process owner to start building the bot. The below diagram (Figure 3) shows the roles and the process steps for bot development. In this methodology the process owner owns the testing and running the bot (if in attended mode), the federated COE developers build the bot and the IT COE reviews the code and provides the devops support for the bot. Once the bot moves to production, the support model and Business Continuity Plan (BCP) for the bot needs to be in place.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/623/1*ExZD2oESERh9Da3w6dQ8ug.png" /><figcaption><strong>Figure 3: RPA Governance Model — Bot Development Process</strong></figcaption></figure><p><strong>3.</strong> <strong>Support Process</strong></p><p>The support process for the bots and identifying who is responsible for what is very critical to document even before the bot development is started. If the RPA bot is performing a business-critical process, then it’s very important that the support process is clearly understood and there is staffing to execute the model. The below diagram (Fig. 4) shows a way to organize your support process. This can be modified to fit your company’s support process.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/624/1*k2QWD6sd8I8GN6jjTjRuvg.png" /><figcaption><strong>Figure 4: RPA Governance Model — Support Process</strong></figcaption></figure><p><strong>Conclusion</strong></p><p>RPA is not a project; it is a journey. It is very important to select the right RPA product for your company and equally important to create a well thought out team structure and governance model to ensure success of your RPA program. You can decide whether a Central COE or Federated COE model works best for your company and then use the intake, development and support model process flows shown in this article as a starting point for establishing the governance model which works best for your company.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=153f29054dc9" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to create a BI Modernization Strategy]]></title>
            <link>https://medium.com/series/how-to-create-a-bi-modernization-strategy-d1cbd1210a30?source=rss-d930152461ff------2</link>
            <guid isPermaLink="false">https://medium.com/p/d1cbd1210a30</guid>
            <dc:creator><![CDATA[Bharath Natarajan]]></dc:creator>
            <pubDate>Mon, 07 Sep 2020 06:02:55 GMT</pubDate>
            <atom:updated>2020-09-07T06:44:06.162Z</atom:updated>
            <content:encoded><![CDATA[<h4>Part 1: How grouping your existing BI use cases by data latency requirements can help clarify your BI Strategy</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/700/1*JcpAM7HVHokFcK_rusT_Gg.jpeg" /></figure><p>A lot of companies are burdened by legacy BI platforms and are not able to deliver the right data to their users at the right time to run their business today. They know that they need to modernize their BI platforms but do not know how to go about it. First let’s look at what should be the goals of a BI Modernization strategy.</p><p>Here are the goals from my view for any BI Modernization Strategy -</p><p>1. <strong>Speed of change</strong> — Probably the most important goal. The new platforms should be able to add new fields or add new data sources much faster than legacy platforms.</p><p>2. <strong>Combine data from multiple sources</strong> — This one was always difficult to achieve in legacy tools but is possible with modern tools.</p><p>3. <strong>Superior performance</strong> — Report performance and data load timings should be significantly improved.</p><p><strong>4.</strong> <strong>Self Service — </strong>Enable business users and power users to build their own reports and dashboards without relying on IT and super users</p><p>5. <strong>Support different data latency requirements</strong> — Real Time, Operational and Analytical Reporting are the three ways to look at data latency requirements</p><p><strong>6.</strong> <strong>Reduce overall cost — </strong>The<strong> </strong>BI software licensing models and the<strong> </strong>costs of compute and storage have come down enormously leading to a significant reduction in costs if implemented correctly compared to legacy BI</p><p><strong>Grouping BI Use Models by Data Latency</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*-te_4Rji8gXreF6r.jpeg" /></figure><p>How classifying your existing BI use models can help clarify the BI Modernization strategy</p><p>In most companies BI would have evolved in a somewhat organic manner over many years or decades. Many different BI tools would have been used at different times either brought in by acquisitions or by the best tool available at that time. When companies try to replace their legacy tools, they often try to replace one tool with another tool. For example, their project would like “we should move from OBIEE or Cognos to Tableau or Power BI” and/or “let’s move our data warehouse to Snowflake”. They then end up customizing or building solutions into Tableau/Power BI which it was not meant to do, and the project runs into issues.</p><p>A better strategy would be to consider the use models of your current BI solution. One way to look at your current use model is by data latency requirements.</p><p>There are three ways to classify use models by data latency –</p><p><strong>1.</strong> <strong>Real-Time Data</strong></p><p>When the users want to see the data as it is “right now” in the operational system then that is a real-time requirement. This kind of data needs are common in the supply chain, finance, sales or call center area. The supply chain users would want to see what are the orders they need to ship right now, or in finance they would want to see what is the GL Balance at 12 AM at the end of the quarter, or in sales they want to see quota to orders at frequent intervals at the end of a quarter or in call center they would like to see call volume related data in real-time. None of the use cases can wait for the data refresh to the warehouse to happen. The best place to do this reporting is embedded analytics inside the ERP or CRM software but its often the case that the reporting is often slow or does not offer the capabilities of a BI tool. Hence these requirements fall into the BI arena. The primary users of real-time reporting are operational line level employees in the manufacturing floor, shipping or in finance operations who use the data to perform day to day tasks. The BI user interface can be simple but effective for these needs as user is just trying to get the data they need at any given time.</p><p><strong>2.</strong> <strong>Operational Reporting</strong></p><p>Operational reporting is needed when the data being analyzed can be a few hours or few days old. This kind of reporting is useful to analyze order or call volumes count over the last few days, how many of the marketing leads have been generated by various campaigns this month. The data needed for operational reporting are usually in multiple systems and the reporting solution should be able to join these data together. For example, the campaign data could be in Eloqua or Marketo, leads data might be in Salesforce and the order data might be in Oracle or SAP. The operational reporting solution should be able to bring the data from these three sources and tie the data together to get a full picture of the campaign effectiveness. In most companies, the bulk of BI requirements fall into this category which is essential for running the business. The primary users of operational reporting are managers who are analyzing data to make decisions on their operations. They usually make decisions like how many trucks do I need for my shipments today, or which campaigns should I run today based on the data and analysis they were able to obtain from their operational reporting.</p><p><strong>3.</strong> <strong>Analytical Reporting</strong></p><p>This kind of reporting is useful to analyze data trends over months, quarters or years. The data is usually stored for the long term and frequently data is also stored as snapshots to get the view of the data at a specific day of a month. Then the data is usually summarized to give a long-term view of the various metrics. Some common examples are revenue by industry or region over many years or how gross margin for a product has changed from last year vs. this year. In this type of reporting the frequency of data refreshes can be once a day, week or month and data is usually summarized and stored for trend reporting. The user base here are analysts who are answering questions based on trends in the data or executives and senior managers of various departments to do long range planning of their business based on the historical trends. The BI tool used here should be very user friendly and will need to enable self-service for the analysts.</p><p>Now if you look at your traditional BI environments, all these three use models will be included in all the BI tools. For example, OBIEE will be performing real-time, operational or analytical reporting but so is Business Objects or Cognos or other tools, just that they will be doing the same for different departments or different source applications in your company. When you are trying to modernize the BI architecture, you can look across all your current BI applications and group the reports or dashboards serving these three use cases. Once they are grouped into different use cases, you have a good understanding how much your company uses each of these use models. Then once you have this analysis in hand you can proceed on working out the architecture for these three use models.</p><p>I will cover possible architecture for each of these use models in the following articles. Please stay tuned and let me know your comments on whether classifying your BI landscape into these use models makes sense for your company.</p><h4><strong>Part 2: Architectures for Real-Time, Operational, and Analytical Reporting that will help speed up your BI modernization journey</strong></h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_f7ZaRB2AFz7K89nZbxNFQ.jpeg" /></figure><p>In the <a href="https://medium.com/@bnatarajan/how-to-work-on-a-bi-modernization-strategy-for-your-company-part-1-4efccf8cbd8">Part 1</a> of this article I had written about what should be the goals of a BI Modernization strategy and how grouping your existing BI use cases by data latency requirements can help clarify your BI Strategy. The three groupings by data latency are Real-Time, Operational and Analytical Reporting. In this article I will cover possible architectures for each of these use models.<strong>Design Pattern</strong></p><p>The key design assumption for these architectures are the following –</p><p>1. Companies will have multiple visualization tools</p><p>2. A single architecture and set of tools might not be able to satisfy all the three data latency use cases.</p><p><strong>Real-Time Reporting</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*3Wq3x5DOEk2GjE_t.jpeg" /></figure><p>Real-Time Reporting Architecture</p><p>The characteristics of real time reporting are that users need the data “as it is any point” in the source system when they run the report. The users need data in real-time from one data source like Oracle EBS, SAP, or Salesforce, and the volume of data required for each report is relative small — few tens of thousands at most and the UI needs of the reports are simple, which means mostly they need a table like report. Given the fact that there are multiple source systems that need real-time reporting each might have a different way of accessing the data. Some might have JDBC/ODBC interfaces where you can run SQL queries while others might have an API interface. The other consideration is that there might be multiple visualization tools which can be used for real-time reporting. So, given the fact that there are multiple back end systems and multiple visualization platforms and there is no need to join one system to another (no need for query federation) a central data modeling tool would be ideal. This will avoid creating data models in different data visualization tools and give the capability to restrict the data volume and query performance on the source systems, so the reporting platforms do not impact the source system resources. A leading data virtualization tool like Denodo will be a good solution for this use case and I have written about it <a href="https://medium.com/@bnatarajan/what-is-data-virtualization-and-how-it-can-unlock-real-time-insights-directly-from-source-systems-85c279502652">here</a> in detail. The data virtualization tool will provide the semantic layer and it will enable the various BI tools to connect to it so if a new BI tool is introduced there is no need to redo the data model.</p><p><strong>Operational Reporting</strong></p><p>The data needed for operational reporting are usually in multiple systems and the reporting solution should be able to join these data together. Each of these systems can have data in a different shape and format and hence it is important to bring the required data into a central database. If you have a lot of data sources and millions of rows that need to be brought in, then query federation is usually not an option as there will be significant performance differences between the different sources and hence user experience might not be optimal. So, you will need to decide on an ELT/ETL tool to move the data and a database to host the data. Once the data is brought into the central database then the data modeling exercise begins where the data is reshaped and joined appropriately based on the reporting requirements. After the data modeling is done then business friendly labeling can be applied and calculated columns like currency translations, gross margin can be added. Once this is complete, then the reporting and data visualization activity can begin.</p><p>There are two approaches companies can make –</p><ol><li><strong>Choose one tool which does all the tasks</strong> — ETL/ELT, Database, Data Modeling, and Data Visualization. There is a new industry term for these kinds of tools called Unified Data and Analytics Platforms (UDAP). These UDAP tools don’t have best of breed features in any of these four domains but the advantage is that they have a single stack which leads to well-integrated set of capabilities which should lead to significant time to value. Examples of these tools include Incorta, Birst, Domo, Pentaho and others. I specifically have good experience with Incorta which has been very good for operational reporting from large complicated on-premise and cloud data sources and joining them at scale. I have written about the Incorta architecture and capabilities <a href="https://medium.com/@bnatarajan/how-incorta-works-and-where-it-can-fit-into-your-enterprise-bi-strategy-283b18711e1d">here</a>.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*dKNd6unYy0-qskXc.jpeg" /></figure><p>Unified Data Analytics Platform</p><p>2. <strong>Use best of breed software to create operational reporting stack</strong></p><p>A best of breed approach can also be taken to create the operational reporting stack. In this case, you will have to choose an ETL/ELT including CDC mechanism which can get near real-time data into the target database quickly and without much transformation in the process. I have talked about how to enable real-time analytics to Snowflake <a href="https://medium.com/@bnatarajan/how-to-enable-real-time-analytics-in-snowflake-with-data-from-on-premise-databases-603e17220bb5">here</a> in detail. The transformation will happen after it is loaded into the target database. In this case, an ETL/ELT tool like Matillion, Stitch, FiveTran and others can be used to move the data into the target database. In today’s world the target database totally makes sense to be one of the major cloud platforms like Snowflake, Redshift, or Google Big Query. If you are using a tool like Stitch or FiveTran which are primarily data ingestion tools then using <a href="https://www.getdbt.com/">dbt</a> to transform the data will be very useful. Matillion does both ingestion and transformation as well as orchestration.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*i3Dkd0djuD-vv5gB.jpeg" /></figure><p>Using Best of Breed tools for Operational Reporting</p><p>This stack works best if all the major data sources are on the cloud. If there are large volumes of on-premise data and data is needed to be loaded several times a day, I don’t think this will be a very robust data pipeline. The biggest advantage is that you will have your BI tool of choice for operational, analytical and real-time reporting. You will have to evaluate your data sources, volume and frequency of loads to make sure this is the best architecture for your operational reporting.</p><p><strong>Analytical Reporting</strong></p><p>Analytical reporting has the following characteristics –</p><p>1. Analyze data trends over months, quarters or years.</p><p>2. Frequency of data loads can be daily, weekly or monthly from various sources. No need for loading several times a day.</p><p>3. The data is usually stored for the long term in the form of snapshots.</p><p>4. Data is summarized to a more aggregate form.</p><p>5. Data can come from multiple sources.</p><p>6. Analysts and users prefer to use their favorite BI tools. The end users skew more towards senior management.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*YEqHjH1UAXz1QHhp.jpeg" /></figure><p>Proposed Modern tech stack for Analytical Reporting</p><p>The proposed architecture for analytical reporting is not too different from the best of breed tools for operational reporting. This architecture should address all the six major characteristics of analytical reporting.</p><p>The important distinction between operational reporting and analytical reporting are the reduced frequency of data loads, use of snapshots and aggregations to store data for long term, and the use the best self-service data visualization tool that your organization has access to. The modern cloud data warehouses like Snowflake, Redshift and Google Big Query are well suited for this purpose as they perform fast, provide cheap storage for snapshots and data retention and can be accessed by a wide variety of BI tools for visualization.</p><p><strong>Summary</strong></p><p>When you look to modernizing the data architecture for your company, it might be helpful to classify your current BI landscape into real-time, operational and analytical reporting buckets. This will give clarity on how significant each of these use models are for your company. Once that is done you can design an architecture for each of these and then build your existing reports and analysis into one of these three architectures and then continue using these platforms for your future requirements.</p><p>I would love feedback on whether thinking about your BI landscape in this way using data latency requirements is useful to you and your organization. I look forward to your comments.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d1cbd1210a30" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to work on a BI Modernization strategy for your company — Part 2]]></title>
            <link>https://bnatarajan.medium.com/how-to-work-on-a-bi-modernization-strategy-for-your-company-part-2-e16562013fdf?source=rss-d930152461ff------2</link>
            <guid isPermaLink="false">https://medium.com/p/e16562013fdf</guid>
            <category><![CDATA[business-intelligence]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[enterprise-software]]></category>
            <category><![CDATA[cloud-computing]]></category>
            <category><![CDATA[database]]></category>
            <dc:creator><![CDATA[Bharath Natarajan]]></dc:creator>
            <pubDate>Mon, 07 Sep 2020 01:52:05 GMT</pubDate>
            <atom:updated>2020-09-12T17:40:10.026Z</atom:updated>
            <content:encoded><![CDATA[<h3><strong>How to work on a BI Modernization strategy for your company — Part 2</strong></h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_f7ZaRB2AFz7K89nZbxNFQ.jpeg" /><figcaption>Photo by <a href="https://unsplash.com/@rosssneddon">Ross Sneddon</a> on <a href="https://unsplash.com/">Unsplash</a></figcaption></figure><p>In the <a href="https://medium.com/@bnatarajan/how-to-work-on-a-bi-modernization-strategy-for-your-company-part-1-4efccf8cbd8">Part 1</a> of this article I had written about what should be the goals of a BI Modernization strategy and how grouping your existing BI use cases by data latency requirements can help clarify your BI Strategy. The three groupings by data latency are Real-Time, Operational and Analytical Reporting. In this article I will cover possible architectures for each of these use models.</p><p><strong>Design Pattern</strong></p><p>The key design assumption for these architectures are the following –</p><p>1. Companies will have multiple visualization tools</p><p>2. A single architecture and set of tools might not be able to satisfy all the three data latency use cases.</p><p><strong>Real-Time Reporting</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*uOsVBtqSh_76tTRw06EgVQ.jpeg" /><figcaption><strong>Image by Author</strong></figcaption></figure><p>The characteristics of real time reporting are that users need the data “as it is any point” in the source system when they run the report. The users need data in real-time from one data source like Oracle EBS, SAP, or Salesforce, and the volume of data required for each report is relative small — few tens of thousands at most and the UI needs of the reports are simple, which means mostly they need a table like report. Given the fact that there are multiple source systems that need real-time reporting each might have a different way of accessing the data. Some might have JDBC/ODBC interfaces where you can run SQL queries while others might have an API interface. The other consideration is that there might be multiple visualization tools which can be used for real-time reporting. So, given the fact that there are multiple back end systems and multiple visualization platforms and there is no need to join one system to another (no need for query federation) a central data modeling tool would be ideal. This will avoid creating data models in different data visualization tools and give the capability to restrict the data volume and query performance on the source systems, so the reporting platforms do not impact the source system resources. A leading data virtualization tool like Denodo will be a good solution for this use case and I have written about it <a href="https://medium.com/@bnatarajan/what-is-data-virtualization-and-how-it-can-unlock-real-time-insights-directly-from-source-systems-85c279502652">here</a> in detail. The data virtualization tool will provide the semantic layer and it will enable the various BI tools to connect to it so if a new BI tool is introduced there is no need to redo the data model.</p><p><strong>Operational Reporting</strong></p><p>The data needed for operational reporting are usually in multiple systems and the reporting solution should be able to join these data together. Each of these systems can have data in a different shape and format and hence it is important to bring the required data into a central database. If you have a lot of data sources and millions of rows that need to be brought in, then query federation is usually not an option as there will be significant performance differences between the different sources and hence user experience might not be optimal. So, you will need to decide on an ELT/ETL tool to move the data and a database to host the data. Once the data is brought into the central database then the data modeling exercise begins where the data is reshaped and joined appropriately based on the reporting requirements. After the data modeling is done then business friendly labeling can be applied and calculated columns like currency translations, gross margin can be added. Once this is complete, then the reporting and data visualization activity can begin.</p><p>There are two approaches companies can make –</p><ol><li><strong>Choose one tool which does all the tasks</strong> — ETL/ELT, Database, Data Modeling, and Data Visualization. There is a new industry term for these kinds of tools called Unified Data and Analytics Platforms (UDAP). These UDAP tools don’t have best of breed features in any of these four domains but the advantage is that they have a single stack which leads to well-integrated set of capabilities which should lead to significant time to value. Examples of these tools include Incorta, Birst, Domo, Pentaho and others. I specifically have good experience with Incorta which has been very good for operational reporting from large complicated on-premise and cloud data sources and joining them at scale. I have written about the Incorta architecture and capabilities <a href="https://medium.com/@bnatarajan/how-incorta-works-and-where-it-can-fit-into-your-enterprise-bi-strategy-283b18711e1d">here</a>.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ir6IiFu3uN9WLH8JxUu20w.jpeg" /><figcaption><strong>Image by Author</strong></figcaption></figure><p>2. <strong>Use best of breed software to create operational reporting stack</strong></p><p>A best of breed approach can also be taken to create the operational reporting stack. In this case, you will have to choose an ETL/ELT including CDC mechanism which can get near real-time data into the target database quickly and without much transformation in the process. I have talked about how to enable real-time analytics to Snowflake <a href="https://medium.com/@bnatarajan/how-to-enable-real-time-analytics-in-snowflake-with-data-from-on-premise-databases-603e17220bb5">here</a> in detail. The transformation will happen after it is loaded into the target database. In this case, an ETL/ELT tool like Matillion, Stitch, FiveTran and others can be used to move the data into the target database. In today’s world the target database totally makes sense to be one of the major cloud platforms like Snowflake, Redshift, or Google Big Query. If you are using a tool like Stitch or FiveTran which are primarily data ingestion tools then using <a href="https://www.getdbt.com/">dbt</a> to transform the data will be very useful. Matillion does both ingestion and transformation as well as orchestration.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_4VTcBY7wQXXYOiZ30R0Dw.jpeg" /><figcaption><strong>Image by Author</strong></figcaption></figure><p>This stack works best if all the major data sources are on the cloud. If there are large volumes of on-premise data and data is needed to be loaded several times a day, I don’t think this will be a very robust data pipeline. The biggest advantage is that you will have your BI tool of choice for operational, analytical and real-time reporting. You will have to evaluate your data sources, volume and frequency of loads to make sure this is the best architecture for your operational reporting.</p><p><strong>Analytical Reporting</strong></p><p>Analytical reporting has the following characteristics –</p><p>1. Analyze data trends over months, quarters or years.</p><p>2. Frequency of data loads can be daily, weekly or monthly from various sources. No need for loading several times a day.</p><p>3. The data is usually stored for the long term in the form of snapshots.</p><p>4. Data is summarized to a more aggregate form.</p><p>5. Data can come from multiple sources.</p><p>6. Analysts and users prefer to use their favorite BI tools. The end users skew more towards senior management.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ipsSCxJOVkVmbK1L0AdrhA.jpeg" /><figcaption><strong>Image by Author</strong></figcaption></figure><p>The proposed architecture for analytical reporting is not too different from the best of breed tools for operational reporting. This architecture should address all the six major characteristics of analytical reporting.</p><p>The important distinction between operational reporting and analytical reporting are the reduced frequency of data loads, use of snapshots and aggregations to store data for long term, and the use the best self-service data visualization tool that your organization has access to. The modern cloud data warehouses like Snowflake, Redshift and Google Big Query are well suited for this purpose as they perform fast, provide cheap storage for snapshots and data retention and can be accessed by a wide variety of BI tools for visualization.</p><p><strong>Summary</strong></p><p>When you look to modernizing the data architecture for your company, it might be helpful to classify your current BI landscape into real-time, operational and analytical reporting buckets. This will give clarity on how significant each of these use models are for your company. Once that is done you can design an architecture for each of these and then build your existing reports and analysis into one of these three architectures and then continue using these platforms for your future requirements.</p><p>I would love feedback on whether thinking about your BI landscape in this way using data latency requirements is useful to you and your organization. I look forward to your comments.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e16562013fdf" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to work on a BI Modernization strategy for your company — Part 1]]></title>
            <link>https://bnatarajan.medium.com/how-to-work-on-a-bi-modernization-strategy-for-your-company-part-1-4efccf8cbd8?source=rss-d930152461ff------2</link>
            <guid isPermaLink="false">https://medium.com/p/4efccf8cbd8</guid>
            <category><![CDATA[bi-strategy]]></category>
            <category><![CDATA[bi-modernization]]></category>
            <category><![CDATA[big-data-analytics]]></category>
            <category><![CDATA[data-latency]]></category>
            <category><![CDATA[business-intelligence]]></category>
            <dc:creator><![CDATA[Bharath Natarajan]]></dc:creator>
            <pubDate>Sun, 17 May 2020 00:17:24 GMT</pubDate>
            <atom:updated>2020-09-11T20:30:14.225Z</atom:updated>
            <content:encoded><![CDATA[<h3><strong>How to create a BI Modernization strategy for your company — Part 1</strong></h3><p>How grouping your existing BI use cases by data latency requirements can help clarify your BI Strategy</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4WKa5dkP739j_Wr0v70jWw.jpeg" /><figcaption>Photo by <a href="https://www.pexels.com/@pixabay?utm_content=attributionCopyText&amp;utm_medium=referral&amp;utm_source=pexels">Pixabay</a> from <a href="https://www.pexels.com/photo/black-and-white-blackboard-business-chalkboard-356043/?utm_content=attributionCopyText&amp;utm_medium=referral&amp;utm_source=pexels">Pexels</a></figcaption></figure><p>A lot of companies are burdened by legacy BI platforms and are not able to deliver the right data to their users at the right time to run their business today. They know that they need to modernize their BI platforms but do not know how to go about it. First let’s look at what should be the goals of a BI Modernization strategy.</p><p>Here are the goals from my view for any BI Modernization Strategy -</p><p>1. <strong>Speed of change</strong> — Probably the most important goal. The new platforms should be able to add new fields or add new data sources much faster than legacy platforms.</p><p>2. <strong>Combine data from multiple sources</strong> — This one was always difficult to achieve in legacy tools but is possible with modern tools.</p><p>3. <strong>Superior performance</strong> — Report performance and data load timings should be significantly improved.</p><p><strong>4.</strong> <strong>Self Service — </strong>Enable business users and power users to build their own reports and dashboards without relying on IT and super users</p><p>5. <strong>Support different data latency requirements</strong> — Real Time, Operational and Analytical Reporting are the three ways to look at data latency requirements</p><p><strong>6.</strong> <strong>Reduce overall cost — </strong>The<strong> </strong>BI software licensing models and the<strong> </strong>costs of compute and storage have come down enormously leading to a significant reduction in costs if implemented correctly compared to legacy BI</p><p><strong>Grouping BI Use Models by Data Latency</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*BUwPLU_d2x5GpD8iyfsPsw.jpeg" /><figcaption>Image by Author</figcaption></figure><p>In most companies BI would have evolved in a somewhat organic manner over many years or decades. Many different BI tools would have been used at different times either brought in by acquisitions or by the best tool available at that time. When companies try to replace their legacy tools, they often try to replace one tool with another tool. For example, their project would like “we should move from OBIEE or Cognos to Tableau or Power BI” and/or “let’s move our data warehouse to Snowflake”. They then end up customizing or building solutions into Tableau/Power BI which it was not meant to do, and the project runs into issues.</p><p>A better strategy would be to consider the use models of your current BI solution. One way to look at your current use model is by data latency requirements.</p><p>There are three ways to classify use models by data latency –</p><p><strong>1.</strong> <strong>Real-Time Data</strong></p><p>When the users want to see the data as it is “right now” in the operational system then that is a real-time requirement. This kind of data needs are common in the supply chain, finance, sales or call center area. The supply chain users would want to see what are the orders they need to ship right now, or in finance they would want to see what is the GL Balance at 12 AM at the end of the quarter, or in sales they want to see quota to orders at frequent intervals at the end of a quarter or in call center they would like to see call volume related data in real-time. None of the use cases can wait for the data refresh to the warehouse to happen. The best place to do this reporting is embedded analytics inside the ERP or CRM software but its often the case that the reporting is often slow or does not offer the capabilities of a BI tool. Hence these requirements fall into the BI arena. The primary users of real-time reporting are operational line level employees in the manufacturing floor, shipping or in finance operations who use the data to perform day to day tasks. The BI user interface can be simple but effective for these needs as user is just trying to get the data they need at any given time.</p><p><strong>2.</strong> <strong>Operational Reporting</strong></p><p>Operational reporting is needed when the data being analyzed can be a few hours or few days old. This kind of reporting is useful to analyze order or call volumes count over the last few days, how many of the marketing leads have been generated by various campaigns this month. The data needed for operational reporting are usually in multiple systems and the reporting solution should be able to join these data together. For example, the campaign data could be in Eloqua or Marketo, leads data might be in Salesforce and the order data might be in Oracle or SAP. The operational reporting solution should be able to bring the data from these three sources and tie the data together to get a full picture of the campaign effectiveness. In most companies, the bulk of BI requirements fall into this category which is essential for running the business. The primary users of operational reporting are managers who are analyzing data to make decisions on their operations. They usually make decisions like how many trucks do I need for my shipments today, or which campaigns should I run today based on the data and analysis they were able to obtain from their operational reporting.</p><p><strong>3.</strong> <strong>Analytical Reporting</strong></p><p>This kind of reporting is useful to analyze data trends over months, quarters or years. The data is usually stored for the long term and frequently data is also stored as snapshots to get the view of the data at a specific day of a month. Then the data is usually summarized to give a long-term view of the various metrics. Some common examples are revenue by industry or region over many years or how gross margin for a product has changed from last year vs. this year. In this type of reporting the frequency of data refreshes can be once a day, week or month and data is usually summarized and stored for trend reporting. The user base here are analysts who are answering questions based on trends in the data or executives and senior managers of various departments to do long range planning of their business based on the historical trends. The BI tool used here should be very user friendly and will need to enable self-service for the analysts.</p><p>Now if you look at your traditional BI environments, all these three use models will be included in all the BI tools. For example, OBIEE will be performing real-time, operational or analytical reporting but so is Business Objects or Cognos or other tools, just that they will be doing the same for different departments or different source applications in your company. When you are trying to modernize the BI architecture, you can look across all your current BI applications and group the reports or dashboards serving these three use cases. Once they are grouped into different use cases, you have a good understanding how much your company uses each of these use models. Then once you have this analysis in hand you can proceed on working out the architecture for these three use models.</p><p>I will cover possible architecture for each of these use models in the following articles. Please stay tuned and let me know your comments on whether classifying your BI landscape into these use models makes sense for your company.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=4efccf8cbd8" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to enable real-time analytics in Snowflake with data from On-Premise databases]]></title>
            <link>https://bnatarajan.medium.com/how-to-enable-real-time-analytics-in-snowflake-with-data-from-on-premise-databases-603e17220bb5?source=rss-d930152461ff------2</link>
            <guid isPermaLink="false">https://medium.com/p/603e17220bb5</guid>
            <category><![CDATA[real-time-analytics]]></category>
            <category><![CDATA[data-warehouse]]></category>
            <category><![CDATA[etl]]></category>
            <category><![CDATA[change-data-capture]]></category>
            <category><![CDATA[snowflake]]></category>
            <dc:creator><![CDATA[Bharath Natarajan]]></dc:creator>
            <pubDate>Sat, 15 Feb 2020 22:16:20 GMT</pubDate>
            <atom:updated>2020-09-07T22:04:54.430Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*mpyrgqwMjfclV2oN1U2VIA.jpeg" /></figure><p>Snowflake is rapidly becoming the leading cloud data warehouse platform for all kinds of different analytics applications. I have written about <a href="https://medium.com/@bnatarajan/why-snowflake-is-a-great-option-to-move-your-on-premise-oracle-sql-server-and-teradata-data-6c353eade261">why snowflake is a great option for moving on-premise data warehouses to the cloud</a>. While moving to Snowflake has big advantages like no need for a server footprint, DBA, tuning, backup and recovery, it still needs a well thought out data pipeline from your source systems.</p><p><strong>A chance to simplify and reduce latency of your data pipelines</strong></p><p>If your company has been using a central data warehouse for several years, chances are your BI team uses a traditional tool like Informatica to extract, transform and load (ETL) data from one or several on-premise databases into the data warehouse built on Oracle, SQL Server or Teradata. These ETL processes would have been built and enhanced over many years and are probably very complex to maintain or change now. They were optimized for performance on traditional databases, the core of whose technology were written decades ago. The on-premise data warehouse will get loaded once or twice a day which run for several hours each and usually lead to downtime during loading for the business users. This method of ETL is completely outdated for today’s global 24x7 operations and real time data requirements. Now that you have decided to move to Snowflake as your data warehouse you should rethink your data pipeline completely.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9rRzTprNh-udQMUtn-yZyQ.jpeg" /><figcaption>CDC based real-time data integration into Snowflake</figcaption></figure><p><strong>Change Data Capture + Streaming as the data pipeline strategy</strong></p><p>In the last two years, there has been tremendous development of the partner and technology ecosystem around Snowflake. Now there are more than a dozen tools which enable real time or batch replication to Snowflake from on-premise databases. The tools have become sophisticated enough to be able to replicate only some of the tables and even in these tables they can replicate only some of the columns. This way you can replicate only the data needed for reporting and not the entire on-premise database to Snowflake.</p><p>The replication tools look at the logs generated by the on-premise databases which contain an encoded entry for each insert, update or delete that occurs in any table in the source database. For example, if your source database is Oracle then these tools can continuously replicate one or many Oracle tables to Snowflake. As a first step the tables to be replicated needs to be created Snowflake. Then the existing data needs to be loaded into the corresponding tables in Snowflake. Then the tool can read new data using LogMiner and continuously replicate it to Snowflake.</p><p><strong>Advantages of using the CDC approach of loading data into Snowflake are:</strong></p><p>1. Real time continuous data load into Snowflake without the need for specific time based data loads which used to run for hours</p><p>2. What you see in the source database is what you get in Snowflake. It is easy to verify the data quality and accuracy compared to traditional ETL</p><p>3. Uses the power of Snowflake performance to do the complex transformations needed and not in the ETL tool. Data can be processed many times faster than before.</p><p>4. Can capture deletes in the source and not require a full load to clean out the deletes.</p><p><strong>What are the tools available in the market?</strong></p><p>There are plenty of tools now available to replicate on-premise data to Snowflake and more are getting added as Snowflake is getting very popular in enterprises large and small. It is important for companies to evaluate these tools on various vectors including capability, performance, pricing, support, data transformations, roadmap, long term viability of the company among other factors. You need to look at all the capabilities of the tool holistically so that your company can standardize on one tool instead of supporting multiple tools for different needs.</p><p>Here are some leading tools that are available in the market which can replicate on-premise databases to Snowflake using CDC –</p><p>1. <a href="https://www.striim.com/partners/real-time-data-to-snowflake/">Striim</a></p><p>2. Attunity (bought by Qlik) — now called <a href="https://www.qlik.com/us/data-streaming/data-streaming-cdc">Qlik Replicate</a></p><p>3. <a href="https://www.stitchdata.com/integrations/oracle/snowflake/">Stitch</a> (bought by Talend)</p><p>4. <a href="https://fivetran.com/directory/oracle">FiveTran</a></p><p>5. <a href="https://www.matillion.com/resources/blog/setting-up-change-data-capture-cdc-in-matillion-etl/">Matillion</a></p><p>6. <a href="https://streamsets.com/support/connectors/#origin-connectors">Streamsets</a></p><p>There are other tools which are coming up based on a simple Google search — <a href="https://www.cdata.com/sync/?gclid=EAIaIQobChMIqq-J36LU5wIVZCCtBh0sUQpXEAAYAyAAEgJeE_D_BwE">Cdata</a>, <a href="https://www.bryteflow.com/cdc-to-snowflake/">Bryteflow</a>, <a href="https://www.hvr-software.com/blog/cdc-for-snowflake/">HVR Software</a>, <a href="https://etlworks.com/">ETLWorks</a>. <a href="https://www.diyotta.com/data-sheets/unlock-the-power-of-snowflake-cloud-data-warehouse">Diyotta</a>. I’m sure more will be there with a deeper search.</p><p>In summary, the full power of Snowflake can be unlocked by enabling a real-time data replication from on-premise databases to Snowflake. There are a lot of highly capable tools available in the market. With careful evaluation and a solid POC, you can enable your business users to gain real time insights into their operations with the power of Snowflake and in the process become the data hero of your company!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=603e17220bb5" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How Incorta works and where it can fit into your enterprise BI strategy]]></title>
            <link>https://bnatarajan.medium.com/how-incorta-works-and-where-it-can-fit-into-your-enterprise-bi-strategy-283b18711e1d?source=rss-d930152461ff------2</link>
            <guid isPermaLink="false">https://medium.com/p/283b18711e1d</guid>
            <category><![CDATA[analytics]]></category>
            <category><![CDATA[business-intellgence]]></category>
            <category><![CDATA[incorta-architecture]]></category>
            <category><![CDATA[incorta]]></category>
            <dc:creator><![CDATA[Bharath Natarajan]]></dc:creator>
            <pubDate>Sat, 19 Oct 2019 08:24:46 GMT</pubDate>
            <atom:updated>2019-10-29T20:59:49.629Z</atom:updated>
            <content:encoded><![CDATA[<h3>How Incorta works and how it can fit into your enterprise BI strategy</h3><p>Incorta is the new kid on the block in a very competitive enterprise business intelligence tools space. The company started around 6 years back and we have been using it for the last 2.5 years successfully for operational reporting.</p><p>This blog was written in October 2019 and represents Incorta features as of their 4.3.x version. Please keep in mind Incorta builds significant new capabilities in each of their quarterly releases and the product changes over time. I will try to add new articles to go over significant new capabilities in the platform.</p><p>Incorta has taken a very different approach to solve BI and analytics problems than any other vendor in the market. They are essentially four different integrated BI tools in one.</p><p>These are the -</p><p>1. Data Extraction Layer</p><p>2. Database layer</p><p>3. Data Modeling Layer</p><p>4. Data Visualization Layer</p><p>In addition to the above Incorta has a very close integration with Spark and comes with the same installation package. Spark integration provides 2 functions for Incorta -</p><p>1. Advanced calculations and joins</p><p>2. SQL Interface (SQLi) or Datahub for external BI tools to connect with Incorta</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*GauhS4QKigvFDRk5CBDmLw.jpeg" /><figcaption><strong>Incorta Architecture</strong></figcaption></figure><p>In a traditional BI environment, you have at least three tools (if not four if you have a separate semantic layer tool) to do the same set of tasks. You will have a ETL tool like Informatica or SSIS to load into a database like Oracle or SQL Server and then data model and visualize the data in a BI tool like MicroStrategy, OBIEE or Tableau. You must keep in mind that these are best of breed tools which have been around for a long time with tons of features.</p><p>Incorta does a lot of these tasks very well but it cannot match the same set of features as these best of breed solutions in the various categories. As a combined package it delivers a lot of capabilities for BI developers and users. But first let’s take a look at the various components of the Incorta platform.</p><p><strong>Components of Incorta platform</strong></p><p>Here is a very brief and simplified description of the various components of the Incorta platform and what is special or unique about them -</p><p><strong>1.</strong> <strong>Data Extraction Layer</strong></p><p>The first layer of Incorta platform is the Data Extraction layer. Here they have built a technology called Direct Data Mapping where you can extract data table by table from the source system both as a full load or as an incremental load. The data then goes into a parquet file in the Incorta platform and then gets loaded into memory. Each table can have a have a full load and incremental load SQL</p><p>Full Load SQL can look as simple as the following -</p><p>Select COL_A, COL_B, COL_C from TABLE_1 where CREATED_DATE&gt;=’01–01–2016’</p><p>Incremental Load SQL can look like the following -</p><p>Select COL_A, COL_B, COL_C from TABLE_1 where LAST_UPDATE_DATE&gt;?</p><p>When the load is kicked off the table gets populated for a full load and then in each incremental run it will get the incremental data from the source. Incorta can pull data from almost all traditional databases like Oracle, SQL Server, Cloud applications like Salesforce or from files stored on on-premise or cloud storage. The number of source applications supported increases in each release of the Incorta platform.</p><p>After loading data from source systems into parquet files, materialized views (MV) can be created using the Incorta UI and can be written in SQL or PySpark. During run time Incorta sends the request to Spark to read the data from Parquet files and compute the data and reload into a separate parquet file which then gets loaded into memory during the loading stage. The Spark component comes with the Incorta package but does involve some set up to be done for it to work properly.</p><p>The advantage of pulling table by table is that the performance of the loads is very fast as there is no need to perform the joins in the source system and the second advantage is that it becomes very easy to add new columns into Incorta as all you need to do is add a new column in the extract SQL and run a full load. Hence an addition of a new column in Incorta can be done in a few minutes compared to days or months using traditional ETL methods.</p><p><strong>2.</strong> <strong>Database Layer</strong></p><p>Once the data is extracted into parquet files it is then loaded into the in-memory database engine and then the joins defined in the schema are precomputed at the end of the load. This precomputation of the joins leads to the revolutionary performance of the reports in Incorta. Even if the report built in Incorta has 30 or 40 joins across large tables containing millions of rows the data still comes back in a few seconds. This kind of performance is unheard off in a traditional database.</p><p><strong>3.</strong> <strong>Data Modeling Layer</strong></p><p>Incorta data modeling layer comes in two forms — physical schemas and business schemas.</p><p>In the physical schema you can create aliases to base tables and materialized views and create joins between various tables and MVs. Business schemas can be created to present a flat, user friendly representation of the physical schema to the user building the reports. Columns can be brought in from one or more tables in different physical schemas and renamed. Formula columns can be added either in physical or business schemas.</p><p><strong>4.</strong> <strong>Visualization Layer</strong></p><p>Incorta comes with its own visualization layer. Insights (Reports) and Dashboards can be created on top of physical or business schemas. Analyzer users can create insights or reports with in-built filters, prompts and bookmarks that then end users can consume. There are multiple types of visualizations like charts, pivot tables as well as drill downs are available.</p><p>Incorta visualization is good enough for most purposes but when compared to a more best of breed product like Tableau, Power BI or MicroStrategy it does fall short a bit. The Incorta development are rapidly adding features in various releases and they should be able catch up to the other tools in terms of UI capabilities sooner than later.</p><p>In addition, Incorta provides a SQL Interface using which other BI tools like Tableau or Power BI can connect to the business schema, physical schema or the Parquet layer.</p><p><strong>Where does Incorta fit in your BI architecture</strong></p><p>Incorta comes with several distinct advantages compared to traditional BI tools –</p><p>- revolutionary performance of reports</p><p>- no tuning or DB maintenance needed</p><p>- can hold massive amounts of data (billions of rows of data) in memory</p><p>- complex data modeling is possible</p><p>- simple to develop and make changes as reshaping of data is not needed</p><p>- easy to learn and get started for any developer</p><p>- a platform where all the four layers of BI are available</p><p>- cross data source joins is very easy to achieve</p><p>There are three kinds of reporting in any company — Operational Reporting, Real-Time reporting and Analytical Reporting (snapshots, period over period analysis). Incorta’s sweet spot is operational reporting requirements where data can be pulled from various transaction systems and joined together to provide lighting fast reports. You can load data into Incorta several times a day from your source systems and provide operational insights that the users need to make daily decisions to run their business.</p><p>In summary, if you are looking for a solution to get lighting quick operational reporting with data joined from multiple complex source systems then Incorta is the one of the best options that is out there today.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=283b18711e1d" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Consolidation in the Business Intelligence Market]]></title>
            <link>https://bnatarajan.medium.com/consolidation-in-the-business-intelligence-market-61f48c585ed6?source=rss-d930152461ff------2</link>
            <guid isPermaLink="false">https://medium.com/p/61f48c585ed6</guid>
            <category><![CDATA[salesforce]]></category>
            <category><![CDATA[tableau]]></category>
            <category><![CDATA[looker]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[business-intelligence]]></category>
            <dc:creator><![CDATA[Bharath Natarajan]]></dc:creator>
            <pubDate>Sun, 21 Jul 2019 06:55:14 GMT</pubDate>
            <atom:updated>2019-07-21T07:12:13.885Z</atom:updated>
            <content:encoded><![CDATA[<h3><strong>What to expect from the recent consolidation in the Business Intelligence Market</strong></h3><p><strong>Why the Looker acquisition by Google makes sense and the Tableau acquisition by Salesforce does not</strong></p><p>There were two very high-profile acquisitions in the month of June 2019 in the BI tools space. One was the acquisition of Looker by Google ($2.6 Billion) and another one is the acquisition of Tableau by Salesforce ($16 Billion).</p><p>Out of the two major acquisitions, one makes a lot of sense (Looker by Google) and the other one not so much (Salesforce by Tableau). Here is my view of these two acquisitions -</p><p><strong>Looker acquisition by Google Cloud</strong></p><p>Google has been investing heavily in the enterprise cloud area for the last several years. The scale and scope of their investment was expected to increase drastically when they hired away Thomas Kurian from Oracle. Google Cloud Platform (GCP) entered the enterprise BI market via Google Big Query. Google Big Query has evolved over the years by starting as purely a cloud based serverless columnar store database with a lot of features for the big data developer community. It was not really considered a true enterprise data warehouse supporting all the SQL syntax like Oracle, SQL Server and Teradata. Google probably realized over time how big the enterprise data warehouse market truly was by looking at the success of Snowflake and has caught up in terms of full SQL support. Now it is considered a fully functional cloud data warehouse to which companies can move their traditional data warehouse workloads.</p><p>In addition, Google was also building their own BI tools on GCP — Google Data Studio. Google Data Studio is considered as a simple BI tool which primarily connects to Big Query and other Google properties and provides reporting and visualizations. It was never a serious contender as an enterprise BI tool against the likes of Tableau and Qlik. GCP also announced several data integration tools this year — <a href="https://cloud.google.com/dataflow/">Cloud Data Flow</a> and <a href="https://cloud.google.com/data-fusion/">Cloud Data Fusion</a>. Although these tools look very powerful with lots of features they don’t look like a good fit for the traditional ETL developers who are familiar with Informatica or SSIS. These tools look more for the modern data engineers in startups which is a whole different but less lucrative market than the traditional BI market.</p><p>Not surprisingly, a significant development which happened earlier this year after Thomas Kurian joined was the <a href="https://cloud.google.com/blog/topics/inside-google-cloud/google-announces-intent-to-acquire-alooma-to-simplify-cloud-migration">acquisition of Alooma</a>. <a href="https://www.alooma.com/">Alooma</a> specializes in moving data from various cloud data application sources like Salesforce or Workday to cloud data warehouses like Snowflake and Big Query as well as syncing on-premise data sources to the cloud. Clearly the future focus for Alooma will be syncing more and more data sources to Google Big Query. Google also announced an in-memory caching layer on top of Big Query called <a href="https://cloud.google.com/bi-engine/docs/overview">BI Engine</a>. BI Engine can cache data sets from Big Query for extremely fast results and can compete with Tableau’s hyper file or with Qlik’s in-memory engine in terms of performance.</p><p>The missing piece in GCP’s data platform was an enterprise level BI tool. Google Cloud was an early partner with Looker and would have had a ring side view of its popularity and growth. Looker’s big advantage was its all cloud architecture, semantic layer using LookML and integration of the LookML code with Git for versioning. Looker is very popular with startups and is starting to penetrate the enterprise companies who have code-savvy data analysts.</p><p>With the acquisition of Alooma and Looker and its own BI Engine and Big Query, GCP now has a complete stack for enterprise BI. GCP can now provide a one stop solution for replacing traditional enterprise BI built on top of Oracle/Teradata warehouses, Informatica as ETL and OBIEE/BO/Cognos/MicroStrategy as BI tools.</p><p>If they make these tools easy to purchase, easy to build end to end BI solutions then they really have a gold mine in their hands. It is going to be critical for Google to create a seamless experience across all these four tools for users and developers. They must keep in mind that the customer base is SQL savvy and not code savvy. The tools need to work together without much configuration or coding to truly go after the enterprise BI market.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_F8XqYirGoKgULGU-tpeuA.png" /><figcaption><strong>Google Cloud BI Architecture post Looker and Alooma acquisitions</strong></figcaption></figure><p>It remains to be seen if Google can keep its focus for the long haul and really invest in these four capabilities (database, ETL, caching layer and visualization) rather than building more and more tools which distract them and confuse customers as to what their road map is.</p><p>Google clearly can provide excellent advanced analytics, ML and AI capabilities on top of this core set of capabilities. That market can be captured if GCP starts hosting the data and the visualizations. It is well known that without the data in proper form, advanced capabilities like ML and AI are hard to achieve.</p><p><strong>Tableau acquisition by Salesforce</strong></p><p>While Google’s acquisition of Looker was all cash and much smaller, the Tableau acquisition by Salesforce was a massive all stock $16 billion deal. Tableau’s acquisition by Salesforce is a real head scratcher not only in terms of price paid but also because of the significant architecture incompatibilities and Salesforce’s own relatively poor post-acquisition track record for the various companies they have purchased so far.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1014/1*JjNZJG3LcFK4A7MzONgGMA.png" /><figcaption><strong>Salesforce needs to continue acquiring to have a end to end BI Solution</strong></figcaption></figure><p>The motivation for entering the BI space makes sense for Salesforce as they can use their much bigger sales channel and customer base to expand into new markets. The question is why Tableau?</p><p>Customers love Tableau because of its easy to use and powerful desktop product. Tableau does have server and cloud products, but they were built more as a sharing mechanism for dashboards and workbooks between users rather than as the primary development platform. Tableau also completely relies on data extracts in .hyper form to perform in a reasonable manner. This adds a whole layer of complexity in the environment as it leads to two stage ETLs and data sitting in various desktops leading to security issues. Salesforce’s whole premise was to get rid of on-premise software and especially desktop-based applications.</p><p>They might have been better off buying cloud based BI tools like Domo rather than Tableau. With Tableau they now must explain to the customers who love the desktop tool in the ultra-competitive BI market as to what their future architecture is for the product. Microsoft with its Power BI tool set which includes a near free desktop product was already giving Tableau a run for their money, but now their sales team can create fear and uncertainty in the market regarding Tableau’s future architecture.</p><p>One explanation for the acquisition of Tableau is that Salesforce may not be done yet but is planning on adding a cloud data warehouse as well as a ETL tool to its product set. This might lead them to buy Alteryx or Talend for ETL and possibly Snowflake as the data warehouse. But this is going to be an extremely costly route to take as each of these companies are already very big. Salesforce’s shareholders will demand that the Tableau acquisition has led to significant growth acceleration for both products before its next big acquisition in the analytics space.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=61f48c585ed6" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[What is Data Virtualization and how it can unlock real-time insights directly from source systems]]></title>
            <link>https://bnatarajan.medium.com/what-is-data-virtualization-and-how-it-can-unlock-real-time-insights-directly-from-source-systems-85c279502652?source=rss-d930152461ff------2</link>
            <guid isPermaLink="false">https://medium.com/p/85c279502652</guid>
            <category><![CDATA[data-virtualization]]></category>
            <category><![CDATA[semantic-model]]></category>
            <category><![CDATA[denodo]]></category>
            <category><![CDATA[business-intelligence]]></category>
            <dc:creator><![CDATA[Bharath Natarajan]]></dc:creator>
            <pubDate>Wed, 15 May 2019 17:46:11 GMT</pubDate>
            <atom:updated>2019-05-15T17:47:28.201Z</atom:updated>
            <content:encoded><![CDATA[<p>In today’s fast-moving business environment there are a lot of reporting requirements which cannot wait for a batch process to load data from operational systems. The business users need the data as it is currently in their on-premise ERP, CRM or any of the cloud systems. For example, towards the end of a fiscal quarter, sales and finance leaders want to know the order bookings in real-time so that they have a good idea if they are going to “make” the quarter or not in terms of revenue. They cannot wait 4–8 hours of delay for a ETL job to finish.</p><p>Data virtualization (DV) is a method of building a “logical” warehouse by connecting “virtually” to various source systems and combining or joining them “virtually” to provide a unified data layer for BI systems or enterprise applications to query from.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Y1RIKS3R_vTwDgphMTmOkg.jpeg" /><figcaption><strong>High Level Architecture of Data Virtualization</strong></figcaption></figure><p>The alternate to the data virtualization approach of providing a unified layer is the traditional ETL approach of moving data from the various source systems at periodic intervals and loading them into a physical data warehouse. The data in the warehouse is then combined by loading them into a target star schema or snowflake data model. ETL loads are usually a batch process and are loaded once a day or a few times a day depending on the time it takes to load and the data latency requirements.</p><p><strong>How DV Works?</strong></p><p>At the core of it DV is a SQL generation tool with an easy to use UI to build a semantic layer across various data sources. Instead of loading data into a physical warehouse by extracting, transforming and loading (ETL) from various source systems, with DV the data will primarily remain in the source systems and the DV tool will load the metadata of the tables (or other objects like views) from source systems into the DV tool. We can call these virtual tables as “Physical Views”. Once the metadata of the tables are loaded, the DV tool will allow the developer to perform various manipulations to these physical views including joins, and formula columns to enrich them with required business logic. We can name these as “Logical Views”. Once the joins and formula columns are added the developer can then build out the “BI Datasets” using these various logical views to create datasets which are flattened views of the data for a specific business purpose. These datasets can include fixed filters if they are always needed.</p><p>These flattened datasets will be then be imported in a BI tool like MicroStrategy, Power BI or Tableau. In these BI tools, users can add further business formula columns, filters or even join two “BI Datasets” from the DV tool. The DV tool will take the input call from the BI tools and then translate the SQL which is appropriate to the specific backend data source, be it a database like Oracle, SQL Server, Teradata or a cloud system using their API like Salesforce.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*eUF4dtsUK94fBA37WTX9Bw.jpeg" /><figcaption><strong>How Data Virtualization Works</strong></figcaption></figure><p>The best DV tools perform SQL tuning before sending queries to the backend data sources. Instead of just sending the base SQL to the backend the DV tool will introspect the incoming request, optimize the query for the specific backend data source and then send the SQL or API call to that data source.</p><p>In addition, the leading DV tools can cache any of physical views, logical views or BI Datasets for better performance if the source is slow, either in a native database or a commercial database like Oracle or Snowflake.</p><p><strong>Advantages of DV</strong></p><p>Data virtualization comes with a lot of benefits -</p><p>1. Simplified view of complex source system schemas</p><p>If the semantic layer is built in the DV tool and the “BI Datasets” are exposed as a flat table/view to be imported in a BI tool like Tableau or Power BI, the complexity of the source schema like Oracle EBS or SAP will be hidden to the BI developers. BI developers are usually not very familiar with the source system tables and struggle to build a semantic layer and instead depend on a database view in the source system. Instead, building the semantic layer in the DV tool not only simplifies a complex schema, it also avoids maintaining a complex and hard to change physical views in source databases.</p><p>2. Speed of development</p><p>Since DV just imports the metadata of tables and creates virtual tables which mirrors the source objects, complex ETL to achieve real time reporting can be avoided. Adding new fields or tables in ETL could take several weeks to months as star schemas have to be designed and built, but in DV the shape of the data can be preserved to be the same as the source and hence adding new tables or fields will be much easier and quicker.</p><p>3. Real time reporting need</p><p>Since DV points to the source system directly, either through ODBC/JDBC to the database or through an API to cloud systems the latency of the data is now zero to a minimum. The data available to the user is real-time and for use cases where users need data immediately as it changes DV really comes through as a great option.</p><p>4. Source system changes can be hidden from BI tools</p><p>A big benefit of DV is that when a source system is upgraded or even if the company replaces a source system with another or is planning to just move databases from let’s say Oracle to Snowflake, virtualizing the semantic layer will help hiding these complex changes in the environment from the various BI tools. To accommodate changes which occur in source systems due to upgrades or database changes, in the DV tool the data source connection needs to be re-pointed to the new database if the schema is the same or some changes in relevant physical views will be needed if there are schema changes like column additions etc. This way the reports and visualizations built on the various BI tools do not have to change and users are not impacted.</p><p>5. Performance optimizations and caching</p><p>Most leading DV tools provide the ability to cache certain tables from the source database or cache datasets as well. Caching will significantly improve the performance of the reports in the BI tools. The best DV tools tunes the queries they receive from the BI tools and then pushes them down to the source databases. They also find ways to extract and move minimum amount of data from different data sources before joining them in the DV tool and sending to the BI tools.</p><p><strong>Data Virtualization Tools</strong></p><p>There is a lot of confusion in the market as to what Data Virtualization is. For Data Virtualization as described here, these are tools that companies can evaluate for their needs –</p><p>Commercial Products</p><p>1. <a href="http://denodo.com/">Denodo</a></p><p>2. <a href="https://www.tibco.com/products/data-virtualization">Tibco Data Virtualization</a></p><p>3. <a href="https://datavirtuality.com/">DataVirtuality</a></p><p>4. <a href="https://www.atscale.com/">AtScale</a></p><p>5. <a href="https://azure.microsoft.com/en-us/services/analysis-services/">Azure Analysis Service</a></p><p>Open Source Alternatives</p><p>1. <a href="http://www.dremio.com/">Dremio</a></p><p>2. <a href="http://teiid.io/">Teiid</a></p><p>We personally selected Denodo and we really like it for working with on-premise databases, cloud data sources, merging data from multiple sources as well as presenting a simplified view of the data to multiple BI tools. I will write a more detailed article on Denodo and how it works on a future article.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=85c279502652" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>