Reporting and Analytics

INFO-Subscription provides and out-of-the-box database which can be used for reporting, visualization and analytics of your subscription data and economy.

The reporting and analytics database is currently available through direct SQL and indirectly via our hosted Power BI solution.

Many of the data points and visuals available in the Merchant application, as well as all the reports, are based on the data exposed by this reporting database.

Built for reporting, the solution is optimized for read-access and server-based aggregations. Use it to answer quantitative questions about the past or present state of your subscription business.

For details on what type of data is available refer to the datamodel section or explore the model with your favorite MS SQL capable tool.

General Concepts

There are a few concepts that might be relevant to understand about the reporting system.

1. Near real-time

The model is updated in near realtime, mostly depending on the available resources. It is NOT offloaded once a day or once a week or something like that. Data is processed as it is generated in production and reflect into tables based on the stream of events generated by the production platform.

For many reporting and analytics applications this is not terribly important, a delay of 1 day is not a problem. However for it allows for building “read-only” applications on top of the data layer with near-instant access to the production data, in fact a few of the lookup/search features of the Merchant application is built like this.

2. Semi denormalized

It is common knowledge that to be efficient with storage, databases should be normalized.

However when it comes to reporting and analytics, there are a few issues with that approach, the main one being needless joins between tables where you need the data again and again.

To alleviate that issue, some of the tables are built so that common information is denormalized. The two most prominent examples are:

1. Subscribers

2. Organizations

In many places a reference to a SubscriberId is replaced with the SubscriberId, the SubscriberNumber and the primary contact details. Similarly the OrganizationId is replaced with the OrganizationName, Address etc.

The reasoning being that if you want to build a report of all payments for reconciliation or auditing, it is very likely you want to know the subscriber as well. Having it pre-built makes for faster read access at the cost of a bit of storage.

All things are not equal however, so not everything is denormalized. Reach out to support if you think something should be denormalized.

3. Pre-Aggregated KPIs

Some KPIs are so common, that it would be a waste of time for everyone to retrieve thousands and millions of rows just to summarize them.

INFO-Subscription provides its own set of KPIs that are aggregated during generation (in realtime).

The enables faster generation of visuals in reports and dashboards.

As with the normalized data, there might be something that you think we should include, then feel free to reach out to support, we may be able to acommodate you.

4. Area or Silos of data

The reporting database is divided into areas or silos with different data being put into different areas.

The silos exists as a grouping mechanism, and some data may be duplicated between silos. Mostly they are there to remind consumers that not all concepts map 1:1 and that items you think relate to a specific thing may actually have multiple sources or be connected in a different way than what you expect.

Keep that in mind when you correlate data between silos.

Connecting

At the current time of writing, direct SQL based access is granted to the reporting database upon request. There may come a time where a more automated approach will be considered, but for now it requires some dialog with the consumer and support.

Please do not hesitate to contact support and request access.

Hosted Connections with Power BI

An alternative approach to directly connecting to the database, is to build a report/visual/dashboard using Power BI, and upload it into INFO-Subscription for processing.

However to do that, Power BI needs a model, which is most easily obtained by a direct SQL connection.

For now that means that to enable Power BI building, a regular SQL connection should be established first. Refer to the above section for the details.