Member Avatar for ikel

Hi,

I am working in a company that utilises many systems with their own databases. For example, the cafe has its own CAFE system with its own database (it could be postgresql). The Health Care system has its own database system (it could be MS SQL). The Transport booking has its own database system. And another system uses No-SQL database.
I am faced with a task of producing web-based management reports to allow managers to see key performance indicators on screen.

What would be the best way of accomplishing this?
I would perform cross database queries of the key performance indicators of the company. But I feel that there are things that I haven't taken into an account.

Has anyone done something similar and able to give me suggestions?

  • 3 Contributors
  • 4 Replies
  • 348 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by ikel

Dani AI

Generated

A concise, practical plan that complements the thread: start by defining the handful of KPIs that matter, map each KPI to its source fields, then build a small, curated reporting layer (a reporting schema or data mart) rather than trying ad‑hoc cross‑database queries across dozens or hundreds of tables. This reduces load on production systems, makes business rules explicit, and keeps dashboard queries fast.

Key steps to follow:

  1. Inventory sources and ownership, and record the exact columns and rules that feed each KPI.
  2. Stage raw extracts (with extract timestamp, row counts and checksums) so every load is auditable.
  3. Transform into a small canonical model (facts + dimensions) and create materialized aggregations for each KPI.
  4. Choose load cadence (nightly batch vs CDC/streaming) driven by business needs.
  5. Expose the reporting layer to BI via views/APIs and add automated validation and lineage logs.

Common pitfalls and mitigations:

  • Mismatched identifiers across systems: use mapping tables or surrogate keys and capture the source id for traceability.
  • Conflicting business rules: codify rules in the ETL/transform layer and keep versioned documentation.
  • Late-arriving or corrected transactions: implement a backfill/reconciliation process and keep change timestamps.
  • Schema drift: include schema checks in the extract job and fail fast with alerts.
  • Sensitive data: enforce least privilege, mask PII in the reporting store, and log access.

Practical rollout: prototype with 2–3 KPIs and two high‑value sources first, iterate on mappings and refresh rates, then expand. This builds on ’s consolidated‑store concept and the BI tooling idea raised by , while addressing ’s concern about wading through hundreds of tables by forcing a KPI‑driven, curated approach.

Recommended Answers

All 4 Replies

Member Avatar for rch1231

My suggestion is to set up a server with a database you are familiar with and then regularly export the data from the various databases and import it to your master system. I have done something like this and I normally use PERL to generate the export files (normally a CSV since every db will create a CVS file) and then import them into a MySQL database. Once you have all of the data in one place then you can start generating reports. This way your reports will have a similar format and you only deal with coding for one database and can use some of the same code. Another advantage is that you are not dealing with the actual data in their database and don't have to worry about accidentally changing something.
Hope that helps.

Member Avatar for ikel

,

Thanks for sharing your approach. It sounds like setting up a data warehouse, but simpler. *"You are not dealing with the actual data in their databases and don't have to worry about accidentally changing something." *Well, yes!

Member Avatar for pritaeas

This was something demonstrated to me by a coworker last year. Have to say that it was pretty easy to work with, although importing your data is a bit more work.

Member Avatar for ikel

,

I had a look at Qlik. It says that it does not require a DW as it aggregates from various dbs. I'll have a look in more detail later. (Although it may be a bit difficult to aggregate in the first place as you will be dealing with 100s of tables that you don't want to see, which was a necessary steps prior generating reports from certain tables only).

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.