In a previous post (Warehousing Data Lightly Explored), I mentioned how I used data warehousing for a client. This was the type of report that presents year to year activity comparisons for sales and purchases. I showed the results to my client and they were quite happy. I was relieved as well because their database was NOT one that I created. The database is very code dense and it is challenging to see how extensive the coding prevails on the most rudimentary of tasks. My solution added a new warehouse file to the mix and has minimal interactions with the core coding of the solution.
One of those year by year comparisons was for products a client purchased. I'm showing this information in a dedicated tab panel called Comparisons. I included four sub tabs to allow the user the ability to see the data in different ways. Two of the tabs were supplier based, one showing the totals by A-Z and one by the greatest amount purchased. I can do this easily because my grand totals are all static value. I have those same to sub-tabs for products purchased.
GROUP ID TWIST TO THE MIX
Now my client had a twist in mind (don't they always?) and this had to do with a way some of their client records are grouped. In their clients table, they have a group id field. This allows them to attach multiple client records together in order to form a grouping. The main reason for this is that some client records are different locations for the same overall client. So they wanted my breakdown report to show overall grouping totals for the records that need them.
SEEING WHAT SCHEMA / DATA HAND I WAS DEALT
I went ahead to see if the previous developer was capturing the group id for each sales order line item record. Sure enough, the group ID field did reside there and I did a quick search for non-empties. The group ID did have data for about 70% of the overall records, so we are pretty good to go.
GET THE NEW WAREHOUSE TABLE IN ORDER
In my current version of their data warehouse, I have a table (with data) for every unique combination of a client / product. I use the combination of the Customer ID and the Product ID fields in a relationship to get my annual total breakdowns into calculation fields using aggregate functions for Sum and Count. I guess there may even be a future need of a Average calculation.
So the table mentioned above, has a structure and data set) I need! In fact, it has a touch too much. Like removing the sculpture from a large piece of granite, I need to duplicate this table and extract the the things I do not need.
GETTING THE GROUP ID DATA
My current warehoused data does not include the group ID data. I'm going to start things off with getting the group id data added to my customer / product warehouse table. I simply add the field to the table and have it auto enter the group id data from the sales order line item table. Then I was inspired to add some more background logic. I'll add the group ID related fields from both product and customer tables. Because I'm wondering, what if a company is assigned a group id after that company already has orders in the system. Did the previous developer take this into account?
BRIEF OFF TOPIC
I do want to make sure I check on this for the client's behalf, so I quickly cruise over to basecamphq.com for add that action item for their project. I’ve been using this service for about a month now and it is working out nicely. I pull up their project and I add a new To Do category called Development Questions. I haven't actually give the customer access to the basecamphq account I set up for them. I'm going to spring the idea of them using it as a tool to manage their database needs. This will be a great opportunity to show off how we can communicate and schedule database construction activities.
(to be continued)