As the need to transfer data and have a sustainable, scallable security model for data warehousing and the need for collaboration between different systems within an organisation or even between organisations grows, we have refined and developed this white paper that outlines how we go about structuring such a data warehouse.
The diagram below shows how technology was used to provide a secure environment to store and process data, finally publishing it (access layer) to the users in a variety of ways.
The “clever stuff” is in the green and red arrows which process data from the staging area to foundation and then to publish it. There are a number of reasons why this challenged our technical heads:
- Data integrity is often a major issue, and both stages offer gates to stop bad data getting through. The methods to determine when data may pass through are quite complex, making use of things like: Max, min, median and average. Row counts. Variable tolerance including the ability to self-learn.
- Security of data is often a big concern (e.g. records of vulnerable patients, certain BOM formulas etc). Some data simply cannot go outside the organisation firewalls. The system described allows secure data to remain within the firewalls of the organisation, and also for inputs to come from outside the firewalls. The Foundation area is then used to store and process data in a secure manner. Essential data which must be accessible outside the firewall can be anonamised too.
- Volume of data is sometimes a significant challenge. The system we developed facilitates heavy lifting to be done by non-access servers, and only the final stage (after crunching complete) copies results to the access layer (into the relevant database).
We know the importance of the design and implementation your data warehouse. Many of the projects that we’ve been given and delivered successfully have required careful attention to the points listed above, so we felt it was important to give some insight into why it’s important to consider.
If you’d like to speak to us more about our approach in designing a successful data warehouse, please get in touch.