Re: Navigation question

On Feb 16, 8:33 am, "Kevin Kirkpatrick" <kvnkrkpt..._at_gmail.com> wrote:
> On Feb 16, 7:53 am, "dawn" <dawnwolth..._at_gmail.com> wrote:
>
>
>
> > On Feb 15, 7:04 pm, "Marshall" <marshall.spi..._at_gmail.com> wrote:
>
> > > On Feb 15, 4:11 pm, "dawn" <dawnwolth..._at_gmail.com> wrote:
>
> > > > On Feb 15, 4:51 pm, "Marshall" <marshall.spi..._at_gmail.com> wrote:
>
> > > > > Fred, Joe, Engelbart, Desmond, and you are all looking
> > > > > at the SQL and thinking about something else.
>
> > > > Actually, I'm thinking conceptually (navigation), then modeling
> > > > logically (in this case with SQL).
>
> > > You might think you're thinking at the conceptual level, but
> > > you're not. You're thinking at the implementation level,
> > > aka physical. Neither the conceptual level nor
> > > the logical level has any concept of a current
> > > location, for example.
>
> > I figured it out -- I'm designing for the USER and when designing at
> > the logical level, I'm also designing workflow. After pulling up
> > information about a company, for example, the user goes to one of the
> > orders listed. From there, they might go to a product that is part of
> > a line item on the order. This is similar to users who go to a web
> > site and from there navigate to other web sites. Users navigate.
> > Their workflow includes a navigation path. So, at the conceptual
> > level we can navigate, users navigate, and at the physical level we
> > navigate. But there is one thread of theory within software
> > development that keeps pushing against navigation, as if it were a bad
> > thing.
>
> Perhaps an example would illustrate the difference I see in
> approaches. Allow me to describe a report I just tuned. First, a
> blurb about our reporting tool (shipped with the DBMS). Like any out-
> of-the-box reporting tool, our report designing tool allows the
> developer to specify one (or more queries) and add all sorts of nifty
> output formatting. Using a simple GUI, one can specify page breaks
> and section breaks, each with headers, trailers, etc; one can "add on"
> aggregation levels just by dragging columns around, and one can
> specify any number of post-aggregation formatting: order one level
> aplhabetically, but another by dollar amount; sum dollars at each
> level - all without modifying the base query. Reports are coded with
> a GUI that uses boxes to represent queries, aggregations, and
> computation functions; lines to show how these things relate; and a
> visual schematic that maps the definition GUI to an output screen.
>
> The report I needed to tune was coded "navigation style". The
> comments and layout of the report definition read just like the
> developer read the requirements and attacked the problem as follows:
> "the client wants to see company name in the header - so I'll write a
> query that "goes to" the comany table - Q1; the client wants budget
> amounts by department, so I'll write a query that "goes to" the
> department and budget tables, pulling budget amounts for each
> department, using the company name I just fetched - Q2; the client
> wants total budgets by company, so I'll write a computation function
> C1 that adds amounts from Q2 and resets whenenver Q1 "goes to" a new
> company...(and so on)"
>
> Before I go on, does this seem like a sound navigational approach to
> developing such a report?

Maybe, with the navigation coming in the reading of budget information for each department, using the company name to navigate to these.

I work with a database where you can read in "header" data and have a set of foreign key pointers to child data (not a traditional SQLDBMS).   So, I can see the code reading the company data, then using the set of foreign key pointers to "go to" the budget amounts for the departments. The totals and subtotals (aggregation) are not something I would put in the category of navigation. But for an SQL-DBMS, I would think of going the other direction (irrelevant to the question at hand, but thought it was worth noting).

Interesting that your bring up reporting, since I don't think navigation -style related to reporting, in general, so I'll have to figure out why that is. I guess I do when we are talking about OLAP where there is an interaction and the user "goes from" here to there, slicing and dicing and drilling down (navigating) to greater granularity. So, the fact that the user is not updating anything and that we are not talking about work-flow with a static report is likely the reason I do not think in terms of navigation. But, as you indicate, the code can still use results from one query and use them for another query.

