Building an Online Data Warehouse Part 1

Google Cloud Platform is a great option for companies looking to build a Data Warehouse on the cloud, with plenty of APIs from which to build.  Currently, the number of APIs which you can choose from to connect your applications to the cloud is growing by the day.  As of now, you have two options: hire a developer to code and integrate on top of those great Google and Applications APIs, or use tools like marvin. to help you automate some of the processes that are required to build a data warehouse.

marvin. is a tool from Bimotics that allows you to set up an online data warehouse without the need to code. Google has created the capability to host the biggest data warehouse in history, Google BigQuery, and marvin. is the bridge you need to make that process as easy as possible.  The steps below will help you get started.

What does it take to set up a Data Warehouse on the cloud? A simplified process for cloud bi would contain the following steps: 

1. Gathering the Data

2. Storing the Data

3. Analyzing the Data

4. Visualizing the Data

Gathering the Data

This first step has historically been the hardest, but advances in on-premise technology and Cloud applications like QuickBooks and SalesForce now provide methods to extract the data through APIs. These are often referred to as connectors. Most of these apps allow users to extract data in formats like CSV or JSON. Even e-commerce platforms allow users to extract their customer, product, order, inventory, and lead information through these connectors. Extracting these files to build a Cloud Data Warehouse is where it all begins. Without access to raw data, it is hard to design and build the right Data Warehouse architecture. Remember the old mantra “garbage in, garbage out”, bad data will always yield bad insight or analysis. Important note before going any further: if your data has exceptions, missing items, or other errors, fix them in the application itself before progressing with your warehouse.

Storing the Data

Storage is another big challenge.  You will need to answer questions such as:

• Where should I store this data and how?
• Is there enough space on my servers and hard drives?
• How much data needs to be stored?
• How often does information need to be added?

After setting marvin. up, pull data from your chosen application (e.g. e-commerce sample). marvin. then allows you to create buckets or folders where these files are to be stored- each relating to specific customers, products, orders, inventory etc.

Organizing data into buckets makes it much easier to refer to later. We suggest you create two types of buckets for each file: one to process into the next step and another for files that have already been processed. For example, we have a file called “Orders_New” and one called “Orders_History.” Not to go into great detail on file names, but naming them as order_YYYYMMDD.csv can help identify when the orders where extracted.

marvin. allows you to store data in any format, but the next step requires that the data be either  in CSV or JSON formats. marvin. will take an additional helpful step in compressing the files into GZIP. This minimizes the storage space used as well as the cost associated with storing the data. Storing your data this way allows you to keep a good archive of your data. marvin. also lets you to upload multiple files or folders into a bucket. Finally, you can preview files, as well as download or delete the ones that you do not need.

In the next part of this series, we will go into the next two steps: analyzing and visualizing data using marvin. 

Google BigQuery is a Big Deal : A Brief History of Database Evolution

As a person so close to, active in, and passionate about the technology industry, I love sharing my knowledge with non-technical crowds from time to time.  It is rewarding and refreshing to simplify some difficult concepts, take a look back at history and gain new perspective.  I recently found myself educating the less-technical side of our team at Bimotics about the incredible power of analytical databases like Google BigQuery. What I realized is that folks can better appreciate BigQuery’s  capabilities by  first understanding how databases have evolved over time.  The following is a short evolutionary history of databases and a summary of why BigQuery really is a big deal!

Writing

Most of the databases since the 1970s have been developed, designed and architected with the main purpose of writing and capturing data. Most of these "writing" databases are based on storing data in rows, as this makes it very simple to gather, append and collect data as quickly as possible. SQL became a very common language to inquire or query the data from these writing-optimized relational database management systems. As the volume of data has grown exponentially over the last 40 years, these writing-optimized databases have started to show their age in terms of analyzing large numbers of records, making analytics on top of these architectures time consuming and slow. Big data has also been focused on collecting everything, further increasing the size of and strain on these write databases.

Reading

Although the challenge of creating analytical databases or databases optimized for “reading” also started in the 1970s, it was not until the 1990s that reading-optimized database adoption  began to grow and mature. Multiple approaches like Cubes and non-SQL languages like MDX that focused on being able to read row based (“write”) databases more quickly, usually required processing or ingestion that took time and introduced latency. These approaches also made it hard for analysts to leverage “read” databases like HP’s Vertica, Cognos, Hyperion or Microsoft. Eventually, “reading” databases like SybaseIQ (1995) were developed and based on storing data in columns rather than  rows. This design optimized the capabilities to query and read information quickly. The next step was to move these analytical databases to the cloud.

Why Google BigQuery is a big deal

It was not until the mid-2000s that cloud databases based on columnar storage with SQL language compatibility became a real solution for analysts. Google BigQuery made it to market by 2010, allowing for unparalleled speed regardless of size. Based on a Dremel whitepaper from 2006, the reading optimized database has unlimited growth and unparallel performance. BigQuery is a big big deal in terms of analytical databases because it allows analysts to query their data very quickly using SQL regardless of the size. You can ingest data very fast, while never stopping to query thus removing the latency introduced by early analytical databases. BigQuery is available as a cloud service that does not require configuration or hardware setup.  It is auto optimized with no indexes to manage. Finally, it is fully secure and capable of complex nested structures to support web data. BigQuery is indeed a big deal and the product of 40 years of database evolution!

The next natural progression for Healthcare analytics

In building out our services practice, we have made a lot of headway consulting for small and medium sized businesses in the healthcare industry. With the 2015 government mandate to put electronic medical records in the cloud and make patient information more accessible to the patients themselves, we at Bimotics have seen healthcare providers exploring cloud based analytics options. Now that these providers have found ways to store records in the cloud, they are looking for cost effective methods to leverage this data for reporting and analysis.

With data already in the cloud, healthcare providers are seeing that capital costs needed for more administrative services can be minimized as spending for technology infrastructure such as hardware and structure can be left to the those providing cloud services. Cloud technology also benefits the patients with greater access to their information--even from different specialists and hospitals. With these increased efficiencies and accessibilities, the tangible evidence of cloud’s benefits is speeding its adoption in the healthcare industry.  

Adoption for healthcare based cloud business intelligence and analytics is not without its challenges and obstacles, however. HIPPA compliance, for example,  requires stringent privacy rules and has very serious repercussions when requirements are compromised. There is also much concern surrounding security and data integrity. Solutions can be engineered to address these concerns and we will go into the details of these technical solutions in upcoming blog installments.

Cloud-based healthcare analytics is definitely coming, and coming sooner than some may think.  Given the mandate to place patient records in the cloud and the associated advances enabling holistic patient care, healthcare providers and patients alike will demand equally cost-effective and accessible reports of the services they give and receive.

Related Blogs

10 Signs Your Business is Ready for Analytics Software

Data analysis findings in insurance fraud

Bimotics 2014 in Review

2014 has been a productive year for Bimotics. While we have put in the long hours, as founders, we feel the work is never done.  Armed with optimism, we look forward to reaching new milestones in 2015.  2014 was full of challenges and successes and we have learned from all of them.   Reflecting on the year that was, here is the recap of our top 3 wins in 2014.

3. Implemented Marketing Automation: Set up flexible inbound and social marketing infrastructure  that the team could maintain and edit.  After evaluating several options, we chose  Hubspot as their inbound marketing and social media focus synergized well with our business model. With our increased marketing activities we were able to fully engage two campaigns which led to consistent growth in site traffic and lead generation.  We have really increased our visibility.  We are now receiving over 600 visitors a month to our website.  We are developing our marketing strategy to effectively hone in on even more channels to spread the word and gain further traction.

2. Became among the first Google Cloud Partners: Last year we became newly minted technical partners with Google Cloud Platform. We then got invited to their pilot certification program. In the first quarter, our technical founder Roberto completed the vigourous process of becoming among the first Google Cloud Certified Partners. With this new status, we have gained additional credibility within technical and investor circles.   More importantly, the relationship allowed us to successfully build a fully HIPAA compliant cloud environment for a client.

1. Launched our big data application,  marvin.:  Without having to code, our app gets your data warehouse on the world’s most powerful cloud in just minutes. marvin. is just the first step in realizing Bimotics’ vision of analytics for all businesses, but we are  on our way! Since launching in the fall we have attracted over 30,000 visitors and gained almost one user per day. We have learned so much about getting to market and we will continue to build on this early success.

With these accomplishments under our belt, we have the much needed stepping stones to keep Bimotics going into 2015. Business planning is well underway for next year and in our next segment we’ll outline some of our New Year’s Resolutions.  Until then, we wish you all a wonderful holiday season and a prosperous New Year.

Related Blogs:

marvin. now available in the Chrome Store

5 things we are looking for in our next employees

The value of HMO analytics

In our last blog, we discussed how cloud-based healthcare analytics is gaining traction as medical records increasingly migrate to the cloud and managers seek new insights into workflow, waste, and administrative processes. This time, we take a further look at some key analytics  that utilization and finance managers in medical clinics and HMOs are leveraging to generate superior reporting and analytical capabilities.  Here are 7 ways business intelligence can enable comprehensive analytical clarity to HMOs.

1. What is my membership per member per month (PMPM), versus cost?

This metric is equivalent to income vs. costs in most businesses. It indicates how well you are managing your cost of services and how well you are positioned to sustain or grow. PMPM can be measured against both income and cost.

2. Optimize the overall HMO organization efficiency

HMOs are based on a capitation payment model where a group of physicians are paid a set amount for each enrolled person assigned to them, per period of time, whether or not that person uses medical services. Tracking and constantly measuring their costs and utilization is key to maintaining positive revenue.

3. Identify departments that are over utilized

By applying PMPM to a department or service, HMOs can compare their budgeted PMPM to their actual expenses. This helps them decide if their services are correctly balanced. Services that are not conducive to providing a fast diagnosis may not fit the capitation payment  model of HMOs.

4. Analyze usage trends to improve performance

