What are the best practices for expense report data warehousing?

If you’re aiming for a data warehouse, you usually want this to be designed and architected to address a wide variety of use cases and hence business outcomes. It’s critical to identify a number of potential use cases as part of the strategy phase, before starting to build anything, and then prioritise in terms of impact, cost, value and urgency. These should be discussed with stakeholders across the business to get a good alignment with the expense challenges the business faces. As well as the scope, you should consider whether you are going to buy a solution which addresses your needs, whether to build internally or, if you don’t have the necessary people available to outsource build and operate.

Include letting desired business outcomes dictate data needs, getting as close to the data source as possible, consolidating sources, setting and managing data quality expectations, catching issues early in the data journey, measuring and acting on data quality issues, implementing change management controls, allowing for data collaboration in a controlled way, communicating with data providers to ensure data meets needs, surveying required data to remove manual sources, focusing on important data sources, and leading data sourcing based on audience focus.?These practices emphasize the importance of accurate, clean, and protected data to support efficient business operations and decision-making.

I’d recommend creating a high level data model before getting into data sources, based on the priority use cases and the overall structure of your source to pay process. This way you can choose the most trusted source for each data entity and subtype. In terms of sources, your ERP is going to be the core, and will have purchase orders, supplier invoices etc, as well as some product, material, location, cost centre information. What it might not have is all the hierarchies and classifications that usually would come from a master or reference data management system, and it may not have the approval process info etc. Indirect procurement may be in something like coupa. You should work out where there are potential duplicates between systems

Your core data model is going to be based around materials/products, purchase orders, supplier invoices, payments, approvals, employees and suppliers. This is going to be enriched with master and reference data which includes hierarchies, classifications and similar. If you have one ERP and one source for each data then you can don’t have such of an issue with integration, meaning you can reasonably confidently go land/stage/dimensional model where there is a star schema for each of the key processes (PO, invoice etc). If you have multiple sources then you need to think much harder on integration and datavault is a suitable choice for the integrated data layer, then you can build dimensional models or outbound batch interfaces off this .

Either you are going to build or buy your data warehouse. If you’re building yourself, then you have to choose everything, though if you have an existing warehouse this becomes simpler as ideally you choose something that already exists in your organisation. If you have to choose new then likely you’ll go cloud and the leaders are snowflake and bigquery. If you’re going to buy, then your supplier will usually have a preferred platform of integration /ELT/ETL software and database etc. You’ll need to map sources, tables and fields. You’ll need to work with IT to ensure connectivity between your systems and the ETL/ELT esp. around firewalls if you are going cloud. Testing is crucial, and should be done with every run not just build.

if you only wanna do expense reporting and think about building a data warehouse i would suggest to ask the following question to yourself: if you wanna go shopping in the neighborhood, do you buy a new mercedes to drive two minutes or do you buy a small shopping cart and walk. i would not spend a fortune and walk instead of cracking a walnut with a sledge hammer.