> The report had 8 queries - one base query and 7 "contingent" queires
> parameterized by various values selected in the base query (an
> important point I'll revisit below: each of the 8 queries was
> individually tuned - they all used appropriate indexes based on their
> respective input parameters). Over a dozen custom "compuation
> functions" were scattered throughout the report definition (since
> multiple queries were being used, all aggregation was done "by
> hand"). These computation functions were essentially local variables
> with explicit assignment statements; their use is in stark contrast to
> the implicit aggregation functionality supplied with the report
> product itself. The definition of the untuned report had 8 query
> boxes, 4 aggregation boxes, and 14 compuation boxes spread out over 3
> screens.
>
> I, for one, did not think this was a good approach to the problem. In
> fact, after a quick perusal, I closed the original report definition
> never opened it again. I read the client's original requirements plus
> the few changes requested over the years, and wrote a single query
> that pulled the ~15 fields of interest from a single query joining ~10
> different tables (in the definition file, this is 1 box). With 5
> mouse clicks, I broke the rsults into 5 levels of aggregation - so I
> now had 5 boxes - then placed sum() and count() fields within these
> aggregation boxes. I drew up an output schematic directing to the
> appropriate locations of ~20 fields, then compiled and tested my
> version.
>
> I did not get a single compilation error - everything just
> "worked" (not surprising, because it really was as simple as what I
> just described).

This might be an example of removing navigation, but it also sounds like a) possibly removing unnecessary added queries/combining queries and b) using higher level tools so that any navigation is not specified by the developer. I do like reporting tools, and I don't think of coding in "navigation" when I use them.

> Our reports are compiled into report executables -
> the old executable was 130k, the new one was 10k. The old report was
> running in about 1.5 hours at night; but had run for upwards of 4
> hours during peak business hours (when the OLTP applications are
> hammering away at the indexes). My version ran in 40 minutes on our
> development box, and in production it ran 25 minutes at night and 35
> minutes during the day.

Great!

> Out of curiosity, I reviewed my base query's execution plan: it used
> only one index on one table; all other tables were full-scanned and
> hash-joined. Of course, having taken a declarative approach, I didn't
> really need to see what indexes were used or what order the tables'
> data were read (since I never even considered the problem as a logical
> "navigational" problem;

and, for the record, in case it wasn't clear, this is not where "navigation" kicks in for me either. I think sets, sets, sets when using reporting tools.

> I didn't really care what table was visited
> first, second, or third; any more than a java developer would care
> about the byte-code generated from compiling a java program). It was,
> however, interesting to note that the optimizer actually "went to" the
> company table last - exactly opposite of the intuitve navigational
> decision made by the original developer.

but oddly similar to my intuition. Did the reporting tool make multiple SQL queries?

> What approach to these types of reports or application designs is
> generally better - a navigational approach "go to t1 and get company,
> now go to t2 and get department, then use those to go to t3 for
> budgets", or declarative: "join tables t2, t3, t1 and return company,
> department, and budgets"?

This is ill-thought-out, off the top of my head -- If there is no reporting tool, so that you cannot simply declare the entire report and are writing the database access statements for the DBMS to execute, then you have both the issue of retrieving the data and presenting it. I admit I haven't written a sophisticated report with break points, header and footer data, multi-line addresses that are stacked, but do not take up more space than necessary, allowing other data next to these lines, etc without a reporting tool in quite some time, but something in the mix needs to take the result sets from the queries and turn it into the presentation. If the query language is incapable of doing that, then, hmmm, I'm thinking the reporting tool does iterations under the covers, so I might have to as well? That isn't the same as navigation, but within the iterations, one might navigate, perhaps?

Thanks, Kevin. I was using an example of interactive data maintenance applications, so thinking about read-only is helpful. But it does seem like the reporting tools have removed any need/desire for "me" to think about navigation for reports. cheers! --dawn Received on Fri Feb 16 2007 - 16:43:05 CET