1/6/2018 - How to Import and Analyze MS Access Data

How to Import and Analyze MS Access Data

In this blog post, I will demonstrate the power of Flows computational engine by importing and analyzing a Microsoft Access Database.

Microsoft Access is a standard database format. Using Flow, Access data can be consumed and analyzed with ease. The scenario we will explore will focus on the sample Northwind Database. You can learn more about this database here.

In this example, we will start by learning how to import an Access database into Flow's Generic Data environment with a single action.

After we have imported the data, we will see how to use Flows denormalization functions to reverse the relations of the Access tables and flatten the disconnected data into a single consolidated dataset for analysis.

We will then learn how to use generic expressions to compute new data points dynamically. We will perform various transformations such as computing date and time features to summarize our data better, calculating day distances between different date data points, calculating statistical averages, and evaluating multiplication and subtraction operations against various numeric data points.

We will see an example of how to build simple one-dimensional hypercubes which allow us to aggregate and summarize our data by a single data point of interest.

We will then see how to build a two-dimensional hypercube and learn how this allows us to perform more advanced analytics such as generating multidimensional hierarchical reports.

Finally, we will highlight the full power of Flows denormalization and generic data engine by computing an eight-dimensional hypercube. This final hypercube will dimensionalize data points derived from our original relational tables as well as new data points we calculated using generic expressions.

If you do not have a Flow account - register here. Flow is free for personal use.

The video below provides the full worked example of the Access database analysis. Check out the video here: