NosDB Data in Microsoft Excel: Simplified Data Analytics

The Challenge

Gathering and storing your organization’s data is important to be sure. But presenting volumes of data in a useable format for business analytics is increasingly critical. To store such ever increasing and enormous amount of data companies are incorporating distributed databases. But to compile and analyze the data from distributed databases to third party softwares like PowerBI is a cumbersome problem because it requires you to write custom .NET applications to compile the data in a consumable format.

The Cure

Happily, third party software is not always required.  There are fault tolerant distributed databases that also provide data in ‘analytics-ready’ formats. NosDB is one such NoSQL database, and it is a native .NET product. Like any NoSQL database worth its salt, it is a scalable, fast, distributed database for BigData. In addition, it provides data to Microsoft standard products Excel (for data analysis) and PowerBI (for data visualization) in a consumable format which super-simplifies data analytics.

Connecting NosDB to MS Excel

And here is how it works. You can link NosDB to these Microsoft products by using NosDB’s REST API. REST API uses Open Data Protocol (OData) which allows applications to seamlessly connect to NosDB via an OData URL feed without any code changes. All you need to do is configure the REST API environment by following simple steps given in Configuring NosDB REST API.  Once REST API is configured, you can connect it to Power BI and Excel for enhanced visualization and analysis of data respectively. In this post, I will explain in detail how you can access NosDB data in MS Excel. For details on how to use NosDB with Power BI, please see Using Microsoft Power BI with NosDB.

Here I will use a Northwind database (shipped with NosDB) as the demo database. It is found in the form of JSON files in %NosDBInstallDirectory%->Samples->data->json->Northwind along with the PowerShell script to import the data. You can refer to Import Data in NosDB to see how to properly execute the NosDB PowerShell script.

In order to link an OData feed to MS Excel, Microsoft’s Power Query module is used, which helps you fetch data from NosDB to be displayed into the Excel workbooks. In MS Office versions 2010 SP1 and above, the Power Query module is available as an add-in and can be downloaded from Microsoft’s website. From the 2016 version onwards, Microsoft delivers this module as a built in feature meaning you need not install the add-in. You can find the Power Query module under the Data tab in the Get & Transform section.

PowerQUery

Power Query is used to query data from other data sources, consequently connecting the data source to MS Excel. You can connect to NosDB from MS Excel by making a new power query on the NosDB data source. It is done by going to Data tab-> New Query->From Other Sources -> From OData Feed

OData

The following screen appears that prompts for an OData feed URL. Here I will give the REST API URL that I had configured, and use it to connect to a NosDB database named Northwind.

Feed

Excel will try to connect to this specific address and, if found, will display all the collections in the database in a “Navigator” window. Here you can select one or more collections to be added to your worksheet and filter data according to your needs before it is loaded.

Filter

Data Filtration Before Loading into Worksheets

Before your NosDB data can be imported to Excel sheets, power query offers multiple filtering techniques and features that can be applied to manipulate and/or extract the data specific to your analysis needs. With the help of the “Edit” button on your Navigator window, you can easily go to the QueryEditor window where you can perform filtering on the data to be loaded. For example if you want to choose specific columns or merge different queries, Excel provides you with all the options.

Merging NosDB Collections Using Power Query

MS Excel aids in combining and bringing your data at one location. NosDB will offer the feature of joins and sub-queries in its upcoming versions but for now Microsoft’s Power Query can be used to gather and join different NosDB collections in one place. It is possible to import not only textual data but images and other object specific data as well. As NosDB lets you store media files separately in the form of attachments, the preceding feature of MS Excel to load different type of data can be very useful. NosDB offers separate collections for your multimedia attachments where you can store and fetch these records easily just like any textual data. Find more about how to work with attachments in NosDB Attachment API.

Once you have applied your desired filtering operations on the data in the QueryEditor, click the Close & Load button to continue loading the data to the worksheet. I have selected the Products collection with Supplier and new columns have been added in my existing collection Products. I created this join based on the SupplierID to be used as a foreign key in Products.

Merge

After the data is successfully imported into MS Excel, now comes the part where you can analyze your data. MS Excel enhances your ability to organize and structure your data and perform powerful analysis on large amounts of NosDB records. There are multiple features offered by MS Excel that can be advantageous to your business analysis which can be summarized into two major sections:

  • Statistical Analysis of Data
  • Visual Representation of Data

Statistical Analysis of Data

NosDB data can be analyzed with MS Excel, as it offers a vast variety of data transformation options. For example, it allows you to perform calculations on your data very easily. You can perform mathematical and statistical operations on the data, replacing values of columns, removing duplicates and adding data from different sources into one.and much more. The Formula tab helps you perform business analysis operations from basic math – like addition and averaging the data against some criteria – to financial and logical operations on your data. MS Excel can also find relevant patterns in business information and use them to identify key-trends, facilitating predictions.

Statistics

Visual Representation of Data

MS Excel can also be used to represent this analyzed data in visual form, via graphs and charts, that you can easily create. You can use MS Excel to build reports and graphical presentation of your summarized data. With the aid of a variety of charts, graphs and clustered columns, your data is not just numbers but can emphasize trends and predict outcomes for alternative action plans.

For example, a business owner wants to open a manufacturing facility in the best possible area with the lowest shipping cost for raw materials of his products. For this he can create the following histogram which displays the location of suppliers against their count within one area. This makes it easy to analyze one of the factors in considering the location of the facility. This analysis can be more beneficial when combined with other factors as well. MS Excel auto calculates the count of countries from the original data, as shown in the screenshot below. To the right of the picture you can see Pivot Table options where you can dynamically filter and segment data to display only what is essential for your analysis.

Representation

 

Another presentation of information in visual form is shown below. The graph shows how many units of a product are in stock, which is beneficial for the owner of a retail store to monitor and manage his inventory.

Graphs

From above we can conclude that the combined use of NosDB and MS Excel for analyses and business intelligence offers you a most compelling view of your data. With the help of advanced filters and searches, you can spend more time analyzing your data, rather than sifting through it to extract the useful bits of information.

Leave a Reply

Your email address will not be published. Required fields are marked *