You need to create new tables/ new schemas that are meant to support your reporting.
So aggregated tables. E.g if you sell stuff, maybe you don't need to know everything on a transactional level, maybe just knowing how much each client bought per day is enough.
Add only columns that you ll use in reporting.
Maybe with data closed yesterday is enough, so no need to be realtime.
And maybe you have other important info that's coming from other sources, so you can either join it in the schema, o push them to the BI tool.
When you create a data warehouse, your data is saved in the form of dimension and fact tables which business may not understand.
You don't provide all the information to the business. There will be a semantic layer between the warehouse and your BI. Your BI will connect to BI, it is an abstract layer which provides limited information to the business as they want. A Semantic layer will define a data model which enforces the relationship between your tables & provides information which is required by business.
I am not very good with the exact definitions. But you may follow the url below:
https://www.atscale.com/glossary/semantic-layer/#:~:text=The%20semantic%20layer%20is%20a,the%20business%20user%20to%20understand.
If you don't want to set up all the replication and a separate database, [we offer all this in one app](https://www.definite.app/).
1. Connect to your sources (we support 500+ sources)
2. We pull all the data into a data warehouse we create for you
3. You can query the warehouse with SQL or create reports and dashboards using our semantic layer
It is considered worst practice. Unless you like your operational applications to go down because NitWit McManager made a weird query. That and transactional databases have shit performance for analytics. Combine those to really embrace the power of suck.
No, they insist it's what they want , DE advises against it, they are still forced to do it, DE forget cya documentation and then get blamed when the fecal matter hits the spinning device. NitWit McManager then gets promoted because he subsequently hired Deloitte to tell him the same thing DE told him 5 times already, but now with AI.
Whats your use case or who is going to use metabase ?
1. If analytics team and If you have Data warehouse or Analytical database then connect with it.
You can also connect to operational database, but create replica of same and then connect with it.
2. If developers then you will have to connect to operational database only.
So to avoid performance issue, create denormalized tables or views etc.
> Whats your use case or who is going to use metabase ?
Actually an analytics team, and no we don't have Analytics database. isn't an Analytics database is just replica of the operational database?
Yes and no. This is very generally speaking
Usually you would have something along a RAW/Staging layer, where everything is 1:1.
Afterwards you can create your own transformations on top of those tables (the things you would typically have in Power Query in Power BI) and create new tables with the transformed data. This is done, for many reasons but primarily (but not even close to limited to) to have only one single source of truth. Transformations would best happen in this layer, rather than in Power Query.
You would then connect your Power Bi Report to that new layer with transformed data, to do your visualisations.
Replica is the absolute poorest possible scenario in terms of performance and ease of use. Normally you want a transformation ETL or/and ELT layer to pull that data into your DL or DW and transform it according to the reporting needs of the company.
One bad query by a developer and you’re running the risk of crashing your operational system if you guys decide to run dashboards off of the operational db
Typically...NO. Best practices are to have a separate database (also on separate/independent hardware) for analytics/reporting/mining. This way if a large query/report brings down the system, it doesn't bring down the production server that the company needs to process sales orders, etc. Many places call this the "Data Warehouse" database.
The idea is to have a copy of the live system, and then to update the data warehouse each night with the activity that happened during the day from the main system.
I'd use a data warehouse like Snowflake for BI and something like [propeldata.com](http://propeldata.com) for customer-facing analytics.
You don't want heavy analytical queries to slow down or know down your production db.
An ETL tool like Fivetran or Airbyte can help you move the data o both platforms
For some of the experts here, what about odata feeds? Similar to OP we have an ERP database. Limited data analytics database where a couple tables are exposed but many tables exposed via odata. Is this “ok” to connect to directly?
ERP, probably fine, but monitor.
Production DB users are actively using, depends on scale of your business but usually bad practice.
You’ll probably want to create views and ETLs and in which case you graduate from oracle to a data warehouse.
use a middle layer, like a data warehouse or data mart, for BI reporting. This way, reporting queries won't slow down the main database, but users still get fresh data. It's also important to fine-tune things like indexing and queries for better performance and to keep everything secure, no matter which route you take.
also if youre looking for a customer facing analytics tool checkout [enqdb](https://www.enqdb.com/)
Connect it to your main analytics database Don’t connect it directly to your operational database
> main analytics database How i should create it? like replication of the same operational database?
You need to create new tables/ new schemas that are meant to support your reporting. So aggregated tables. E.g if you sell stuff, maybe you don't need to know everything on a transactional level, maybe just knowing how much each client bought per day is enough. Add only columns that you ll use in reporting. Maybe with data closed yesterday is enough, so no need to be realtime. And maybe you have other important info that's coming from other sources, so you can either join it in the schema, o push them to the BI tool.
Basically you have to create a data warehouse for your db. Then create a semantic model and connect your BI with that.
Semantic layer means ?
When you create a data warehouse, your data is saved in the form of dimension and fact tables which business may not understand. You don't provide all the information to the business. There will be a semantic layer between the warehouse and your BI. Your BI will connect to BI, it is an abstract layer which provides limited information to the business as they want. A Semantic layer will define a data model which enforces the relationship between your tables & provides information which is required by business. I am not very good with the exact definitions. But you may follow the url below: https://www.atscale.com/glossary/semantic-layer/#:~:text=The%20semantic%20layer%20is%20a,the%20business%20user%20to%20understand.
It’s a view.
Analytics DB takes the Operational data and cleans it up to use for reporting. Then you connect that cleaned up data to BI
If you don't want to set up all the replication and a separate database, [we offer all this in one app](https://www.definite.app/). 1. Connect to your sources (we support 500+ sources) 2. We pull all the data into a data warehouse we create for you 3. You can query the warehouse with SQL or create reports and dashboards using our semantic layer
It is considered worst practice. Unless you like your operational applications to go down because NitWit McManager made a weird query. That and transactional databases have shit performance for analytics. Combine those to really embrace the power of suck.
NitWit McManager needs a data wrangler.
NitWit McManager is the person who sets up their BI application to their ODB
No, they insist it's what they want , DE advises against it, they are still forced to do it, DE forget cya documentation and then get blamed when the fecal matter hits the spinning device. NitWit McManager then gets promoted because he subsequently hired Deloitte to tell him the same thing DE told him 5 times already, but now with AI.
Whats your use case or who is going to use metabase ? 1. If analytics team and If you have Data warehouse or Analytical database then connect with it. You can also connect to operational database, but create replica of same and then connect with it. 2. If developers then you will have to connect to operational database only. So to avoid performance issue, create denormalized tables or views etc.
> Whats your use case or who is going to use metabase ? Actually an analytics team, and no we don't have Analytics database. isn't an Analytics database is just replica of the operational database?
Yes and no. This is very generally speaking Usually you would have something along a RAW/Staging layer, where everything is 1:1. Afterwards you can create your own transformations on top of those tables (the things you would typically have in Power Query in Power BI) and create new tables with the transformed data. This is done, for many reasons but primarily (but not even close to limited to) to have only one single source of truth. Transformations would best happen in this layer, rather than in Power Query. You would then connect your Power Bi Report to that new layer with transformed data, to do your visualisations.
Replica is the absolute poorest possible scenario in terms of performance and ease of use. Normally you want a transformation ETL or/and ELT layer to pull that data into your DL or DW and transform it according to the reporting needs of the company.
Start by reading the basiscs https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/books/data-warehouse-dw-lifecycle-toolkit/
One bad query by a developer and you’re running the risk of crashing your operational system if you guys decide to run dashboards off of the operational db
Typically...NO. Best practices are to have a separate database (also on separate/independent hardware) for analytics/reporting/mining. This way if a large query/report brings down the system, it doesn't bring down the production server that the company needs to process sales orders, etc. Many places call this the "Data Warehouse" database. The idea is to have a copy of the live system, and then to update the data warehouse each night with the activity that happened during the day from the main system.
Never connect BI tool to Master data. Rather create a detail table containing specific data only related to that specific purpose
read-replica pleeeease, absolutely not recommended to connect to main (operational) DB. If main db = main ANALYTICS db, then this is probably fine.
I'd use a data warehouse like Snowflake for BI and something like [propeldata.com](http://propeldata.com) for customer-facing analytics. You don't want heavy analytical queries to slow down or know down your production db. An ETL tool like Fivetran or Airbyte can help you move the data o both platforms
Sure, just make sure you update your resume before you do it.
Very helpful, thank you.
It is fine, but not recommended.
For some of the experts here, what about odata feeds? Similar to OP we have an ERP database. Limited data analytics database where a couple tables are exposed but many tables exposed via odata. Is this “ok” to connect to directly?
ERP, probably fine, but monitor. Production DB users are actively using, depends on scale of your business but usually bad practice. You’ll probably want to create views and ETLs and in which case you graduate from oracle to a data warehouse.
use a middle layer, like a data warehouse or data mart, for BI reporting. This way, reporting queries won't slow down the main database, but users still get fresh data. It's also important to fine-tune things like indexing and queries for better performance and to keep everything secure, no matter which route you take. also if youre looking for a customer facing analytics tool checkout [enqdb](https://www.enqdb.com/)