Data mining
-
I'm starting the developpement of a brand new version of an in-house application.
One feature that would be incredibally usefull for the users is to be able to have a way to question the system. For example : From this clientID, get me all of their Bills, or tell me all the hardware we sold / installed on their sites. Tell me when did we work for them.
I'm still in mode beta 0.01 on this one. I just had an idea for a feature like that but I've never seen any system that could do this. I've seen system where you could for exemple open a bill, then by double-clicking on an id field you'd open up the client's info or the bill of work. In other words, all the ids presented on the form can be drilled down 1 level by double click. What I want to offer is to be able to start from anywhere and go to anywhere and choose what report they want presented to them (without having that report custom made on the fly, just pick from that list of available reports for that data).
I'd like to hear everyone's comments on how this can be done and what load of work this might represent. Any ideas you have are very welcomed at this point.
-
More details on what I'd like to offer : starting from any of these identities (single entity, single id) :
Technicians
Bills of work
Bills (to be paid by our clients)
Contracts
Inventory
Orders for inventory
Clients
go to any other identity on that list and present me :
List of available reports for that case.
Execute...
-
I know others are doing this because I remember listening to a podcast a few weeks ago where this exact approach was taken. Basically infinite drill down.
However, it sounds more like you're extremely interested in the relationships between various entities... meaning you'll need to store that as metadata. Basically, taken the ER diagram and put that into the database to be queried... something to the effect of having an entity table and having a second table (join table) relating entity to entity. If you queried on this set of tables, it would tell you what types of connections to provide.
K. Brian Kelley
@kbriankelley -
I had built a data warehouse system doing this kind of drill down. Our company built it, we did not buy any outside vendor system because none of them suited our need.
Basically when I built the dimension table, I built the level the users want to drill down.
For example - demographic dimension - Country - State - county - city - zip code.
When I built the fact table using the dimension, I made sure all the levels of data included in the fact table.
We used Oracle at that time, so basically we built multi-dimensional database using a relational data. Sometimes the database has a lot of sparsity, but if the users want a lot of drill down capability, that is the sacifice.
-
In your case, for example client is the dimension.
Client - Country, Region, State, City, zip code, client type, other client info the users want, name of the client.
-
That seems to be somewhere along the lines of what I wanted to do (on the how to heck to do this side).
I wanted to build a list of paths (this table is linked to that table using that join condition).
From that point I wanted to use a shortest distance script to figure out the joins... I'm starting to think that I'm chewing more than I need to!!!
Any other input on this one?
-
Sure. Sit down with the folks who will actually use the system. Ask them, "From here, where would you typically go next?" and build the links accordingly. Sometimes giving them too much is worse than giving them not enough as they'll be overwhelmed by the options.
K. Brian Kelley
@kbriankelley -
This is already going to be intergrated to some point. You will be able to dbl-click on any id field to drill down one level and see that info (assuming the user has permissions of course).
What I wanted to do is allow the user to start from table to table d in one shot... without having 3-4 clicks to do.
Or even offer some things that a drill down like this can't do :
From this client ID, show me all the commands they did with us, or all the products of all the commands they have with us.
-
Ninja,
Your example is not drilling. Your example is showing how to get information from client table to command table and from command table to product table.
Example of Drilling -
Client table - Region, State, City, zip
Command table - all the commands that the clients did.
The user may query
Show commands with product A for clients in East Region.
drill down
Show commands with product A for clients in New York
Drill down
Show commands with product A for clients in New York City, New York
-
Thanks for the information. Things are a little clearer now
. -
Have you thought of creating user views for them to use?
We have a system that allows our users to query the data such as your talking about.
The back end is made up of several different user views. The front end is a web application that builds the actual SQL that browses the view. We then return just a sample of the data for the user look at and change the columns and sort order and then allow them to export the data in one of serveral format (i. e. EXCEL, TXT, ACCESS). It really is a pretty slick system. All columns in the view are available for them to build their criteria for searching. Depending on the data type on the column determines what type of comparison is available. For instance if the column data type is TEXT then comparison LIKE is used however the interface actually calls this comparison STARTS WITH, END WITH and CONTAINS to make it a little more user friendly.
Total development time on this system was 2 years. With one full time web developer, one full time data base admin, and one tester.
Hope this is helpful for you. Best of luck on your project.
Neb12pgmr
-
Thanks for the info.
Now let's see how I can do this in 2 weeks
.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply