2

The Primary goal is the case-optimized data representation. The idea is to gather data from several databases, save the data in a data-warehouse and restructure/connect the data in data marts(similar to a view across multiple databases). Basically the intention is to make it easier to access the data that is spread across databases.

Data Marts

As you can see in the picture the plan is to have dependent data marts(shown in the upper half of the picture) to avoid having multiple Clean and Load processes like independet data marts have (shown in the bottom of the picture). The BI aspect of the data-warehouse will also be used but is not relevant for my question. Only the data in the DB will be changed. The data represented in the DW is just for reading purposes. So the basic concept is not hurt. But I still feel like the DW is misused here.

To anwser my question please include following points:

  • Is the DW misused?
  • Are there any DW's with dataconnectors that fit (near) real-time capabilities?
  • What alternatives fit these requirements to connect data from multiple DB's and represent them in a clean and structured way in real-time without having tons of Clean and Load processes?
0

1 Answer 1

0

The first (top) example is essentially the most common ways to design the ETL process for a BI datawarehouse and datamarts. You don't want the lower example, as you'll need to build, deploy/version and troubleshoot multiple instances of the same code.

As for "misuse" or not, it all comes down to how you design the datamart. Typically, star schemas are designed for analytics, but there's nothing to say it won't work for OLTP-type work. I would consider the amount of work required to remodel the data when loading - is it worth the extra complexity, or would you be better off querying the source database directly from your app? Maybe a database view in the source database(s) could do the same, with a lot less complexity?

Be really careful with "real-time" BI implementations: The more "real-time" you build the solution, the more complex it inevitably gets.

On the one hand, a process that does a truncate-and-load every night is relatively simple to build and run.

And incremental loads that run every hour, and you need to start identifying new, changed and perhaps even deleted (!) rows in the source data. This may be hard or even impossible, depending on how the source data is structured or shipped to you.

A full-on "real-time" solution (within quotes, because unless you have just views on the source database, it's not really realtime) will probably need some kind of change capture, trigger or similar complex logic for every dependent table. All of those things can potentially affect the source databases adversely with regards to performance and stability.

It gets even more complex and costly when those tables interact, say for example, if you need to join snowflake schemas into a star schema.

To answer your third question, pretty much any ETL tool (SSIS, Pentaho, etc) will support basic datawarehouse ETL. Once you add near-realtime logic, the choice of platform ETL tool becomes less relevant (you'll probably need to script most of this yourself anyway), and the solution becomes very dependent on the database platforms of the source data as well the infrastructure and platform of your datawarehouse.

7
  • Thanks for your answer! I got most of the aspects but i couldn't figure out whether its a misuse of a DW to just use the ETL Tools and stuff to generate better views on the raw data and read it. Lets say I have 3 DBs and 3 Apps and I use the DW to collect all the data and create data marts so App1 can read data from a data mart that contains data from DB1 & DB2. No BI No analysing. Let's leave the realtime aspect out. Is it a misuse?
    – Yoshi
    Jul 12, 2018 at 11:57
  • A BI solution is just that - a solution to allow you to analyze/report on multiple data sources. Not sure what the "misuse" would be about. Jul 12, 2018 at 11:59
  • Maybe I'm confusing this somehow. So I would use my warehouse just for rearrange the data, not using any BI tools, not wanting to analyse anything just to do the same stuff I would do on my regular DB? and this is still BI somehow and no misuse?
    – Yoshi
    Jul 12, 2018 at 12:11
  • What would you use it for? Jul 12, 2018 at 12:12
  • My app that is usually working on one of the DBs doing the same stuff as it always does not analyzing not doing BI just App stuff could also work on the Datamart of the DW? Using the Datamart just as a view for the pure data and not for BI queries?
    – Yoshi
    Jul 12, 2018 at 12:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.