WAREHOUSE TWEAK: Clients By Group ID (part 1)

From Dwayne Wright - Certified FileMaker 9 Developer
WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright

Chapter 6: Warehousing Data

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.

Here you can see the tabs I'm mentioning. My client is a wine distributor, so the tabs say Winery instead of Supplier but you get the drift.

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)

ADVERTISEMENT ==================
Help support this blog by considering a donating to its ongoing growth. For more details, please visit http://www.dwaynewright.com/donate.html

FILEMAKER: Warehousing Data Lightly Explored

From Dwayne Wright - Certified FileMaker 9 Developer
WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright

Chapter 3: Advanced Report Design

==========================
RELATED AUDIO FILE:
FYI... I read most of this article for an experiment. If you would like to check it out, here is the link ...

http://www.dwaynewright.com/audio/POD_WarehouseMaster.mp3
==========================

There are two types of database implementations but FileMaker succeeds so well at one, the other is often ignored. I’m speaking of operational database and the more reclusive warehouse databases.

Operational databases are used day in and day out to provide up to the minute reliable interactions with information. FileMaker is one of the best databases in the world for this, particularly in small to average size workgroups. Another form of database is the analytical or warehouse database, these types are often used to store large amounts of historical and static data sets. Warehoused data, unlike operational data, is locked and doesn’t undergo editing by users in any way.


Now I recently picked up a client that switched developers and they had a reporting task for me. The previous developer setup a complex report to show year by year sales data for customers and products. The report had a rather complex and intense setup process where the customer would outline what customers to report on, what products to report on and what four possible years they would want to display on the report.

Basically, it was a tab panel based data entry assistant. You make choices on the first tab and then navigate to the next tab. Based upon your first tab selections, you get a filtered set of options on the next tab. This process continues until you reach the last tab and there you can execute the script that runs the report.

This customers comparison report would use a subsummary by customer name, then by supplier and then by the products sold by a supplier. In the body of the report, would be the product id, product name, year 1 sales totals, year 2 sales totals, year 3 sales totals, year 4 sales totals and a grand total for the four select years. The final result was a classic crosstab report that the client found very useful. They wanted a couple more of these reports that showed data in different combinations and for some otherwise ignored business units.

The developers implementation of this data display was brilliant but flawed. I’ve seen this quite a bit lately in the developer community and have written about this before. The developer used a hunt and gather routine in combination with variable arrays to build the data sets. The reports took forever to run but the coding under the hood is quite remarkable. Because the coding under the hood was also largely undocumented and it used multiple subscripts, tweaking this report for my clients needs was about as flexible as a steel girder.

Sometimes the most simple implementation is the best and sometimes asking the client an extra question or two makes all the difference in the world.

These new clients are so nice and I spent a few hours trying to merge my mind into the programming created by the previous developer. I finally reached the conclusion that I would need to call the client and break some bad news to them. Due to the implementation of the previous developer, these tweaks to the report was going to be very expensive. I thought it was a shame because this could probably be a data warehouse implementation. Then I thought, “you dummy” make one of the new reports a warehouse implementation and show it to them!

I called them up and asked them one quick question, “Do your sales for previous years ever change?”. They said no, in fact, they would like for a method to lock older sales records so they cannot be edited by mistake.

So I created a new file for my data warehouse and then needed to make a first pass at populating it with data. I imported a set of records from the sales order line item table that had every unique combination of a client and a product. I then used that data to form a set of relationships back to the sales order line item table to get aggregate sum totals of each combination. Basically, I wanted to get all the totals of each year combination from 2000 to 2009. Then I created a static field and used the Replace command to put the calculated results into a field that could be indexed.

So I would have a record that had a customer id, a product id and static totals of each years matching sales activity.

The final result was a new tab in the customer file that shows a year by year breakdown of every product they have ever purchased and it all uses non-calculated static data. I have one problem with 2009 data. So I run a script from their server that updates the static 2009 totals and the customer is happy with a one day lag on their information. A customer can do standard searches for the data they want and then perform constrain finds on the found set for just about any combination they can think of and the results are immediate.

So warehousing your report data can be a very big boon for your database projects with large sets of historical data!
=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2009 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.

ADVERTISEMENT ==================
Help support this blog by considering a donating to its ongoing growth. For more details, please visit http://www.dwaynewright.com/donate.html