I'm in search of an easy to admin and deploy solution that will
allow specific users the ability to perform bulk data updating for
controlled data sets, using simple and complex criteria. The data
could be local for one file or across several files. We have Access
but one problem is it has caused issues with botched SQL queries
crashing the server due to memory abuse from improperly linked tables.
Maybe this has been fixed in our current D3 ODBC release, but that
fact is I don't have time to rewrite new queries and create one-off
reports on a daily basis for people who don't know how, and don't need
to know how, to use Access. I moved away from Access a while ago due
to these issues. I'm getting by with tab-del or CSV imports using
quick-n-dirty import snippets, but I really should not need to do
that. I should not have to bring in Access gurus to perform bulk data
changes and generate their own reports either. Yes, many of our users
have been shown how to use the data import feature in Excel but the
sheer number of tables/sub-tables and cryptic dictionary names make
many of them weary of even looking at it.

I do not want to see ads for frameworks that require weeks of .NET
development on my part. I want a solution that I can drop in, flag
some files and dictionaries, and then let our provider trained users
make their own data updates and pull their own complicated reports.
I've seen a mix of solutions at previous Spectrum conferences but most
of the ones that stick out in my head are either app gen solutions or
BI reporting and analyzing solutions. Can anyone throw me a few bones?

Thanks,

GlenB

GlenB wrote:

I'm not sure I understand the goal. Are you thinking about something
where the user visually maps a flat file to pre-authorized MV files
and defs, with subsequent parsing and upload from/to correct
locations?

I know you're looking for a ready solution that doesn't require any
kind of development, but NebulaXChange was designed with that sort of
application in mind:
nospamNebula-RnD.com/blog/tech/2009/03/xchange-intro1.html


That problem would not be solved by the above, but someone would still
need to create friendly names for common fields, and differentiate
those from dict items that confuse the users. In other words, ACBAL
and BAL1 and ABAL_TEST5 might all point to the same attribute but
someone should create Account_Balance for users.

BTW, for any site plagued by such things, I highly recommend starting
a new project to create standardized filenames, like CUSTOMER_MASTER
to replace CM and ORDER_HEADER to replace OH. The new names would
simply be q-pointers to the live files. Then create new corresponding
dict files like DICT_CUSTOMER_MASTER or ORDER_HEADER_SCHEMA and
copy/rename a clean set of dict items. Then users can do this:

SORT ORDER_HEADER USING ORDER_HEADER_SCHEMA
CUSTOMER_NAME ORDER_TOTAL ...

For that you don't need to touch the live files, this can be done over
time, and it can even be managed, implemented, and documented by
end-users with very little knowledge.

That's almost like creating a View for a RDBMS, quite common in the
relational world but rarely done in our arena.


I hear ya on that. The thing that gets me is that reporting products
are generally not designed for data entry, so you're looking for a
developer tool for GUI, something more like DesignBais (which has
reporting) than MITS.

But that doesn't satisfy your requirement for Bulk updates. Think
about it, bulk operations are defined and executed completely
different from item entry/update and reporting.


Well, toss us a couple bones first ... Can you describe the solution a
little better, maybe with a specific scenario, so that we might be
able to think of how some existing product can do what you need with
minimal effort? You could be looking for more than one solution, one
for item entry/update, one for reporting, and one for bulk update.

Best,
T

On Jan 6, 12:16*pm, Tony Gravagno <tony_grava... (AT) nospam (DOT) invalid>
wrote:
It's tough to explain because there are different updating
requirements depending on the scope of the data update. It could be a
simple "replace X with Y" in field "A" for all vendors that have field
"B" set to "A". A more complex one would be "update this huge table of
pricing to the cost matrix file with this spreadsheet of pricing data.
At the same time, change fields A, B, and C in the product file to
match a different spreadsheet keyed by part number."

I will take a look.

This is yet another project I've had no time for, but it needs to be
done.

That is something that I've considered but the user's who are capable
are already overloaded with their own work. That's one of main reasons
I've posted a recent job ad.

I know and I'm trying to avoid multi-app training since it may only
confuse the user and will take longer to get rolling on. Access can be
used for both and there is coherency between pulling tables and fields
for reports and selecting tables and fields for update queries, Is
there not something similar for Pick that is more user friendly? I
could expose a lot of it using MV.NET but how easy will it be for the
user to consume it? If I have to write business logic and data
updating logic subroutines to help someone build an app in, say
DesignBAIS, then that defeats the purpose of deploying it as a user
tool. Some amount of intelligence is given with regards to the user in
question, but if they have to write VB script to make things happen
then it's not a general solution to the problem.

GlenB

Glen,

From the small example you have given, it sounds as though you want a
"simple" solution for what is, at the core, quite a complex
requirement. It probably doesn't help matters that it sounds like you
are trying to solve 2 very distinct problems (mass updates &
reporting) with a single tool.

There are some harsh truths that need to emerge in terms of "data
visibility", which will impact your ability to "palm off" either
issue.

If you REALLY want any user to be able to pull off ANY data from ANY
file, then each of those islands of information (be they fields or
files) needs to be adequately defined, both in terms of content (eg:
this field is a date, or an MR2 style field) and also relationships
(this is how you can construct a key to go & suck data off this
related file).

The good news is, if done "correctly", the same set of definitions can
be used for reporting and/or "data manipulation" (and perhaps these
things already exist within your dictionary definitions anyway?) ....
but apart from taking personal responsibility for each request
YOURSELF, I don't see any way that you would be able to circumvent
involvement/responsibility for establishing data definitions &
referential rules.

Looking at the discrete issues individually, I wouldn't necessary rule
out a BI solution entirely. I know from our use of Visage.BIT within
our own ERP offering that individual "data silos" appear to work quite
well.

Load an end user up with a cube that contains, for example, every
piece of information people have ever wanted referenced/included on a
report for, say, Sales Analysis, and let your users go. No need for
them to worry about HOW data is related/extracted .... they can
concentrate on "exploration" to solve whatever their unique data needs
are NOW.

(In my experience it is then "rare" that someone looking at sales data
is going to want to relate that to General Ledger or Payroll)

For the data updates, I'm guessing that if your users had to
manipulate the data & make changes "by hand" they would simply fire up
an existing program, call up the required records, navigate to the
appropriate screen/field, and change the data .... right?

If your exiting software uses a common input routine, then you may be
able to add a "macro" facility (in our R5 product we call it a
"keystroke", as it simulates what an operator would do if they were
sitting on a keyboard) .... mate this with an ability to do a READNEXT
from a select list (previously generated with whatever your selection
criteria was/is) and you may end up with a viable solution.

I know this isn't as high tech or buzzword friendly as doing SQL
inserts, but we have effectively used this technique for over 20
years ... and it is EASY for operators to understand, because it is
built around the concrete screens they use on a daily basis, rather
than the "abstract" idea of files, fields & relationships.

(Also works well if your application currently does things like
tracking changes to the database, 'cause the same "rules" apply ....
no need to re-invent the wheel)

It is equally likely that this may not provide a solution for your
situation, but the approach may still be useful for others to
consider.

Of course, the other "solution" that you could use is to fire your
criteria against the database & extract key components of the matching
records (eg: minimum @ID, fields to identify the record [eg: customer
or product name], and also existing values for the fields you want to
change) out into Excel, which in my experience tends to be where
everyone wants to make their changes.

The amended data can then "simply" be re-imported.

We have "stuff" that lets us do this within Visage, but I'd hazard a
guess that it wouldn't take too much work to generate a workable
solution around, say, the FTD utility in Accuterm.

Not all nice, neat & packaged I know ..... but still may lead you to a
workable solution with minimal outlay ($ and/or hours)

Hope this helps ... contact me if you would like to know about the
"special commands" we have built into our keystroke/macro facility to
accomodate the automation

On Jan 6, 6:01*pm, Ross Ferris <ro... (AT) stamina (DOT) com.au> wrote:
Yes. I know. I want a magic wand and I will probably end up writing
it to fit our own needs. :/

No no no. I will be deciding what they have access to. I will be
defining the "data silos" as you term it and then they can play as
they wish. A nice feature, which I will implement myself if I write
it, will be a request queue for updates so that a manager can review
and apply update changes before the commitment happens.

A BI solution will probably be very useful for management and
product/marketing people at our current and growing level of business.
I've written some green screen analysis tools already for queries that
happen regularly and those will easily be outdone by a properly
deployed BI solution. I don't want to have to train people on two
completely different systems but I don't think I'll have a choice.
There is quite a bit of business data that has yet to be mined and
analyzed. Right now, true data mining only happens when the business
requires it. As our market reach expands and our customer base
broadens the questions about "who buys what" and "what sells better
when" become more important than just "how many of X have we sold".
All companies in retail and wholesale have experienced this at one
point in their growth and I'm sure a canned BI solution will easily
help with that.

Well it's a bit more complicated than that. There are a multitude of
views for a specific count of data columns. If you consider the sheer
number of columns of data we have just for product sales it would take
me days to just build a list of them. Most of them will probably never
be used and are a waste of storage space. If the users don't already
know what data is available and what views of that data they need,
then I will still spend a lot of time tweaking old and building new
data silos so they can run a complex report once or twice. I dunno..
Maybe I'm thinking too hard about it.


Sure for a dozen items. If they have to make changes to several
thousand, no. They'll send me a spreadsheet and ask me to update it.
If it's a complex update that requires changes to multiple files/
screens then they usually bring me into a meeting to determine the
best way to handle it.

I get your point, but there are more clever ways of dealing with
this. I just can not seem to encapsulate all of the update requests
into a universal tool. There are too many differences between them. I
was hoping to find a query and update solution similar to how a BI
tool pulls data, but allows updates based on a more limited data
scope.

