SQL CREATE DATABASE STATEMENT

A BI system is designed with the premise that the data remains fairly static with infrequent updates. These updates could be nightly, weekly, monthly, or any other time variant as long as updates aren’t happening on a frequent basis, like in an OLTP system. As the name “Business Intelligence” suggests, in this system a large amount of the processing involves analysis of existing data to provide intelligent, analytical, and informative data to the business. There should be little or no updating of that data—ostensibly only when the data within the analysis is found to be incorrect or, as mentioned previously when more data are being applied for analysis. Backing up the data will probably take place only as a “final action,” after the database has had changes applied to it. There is no need to make it a regular occurrence. Many BI systems can also be refreshed from empty from the host data source. BI systems tend to have two databases involved in their overall architecture. The first database is a normal SQL Server database in which you would store the data to be used to build your BI database. This could be your main database, although you would need to take care with the processing time and resources that can be taken up when the BI database extracts that data. A BI database has a completely different database infrastructure and objects compared to OLTP, and what you are going to learn in this book and does not have tables and indexes. Instead, it uses the data loaded from the SQL Server database and stores the data in files on the servers where the data has been aggregated with measures and dimensions. BI databases require a totally different mindset and use different SQL called MDX to extract the data. Systems designed for BI sometimes do not follow any design standards or normalization techniques. You tend to see no normalization in an OLAP system, as it is easier to take data and to slice and dice it without having to bring in data from a normalized table.

SQL CREATE DATABASE STATEMENT

Example System Choice

When you take into consideration all the information presented in the preceding sections, it becomes fairly obvious that although the data updates will be relatively infrequent in the example system (in other words, only when a financial transaction occurs or a statement is generated), there will be updates occurring online with instant results expected. Therefore, the system you will be building will be an OLTP system.

Gathering the Data

Among the first things you need to do before building a database is to find out what information the database system has to hold and then decide how that information should be stored (for example, numerical or text, length, and so on). To achieve this goal, you’ll perform a data-gathering exercise, which could involve talking with those people who are the owners of the system and those who will be using the system. For larger systems, you would hold several meetings, each of which would pinpoint one area of the system to discuss and research. Even then, several meetings may be spent going back and discussing each area. You could also conduct interviews, distribute questionnaires, or even just observe any existing processes in action, all in an effort to gather as much information as possible about the database and how it will be used. The key indicator of whether a database solution is successful is not found so much in the building of the system, but rather in the information-gathering process before the first line of code is actually written. If you’re working off an incorrect piece of information, or you’re missing an element that might be crucial to the final solution, already the system is flawed. Involving the users as much as possible at the earliest stage and then including them as the design progresses should result in a significant reduction of errors and missing parts in the final product. For the example financial program, the first people you would want to talk to are the owners of the data that will be displayed. These are the people in the banking department who handle checks, cash withdrawals, credit card transactions, and so forth, and also those people who handle the purchase and sale of stock shares, unit trusts, life insurance policies, and so on. These people will know what documentation or statements are sent to which customers, as well as the information those statements contain. In addition, these people will likely have a good idea about how customers may want to reconcile these statements, and also what data will be allowed to be downloaded and inserted into your SQL Server database At the first meeting, you examine all the documentation to see what information is stored in the current system. You find out at the meeting that the current system sends out different statements, one statement for the customer’s current account and a separate statement for each financial product the customer owns. When you examine the statements, you focus on the information each contains that you need to capture to produce a similar statement. This could be not only customer-related information but also regulatory statements. With the information from all the documentation in hand, you can start discussions about what is required from the system you are to build. Obviously, a great deal of information will be discussed in these meetings; some of it is useful and some not. Make sure that the discussions are recorded in the order in which the people present make points and not in “logical” order. This simulates meetings where people “remember” items that have to be catered to, where one point raised may make someone remember a point elsewhere. Out of the initial discussions, you note the following points:

1. The software must be able to handle working with more than one product. The main product is a current checking account that a bank or a single user might use to hold banking details. The system also must be able to manage by-products such as loans, life insurance policies, and car insurance policies, and it should be able to record any trading of shares on the stock market.
2. Statements are produced on a monthly basis or at any time the customer requests them from the system. If a customer requests a statement within the month, there will still be a statement produced for that month.
3. Interest accrues daily on accounts that are in credit and is subtracted daily from overdrawn accounts.
4. Annual, monthly, or single-premium products can be held for a customer or held by a customer in a stand-alone version of the system. You need to know when to start and stop collecting payments, and you also need to determine which products you send out a reminder for (for example, a notice to let a customer know her car insurance policy is up for renewal).
5. If a collection for a product failed, the system needs to recognize this so the amount can be collected the next time a collection is due.
6. Each product will have its own statement, but only banking statements will be produced on request.
7. Trading of stock shares is allowed, so the system needs to record and display the current value for a customer’s specific share at any time.

Notice how the information in this list is in no set order because this is how information tends to come out. Also, notice that there is a little bit of duplication of information in points 2 and 6; if this is not realized or understood, it could cause problems.