Prelude
I always find interesting when I have a business case or project where the business requirements involves technical development even in the smallest way.
I’m working at a reporting unit of the institution. We used to have an application maintained for this function unit for their reporting and query needs. As the business grows, the function unit decided to purchase a new third party software to capture its business transactions and reporting needs. My department was not involved in the business decision.
The third party application is web based. Because the users want to analyze the data, the vendor provided a simple query interface through the application so the users can extract the data that meets certain criteria. First of all, the query interface was poorly designed where tables can be viewed awkwardly and the relationship between the tables were not presented in an explicit way. Secondly, the query grid has many limitations regarding the type of the criteria setting and has no capacity to incorporate calculated field to the query criteria. The vendor also provide a sql query tool through the interface, but since the users are not technical savvy and the query interface assumes users have comprehensive knowledge of the underlying database structure and relationships between the tables, the sql query interface wasn’t utilized as intended.
Later my department was asked to produce the statistic report of that function unit, we couldn’t do it because the old database is not maintained and does not have the updated data. We also found out that the third party software can not be easily integrate to meet the institution’s reporting needs. In order to re-establish the reporting function for the new databases, I asked the function unit to contact the vendor to provide me the access to the software as well as establish the back-end access to the data tables through the help of our IT people. Later, I got access to the back-end tables through Mysql ODBC.
I was using MS Access as an intermediate tool to view the tables because I wasn’t provide with any information on the third party software, not on the table and fields structure, not on the business process and flow or the relationship between the tables. Basically, it is self-direct investigation. From the more than 200 tables, I narrow down about 30 tables I would need to produce reports based on an iterative discussion with the function unit on what kind of report and data they want. As a temporary solution, I used Access to understand the database and develop the queries and reports for users to verify. The next task will be set up the database and develop reports on SAS platform.
BI Practice 