Why use NoSQL in a .NET Report?
The majority of data in an enterprise system remains unstructured. NoSQL databases perform better on unstructured information by indexing text natively. Operations like piped aggregation and map-reduce use this feature, which compared to SQL databases are faster and cheaper in terms of CPU cycles.
NoSQL and .NET Reporting
NoSQL database enables your application to retrieve your data model at runtime and does not confine your application in a rigid schema. Because of this independence from schema, you won’t need to create ETL(Extract-Transform-Load) routines when the structure of your report changes. Other advanced features like search alert actions on data ingest, can extract named entities from directories such as those listing people, places, and organizations, which allows text data to be better categorized.
The above scenarios generally apply on Reporting, and having a NoSQL Database would be an added advantage for your reports.
Before we move on to the walkthrough, let's look at DocumentDB and the driver used to connect with it.
DocumentDB ODBC Driver
DocumentDB ODBC driver enables you to connect to C1ReportDesigner or C1FlexReportDesigner with DocumentDB. You can also connect to SQL Server Integration Services with DocumentDB. This driver offers features to help you normalize data in DocumentDB. With this driver, you can represent the data in your DB as tables and views. It also lets you perform SQL operations against tables and views, including group by queries, and other CRUD operations.
Now let’s move on to the walkthrough and how to connect DocumentDB ODBC Driver with DocumentDB and use the connection to bind with FlexReport.
Step 1: Install DocumentDB ODBC driver
- Verify that this driver is installed, by checking the list in Drivers tab in "Open ODBC Data Source Administrator."
Step 2: Connect ODBC with your DocumentDB database
- Open "ODBC Data Source Administrator"
- Click Add. "Create New Data Source" dialog appears.
- Select Microsoft DocumentDB ODBC Driver and click Finish
- Fill the required data in the "DocumentDB ODBC Driver DSN Setup"
- Data Source Name: Your own friendly name for the ODBC DSN. This name is unique to your DocumentDB account, so name it appropriately if you have multiple accounts.
- Description: A brief description of the data source.
- Host: URI for your DocumentDB account. You can retrieve this from the DocumentDB Keys blade in the Azure portal, as shown in the following screenshot
- Access Key: The primary or secondary, read-write or read-only key from the DocumentDB Keys blade in the Azure portal as shown in the following screenshot.
- Click on Test button to test that your setup works. If successful, you'll see this dialog.
- Click OK to add this DSN.
Step 3: Connect your DSN with C1ReportDesigner or C1FlexReportDesigner
- Open C1ReportDesigner or C1FlexReportDesigner application.
- Create new report.
- When report wizard appears, select Data Provider as ODBC Data Provider.
- Click on ... button besides Connection String text box.
- Select the data source from Machine Data Source. Complete the authentication by adding Host and Access Key.
- You report is now connected with DSN.
- Select your database from Data source tab.
- Select your fields that will be included in your report
- Design your report using the fields added in previous step.
Step 4: Generate your report with designer:
Although this walkthrough steps through Microsoft DocumentDB, this is applicable to any other Document Store provided you have its ODBC driver available.