Trend analysis of physician, pharmacy, laboratory, community program, hospitalization, and urgent care data allow HMOs to understand patterns and gain insights which in turn allow them to negotiate better rates or modify those patterns. Trend analysis helps managers to proactively  reduce costs and improve their businesses.

5. Monitor network allocation trends to drive customer satisfaction

By analyzing geographic and demographic information, HMOs can  provide a higher quality of services in the most cost effective way. Changing patient demographics can affect HMOs’ contract models, and it is important that they stay abreast of these changes with smart analytics.

6. Special program effectiveness

Special programs are key to HMO services.  For example, preventative programs can reduce inflated costs down the road.  Similarly, education programs can help patients respond to services better. Tracking these programs is key to understanding how they impact the HMO as well as which patients are benefiting the most and which still need to be targeted.

7. Physician, laboratory, pharmacy, radiology high quality of care metrics

Trend analysis can be applied to any department of the HMO. Following best practices that have been developed through careful BI analysis results in better services and increased cost effectiveness. For example, learning that a physician is prescribing all name brand medication vs. generics, realizing that hospital stays are extending beyond protocol, and determining that certain lab tests are not meaningful enough for a correct diagnostic are valuable insights. Monitoring trends help keep organizations knowledgeable as to what works well and what needs to be improved to boost efficiency and eliminate waste.   

Data analysis findings in insurance fraud

It has been just over six months since we began providing on-line analytical services to one of our customers.  Their business focuses on insurance fraud detection, and through the use of Bimotics, exceptional cost-savings have been achieved and valuable lessons have been learned.  This project continues to be a success, with high customer satisfaction and glowing feedback from our customer. We have been able to demonstrate such great benefits in such a short time that considerations are being made to expand our reach into other types of insurance plans and other insurance providers.

For this project, Bimotics built a product for processing, storing and analyzing a huge amount of insurance claims data (millions of rows and data fields), allowing for auditors to find trends, patterns, outliers and further detailed analysis of the individual transactions. Basically, we allowed them to cull through an ocean of data and raise red flags, pinpointing where potential fraud was taking place.  We leveraged our proprietary Data Mart model which allowed us to analyze multiple insurance companies within a single data model.  This significantly smoothed the learning curve for auditors allowing them to focus on the analysis and not on the different data nuances of each company. Finally, our HIPAA compliant system (medical record integrity certification) allowed auditors to securely analyze millions of records within seconds.

The results so far have beat expectations:

• Around $10 million in recognized savings after identifying the impact of claims for procedures that have limited medical value in unique scenarios.
• 1,000s of claims were wrongly adjudicated.
• Data Analysis can be used instead of on-site auditing because of greater efficiency with more results.
• Less than 5% of identified providers in the sample represented more than 80% of the total amount of auditable claims. This analysis allowed our customer to focus on personal audits with the most potential impact, reducing time and high cost audit resources. In this scenario, the audits were performed on less than 1% of the total providers list and maximized results.

Lessons learned from this Customer:

• The software-as-service business model made this project possible because capital investments were unnecessary, start up costs were low, and the customer could hit the ground running. The SaaS model plus consulting services allows business to start within days without the need for high initial cost and IT headaches.
• Automating the data cleansing process after identifying each new need had the greatest impact on decreasing turnaround time. Millions of records were imported, standardized and analyze within minutes, not hours or days.
• Collaboration is key, especially when the team of analysts are global or not in the same location. Secure web dashboards allow us to efficiently share and consistently communicate our findings.

Bimotics – What’s In a Name?

On a recent trip to Silicon Valley, we got to have an interesting conversation with a so-called “cloud evangelist”, a charismatic character who preached the good word of all things tech and cloud solutions in particular. During the course of our discussion, he was interested in how we became so passionate about building Bimotics and how our unusual name came to be.  After hearing our answer, he encouraged us to share it with our customers.

Our Name

Bimotics stands for business intelligence automation. We got the name by taking the abbreviation for business intelligence, “B.I.”, and merging it with the word “domotics” which is the word for home automation.  We chose this name because it embodies what we set out to achieve -- automating the  processes required to bring fresh, easily identifiable, and actionable insights to companies. Efficiencies gained from this automation allow Bimotics to provide powerful yet affordable solutions to small and medium sized businesses. The same principle applies to our developer tools which automate the steps needed to build big data solutions.

Our Logo

The concept behind our logo is pretty cool, if not slightly nerdy.  As an analytics company, we wanted to show something of a bar chart, incorporating colors often used to denote visualizations like green, yellow, and red.  At the same time, we wanted to capture the heart of Bimotics analytics by depicting various data sources, represented by colored boxes, knit together. That’s our logo on the surface. 

Looking under the hood however, reveals our inner geekiness.  Embedded in the logo is the binary code for “B.I.”. The yellow box is in the 2nd position representing “B” and the red and green are in 1st and 8th position which stands for “I” (the 9th letter in the alphabet).  

We are a technology company through and through, and it is reflected in our name, our icon, and everything we do!