|
|||
|
|
|
|
|
|
What is a DatabaseWe are surrounded by databases. They are everywhere and you can get the data out of them directly from your Excel workbooks. We offer you below a quick course on databases and their 4 basic components: the tables, the queries , the forms and the reports Database Table In a modern database (fed by accounting programs, manufacturing programs, sales programs, human resources programs, cash registers, sensors on machines...etc) data is stored in a very specific way. In what Db (database) people call tables. Records (rows of data) are stored one after the other without interruption. Each record is made up of many fields (columns) holding data for record number, date, name, amounts, account number, etc. A basic table can look like this:
There are usually many tables in a database (clients table, employees table, products table, sales table, etc...) The data is organized so that the computer can easily find what you are looking for. So the first thing that you need to ask of the DbA (database administrator) is the name of the table or tables that you will need to look at to find the data necessary for your reporting activities. There could be hundreds of tables in a database most of them created for database administrative and management purposes. Most of these tables of no interest for the data analysts and only a few are holding the data that needs to be analysed. You can extract data from any table if the DbA (database administrator) grants you access to it. If the DbA is reluctant to grant you such access to the live database he can generate mirror tables for you in the form of text files or Excel files. You can then extract the data and import it in your workbooks. The three other main components of the database are the query, the form and the report. Database Query The queries generate extracts from the tables. The user might not be interested in the complete table, he might only want to see the sales for a certain month or for a certain product or he might want to see subtotals by product or by month. Using a language called SQL (pronounced sequel) Db people extract data (by date, by product, by account, etc) and send the resulting sub-table to the analyst. Using queries, the Db people can generate all kinds of files (txt, csv or xls) containing the data that you need and save them on the network where you can access them. You as data analyst can copy/paste the data in your reporting workbooks or even better you can develop queries within Excel to import all or part of the data that is in these files. See how to work with Microsoft Query a program that is within Excel. Remember that the DbAdmin will rarely give you access to his live database but he can easily program a series of queris that will get an organize the data that you need from any database in the company. Database Form The form is the screen view that allows the user to enter data in the database. Some forms can be complex things with drop-down lists to make sure that the values that you enter in the database are valid. Because many people will be using the forms these safety components can be quite extensive. As a data analyst these forms are of no interest to you. Database Report Finally, the reports allow you develop a document with the proper layout to present the results of the analysis. In all database programs this is the weak spot. That is why downstream from all databases people use Excel to organize and analyze the data (from queries) and develop reports. Other Components of the Database A database programs also manages the traffic in (thousands of users), the traffic out, the security and integrity of the data. It can become a huge thing and can cost tens or millions of dollars. The database is for the database specialist but the data within it is for the data specialist and rarely can one be both. Behind all accounting programs or other data monitoring programs there is a database. It is either integrated into the program or the program feeds an existing database. So from all these programs and databases you can extract data and develop reports and analysis with Excel. Datamarts and Data Cubes Sometimes there is data in many different programs within a company (sales, manufacturing, accounting, finance, etc...). To assemble the data and make it available to the analysts, the DbAdmin will create a datamart to bring all the data together in a single database. Datamarts will also be created to assemble sets of data specifically needed by a department or a branch. A data cube is basically a database of subtotals. FOr example, nobody needs to see millions of cash regster transations. So certain programs (Essbase) will subtotal the data by product, date, store, client or whatever dimensions are needed by the analysts. These subtotals are calculated overnight or at the end of certain periods of time and are made available to the data analysts. A lot of my clients have large centralized databases (EssBase, Oracle, SAP, Sybase, SQL Server...) or large centralized accounting, financial or manufacturing applications (JDE, SAP, Oracle, PeopleSoft, SmartStream...) but the data is analyzed and the reports are developed using Excel. They become really efficient in analyzing and reporting when their employees learn about the SUMPRODUCT function in Excel and the INDEX/MATCH formulas. They adopt this approach because Excel is the most user friendly analyzing and reporting application. They don't need to train a few analysts on other complex and limited reporting applications creating a bottleneck in reporting. Adopting Excel also allows all the analysts and decision makers to be part of the development of significant analysis and reports. The ultimate benefit is: improving the bottom line of the whole corporation. Learn more on Excel There are five sections in this website Section 1: About Databases: 4 lessons |
|
|
|
|
|