Screen scraping and screen automation has always been popular but
I've never really like the idea of having so many interface layers.
Screen scraping, primarily, has not exactly been perfect in my
experience and with huge data updates the whole stack _has_ to support
perfect transfer of data because in many cases post update reviews may
not be that simple to perform. I know we're not talking about scraping
here but a simple variable assignment warning can really screw up a
screen scraping integration so imagine what a keyboard buffer problem
can do.

I do that now, but in some cases the documents get "tweaked" and
stuff gets changed. We're on the same page here, though, and I'm
starting to move in that direction using a data update queue. Users
will be able to download spreadsheets of data that they can tweak and
resubmit. Tony's product suggestion flipped some bulbs on, but I think
a queue approach may be more beneficial since the updates can be
reviewed, redownloaded/changed, and then approved by a manager. I'm
not sure, though, on where to go with the user interface.

I use the transfer util in Accuterm for cases where remote access is
a requirement. Mostly, though, I use Samba shares and Accuterm DOS
commands to transfer to/from user's PCs into temp dir shares on our
server. If I could get netBIOS to play nice with OpenVPN's non-bridged
tunnel I could get rid of the transfer util completely. Too many
things to do - not enough time.

Thanks for the notes. So far I've only talked myself into adding yet
another project to my plate. :/

GlenB

GlenB wrote:

Glen - I think "the problem" is too nebulously defined at the moment.
You have a lot of people asking for a lot of things and you just want
a solution to it all. The answer to Life, the Universe, and
Everything is 42, it's quite simple. But I.T. requirements are
unfortunately not as simple. I seems to me that you don't have a
clear description of specific use-case scenarios to define a solution
that would solve all of your problems. You need reporting, item
inquiry/updates, and bulk updates. Those are three classes of
problem, and while a single solution for all classes would be welcome,
I think it's easier to assume that there will be three solutions,
because individually those needs can be satisfied. From there you can
solve one problem at a time, rather than looking for the universal
solution and solving none of the problems.

For BI/Reporting there are many companies in the MV space, and your
data can be exported to an RDBMS for use with mainstream offerings. I
could have sworn we had a whole page at PickWiki about this but I
guess the info is still spread out in forum postings.

For inquiry/updates, a variety of MV-based products would be ideal
here. Again this info should be at PickWiki but no one has taken the
time to aggregate info about Viságe, DesignBais, and all of the other
offerings that we've discussed over the years.

I have never seen a product in our industry for bulk updates, probably
because everyone defines that problem differently. But I will
follow-on with notes about NebulaXChange to see if any more bulbs can
be turned on...

If you look back at the blog you'll see that NebulaXChange Reads data,
Saves data, and Executes subs with data.
nospamNebula-RnD.com/blog/tech/2009/03/xchange-intro1.html
So you can use NxExecute to save data from a spreadsheet into temp
files which will then get audited prior to movement into the database.
That still requires code to be written on the back-end to actually
move data that's been provided into its final destination.

(Note, unlike AccuTerm with custom scripts, a unique feature of
NebulaXChange is that it can be used over the internet and by anyone
in the office without installing a licensed copy of AccuTerm on the
user's PC.)

But you've given me an idea for an enhancment to NebulaXChange too.
To summarize current behavior: A user opens an Excel task pane (the
Database Explorer) which allows them to point to a server and account.
They can select files which have been pre-approved by management.
Then they see pre-approved definitions of those files. With all of
that detail we know exactly where detail resides, so the user can
copy/paste a generated formula which allows them to read or write that
data.

So a user generates a spreadsheet with a bunch of formulas that Read
data, then someone makes updates, clicks a button, and all of the
Write formulas trigger changes back to the server. Powerful. Cool.
Scary. A lot of words can be used for that and my job is to refine the
process and security to make it more cool and less scary.

Now, I've been writing directly into files as the user directs, which
is the scary part. But I can easily write data into alternative
files, and then do a final post to live data after a manual audit.

One issue here would be with data latency. The user pulls data from
specific MV fields and then submits updates for auditing. The updates
are reviewed an hour later and approved. In the mean time the MVs in
the live data have shifted and updates to old MVs will trash data
integrity. I can introduce a form of optimistic locking to prevent
this scenario, but this is more of a v3 sort of enhancement for a
product that hasn't even been exposed as v1 yet. Glen, if it solves
some of your problems, we can talk.

T

On 2011-01-07 15:23:58 -0500, Tony Gravagno
<tony_gravagno (AT) nospam (DOT) invalid> said:


Ouch. Self-deprecation?

--
Kevin Powick

Kevin Powick wrote:
The concept crosses my mind whenever I use the word, but:
"Sometimes a cigar is just a cigar."

I hope you do. Perhaps you could share it inexpensively with us.

Jeff

On Jan 8, 10:10*am, "Jeff Caspari" <mu... (AT) idt (DOT) net> wrote:
Expenses are so relative that the best approach to this is to
offer it up as an auction. The real question is, how many auctions
will there be and what level of relativity are you willing to accept?

GlenB