Transactional Databases Vs Reporting Databases

Do you think Transactional and Reporting databases/model are identical?

If you are working in a field of Data than database and reporting are a most likely word you hear in your day to day life, but have you ever thought the data which you see in your source or a Data warehouse and the database model created for your reporting both needs to be different.

Now you may be wondering, what is he saying because the Data stored in the form of tables with the existing relationships in the data warehouse will be same used for reporting, but if you see these both are two different systems with each of them having its own efficiency mechanisms.

Just Imagine if you have a Data warehouse with lots of historical data 📁which needs to be on the reporting layer📊 and to bring that in action you use a reporting tool for example Microsoft Power BI.

Lets see why these two types of databases needs to be different!!🙌

Schemas — Now, the way how data stored in the data warehouse is completely different as it should be on the Power Bi model layer, for example if you have a snowflake architecture in your system which is not advisable to be used in Power BI because it is well suited for a star schema model deign ✔ rather than a snowflake hence when you should change the table structure from the source while you are using it on Power BI.

Lookup tables — If you have lot of lookup tables in your transactional database, and which is not efficient to be used in Power BI/reporting database than you can either try merging them into one or less in number or you can get rid of them if they are not important for your reporting needs.

Unwanted Data — If you have many columns in the table of your transactional database which is not required in your reporting database than they should not be pulled in as it will let to increase the size of the file and make the report slow.

Fact Tables — Mainly the fact tables in the transactional database are long and fat which is obvious because that's what data warehouse is for, but while comparing with reporting databases it should be long and thin.

Hence the data in both of the database may be same but they should be designed and used differently as per the use case.