Databases, Data Warehouses, Web Development and Business Intelligence – How to Create an IT System

Database and Web development these days become more and more affordable for everybody. With the technologies approaching people without IT background from almost everywhere, it becomes easy to build systems of every scale from small to complex.

Database (DB) is the core of each IT system. It started from using Hierarchical and Network model based databases and went through inventing and enhancing well-known relational database model. The main property of the mentioned databases is storing structural data. Later, many new features had been added such as: storing binary and XML data. For storing big data, NoSQL DB (like Mongo DB) and Distributed Files Systems have been suggested. They mostly target storing unstructured data.

Object-relational databases such as Oracle, DB2 and MS SQL are used widely during these days. Quick in retrieving of structured data and able to perform relational operations these data bases are very efficient for manipulating with granular data such as banking transactions for example. Oracle, apart from having declarative SQL language and procedural PL/SQL language for working with relational data, has many extensions like Data Mining (ODM), Oracle Golden Gate, Oracle OLAP, Oracle XML DB, Oracle Warehouse Builder and Partitioning. The databases can be used to build an IT system capable of serving a complex, city wide system with quite intensive data usage. Speaking about ‘data movements’ in and out of the system, there are many options for this either moving/integrating data synchronously or asynchronously.

Depending on scale of an IT system, appropriate databases should be used. The very famous and quite reliable databases are MySQL and MS Access. MySQL database is widely used as a database for Web systems whereas MS Access is used for desktop ones. However, MS Access database could be used as a Web database provided it has been developed appropriately and when deployed hosted in SharePoint environment with Access service is up and running.

We have covered databases, but how a system can be built without a front-end? A front-end is the ‘interface’ a user interacts with. Two tier IT systems have become the past and three and more tiers become more and more popular. ‘Tier’ in IT language means separation of functions in an IT system, e.g. Presentation tier is responsible for interacting with a user, Logic or Application tier is responsible for performing actual software code and Data tier is the layer where data is stored.

Web interface is a very good candidate for a quality ‘front-end’. There are many options on how Web interface could be developed. ASP.Net from Microsoft is a very good technology for development a front-end. An interface could be built by using HTML, CSS and PHP, for example. Oracle Forms is a Web based platform for developing a good front-end. Speaking about small scale solutions, Access 2010 has the ability to publish a Web database in SharePoint using SharePoint Access Services and, in that way, the database will be accessed from the Web.

So, now we have created the data store, put our data there, now what? Of, course, we can use it as we had planned, but apart from it we can gain additional insight into our data by using Business Intelligence or BI. The BI is software which helps management analyse a current situation, by producing reports and possibly predict the future. Overall, the BI is the ability to understand relationships between facts. Besides the reporting ability, the BI includes: Online Analytical Processing (OLAP) which answers to multidimensional queries very quickly and Data Mining which discovers relationships between data sets.

How to tie it all together if, for example, we need to create an IT system which would suit our requirements? Business, System Analysis and Project Management are the answers. First of all, we need to understand business requirements that are what is required to deliver value to your business. Second, we need to understand user and solution requirements and verify if they are aligned to the business requirements. Next, design phase should follow where design is being built. After the design is done, transition requirements are produced along with the estimation of readiness of an organisational unit to take the developed solution on board. Final step could be developing the solution and deploying it along with performing necessary tests.

We should remember that the above described approach is ‘general’ in nature and should be carefully crafted to real life. Another aspect of crafting it is that a decision should be taken either sequential or iterative approach will be used. In sequential approach, all types of requirements are developed upfront. This is done on projects where formality is required or the project has a high risk profile or if the desired solution or its components have been developed in the past. Iterative approach is used when low formality is acceptable, or when the desired solution is being developed for the first time, or part of the solution should be delivered in a short amount of time.

The sentences above only ‘scratched the surface’ in IT area but the main trend is clear that Web and Database development started as a complex area but now anyone, who can invest time to get used to existing IT software development environments, might deliver a good solution for benefiting their business.