End-user software to query a warehouse?
Ian Boyd
conjure a system to constantly bring in new data from the "live" system.
How do the computer illiterate end-users perform their own querying of my
warehouse?
i can't image any canned software exists - since that software won't know
what tables are my fact tables, and which are my dimensions tables. Nor will
it know what dimension keys in the fact tables correspond to what dimension
tables.
So that means i'm going to have to write the canned ad-hoc query software
myself. But i can't imagine any intuitave user interface to be able to
select a fact table, see the corresponding dimension tables. Pick dimensions
and facts. Group by some dimensions, group by some facts, sum some facts,
order by the dimensions and facts, pivot some facts and dimensions, etc,
etc.
i've looked at BusinessObjects' website. i've looked at SQL Server Business
Intelligence/Analysis Services. Unfortnatly, these companies are good at
obfucating what their software does - if anything. i realize all the words
are english, but i have no idea what they're trying to sell me:
"SQL Server 2005 Analysis Services provides, for the first time, a unified
and integrated view of all your business data as the foundation for all of
your traditional reporting, online analytical processing (OLAP) analysis,
Key Performance Indicator (KPI) scorecards, and data mining."
(For fun, pretend you don't know what a spreadsheet is - now try to figure
out what Microsoft is selling you when you buy Excel. They never actually
say what it is or what it does.)
So, can anyone point me to some screenshots of software in action doing what
it is i want it to? i want to be able to pick a fact table, see the
associated dimensions, drag and drop into columns, or into group-detail,
apply summing, counting, averaging, stddeving, pivoting - all from a user
interface that my mother could use.
Has the computer industry invented such a user interface yet?
Guilbert
and dimension tables.
Try this for using Excel to query a cube
http://office.microsoft.com/en-us/assistance/HP052747511033.aspx
Guilbert
Guilbert
"Software products to work with Analysis Services"
http://www.mosha.com/msolap/util.htm
Guilbert
Ian Boyd
> and dimension tables.
They have to, that's how the two are joined.
> Try this for using Excel to query a cube
>
> http://office.microsoft.com/en-us/assistance/HP052747511033.aspx
i just tried Microsoft Query. Not at all what i'm looking for. i need
something that end users can use. That is nothing but another query builder.
Ian Boyd
My god. It's more of the same. It's like pulling teeth.
Microsoft Data Analyzer: "With Data Analyzer—the Office data analysis
solution—you can quickly and easily view, analyze, and share business data,
and gain the power to make better business decisions. " How?
Microsoft Analysis Services: "SQL Server 2000 Analysis Services is the next
generation of the OLAP Services component in SQL Server 7.0." Good for it.
Microsoft Reporting Services: "SQL Server 2000 Reporting Services is a
comprehensive, server-based reporting solution that can author, manage, and
deliver both paper-oriented and interactive, Web–based reports." How?
Microsoft SQL Server Accelerator for BI: "Business intelligence enables
industry leaders to provide knowledge workers with the data they need to
make more informed business decisions. Over time, these decisions can have a
dramatic bottom-line impact on the business." Good for it.
Microsoft OLAP Manager Kit: "The Add-In Kit for OLAP Manager provides
functionality that enables you to perform tasks that previously required
custom programming using Decision Support Objects (DSO). " Well as long as
i don't have to use "DSO" anymore.
Panorama Enterprise Reporter: "Panorama Enterprise Reporter enables users to
create a variety of enterprise reports (production, transaction and
financial) through any web browser, without technical assistance. With
guided report authoring wizards, users can create custom reports leveraging
multiple data sources from across the enterprise. Global queries and
parameters libraries support re-usable enterprise report designs. " -at
least they had some screenshots; very unusable for users. They're supposed
to be making their own reports.
Proclarity Business Analytics: "As the leading OLAP front-end tool for
Microsoft SQL Server Analysis Services, the ProClarity Analytics family has
helped thousands of organizations worldwide to make sense of their data and
achieve Understanding faster." i'm not using Analysis Services
Temtec Executive Viewer: "Executive Viewer provides users real-time web
access to online analytical processing (OLAP) databases such as Microsoft
Analysis Services and Hyperion Essbase for advanced ad-hoc analysis as well
as distributed reporting. Power users and non-power users alike can gain
insight into their business with on-the-fly answers to critical questions,
converting stored data into actionable information. With no programming or
training needed, users can explore data from any perspective, dynamically
sort/select/pivot information, create on-the-fly calculations, identify
exceptions, generate graphs, print fully formatted reports, and more – with
unbeatable response times." Again with the Analysis Services
Business Objects: "BusinessObjects XI is a business intelligence (BI)
platform that integrates everything you need to track, understand, and
manage your business. It gives you easy access to accurate information and
the tools to turn it into better decisions." Care to tell me what it does?
MIS DecisionWare: "MIS DecisionWare is a business intelligence platform that
integrates information from existing source systems and data warehouses
across the organisation. MIS Alea, MIS onVision, MIS DeltaMiner and MIS
Plain front ends sit on top of this technology framework." Outstanding.
arcplan: "we provide the solutions that enable your organization to maximize
the value of information as a key corporate asset to drive your business
performance." As long as they keep using active verbs like drive and
maximize. They sure are thinking outside the box.
SofPro Tetral: "is 'one of the kind' OLAP (On Line Analytic Processing)
application that allows managers to analyze their business using their
business knowledge without learning any programming and any program
languages. " Outstanding. And the enterprise version has "Maximum
scalability due to a distributed client-server architecture."
OLAP Hub: "OLAP Hub is a zero-client, Web-based online analytical processing
(OLAP) solution that enables your organization to efficiently deliver
relevant, timely information to all your decision makers. The result?
Faster, more informed decisions, leading to enhanced productivity and
increased profitability." Contact them for more info.
TARGIT: "TARGIT is a developer of analytical tools to streamline decision
processes in companies. The products in the TARGIT Analysis Suite are
Business Intelligence tools which all employees in a company can benefit
from – regardless of their IT skills. " They have interactive demos and a
demo download version, but you have to already be a member it seems.
and on
and on
and on
Near as i can figure, i would have to define which tables are my fact
tables, and which are my dimension tables. After that, i would have to
document the relationships between the tables. Then, once a fact table is
chosen, i would have to show the FK's dimension tables. Easy enough -
something like SQL Server's database diagram (although the database diagram
is not very easy to look at - difficut for someone who doesn't know
databases to figure out).
It's easy enough to pick all the columns you want. But how can the user
indicate what columns they want grouped by, and ordered by, and which
columns should be summed, or differenced, or counted, or averaged.
It boggles the mind.
Jéjé
easy to use interface, look at Business Objects & Cognos ReportNet.
but, from my knownledge and usage of these products...
its really difficult for the user to create a good query.
The training required to use these tools is long
To allow a user to access the data through a reporting tool, you have to
provide really simple models because complex one can provide wrong results
to the end user. because its difficult to control which relations will be
used in the queries.
Microsoft will provides a new approach in SQL 2005. RS 2005 has a new tool
to create a model to allow a user to create its own report. The interface
insure that the user will not ask unusable objects in the database.
A complex model become useable for the end user.
"Ian Boyd" <ad...@SWIFTPA.NET> wrote in message
news:utIryBut...@TK2MSFTNGP12.phx.gbl...
Ian Boyd
> easy to use interface, look at Business Objects & Cognos ReportNet.
i really don't want them to build queries. i want them to see some
dimensions, and pick them. And see the facts, and pick some. No query
building. So dropping "data fields" on a report, and linking the label to
the query column. It must be doable - Kimball says it is. Although he never
indicates how such software would look.
> Microsoft will provides a new approach in SQL 2005. RS 2005 has a new tool
> to create a model to allow a user to create its own report.
Referring to end-users as "it"? Reminds me of a line:
"It turns the caps-lock off, and it enter's it's password again."
Paul Smith
1) Create a database to hold metadata regarding the tables/columns and
indexes ina datamart/warehouse
2) Create an ASP (before .NET days) web application to provide an UI,
generate and display data
The tool should actually create the DML statements, so the user does not
know what was, or how, is joined.
I must say it was a labour of love, and my life is back now I can use BO,
Brio, Cognos etc ;-)
"Ian Boyd" <ad...@SWIFTPA.NET> wrote in message
news:%23nSvonz...@TK2MSFTNGP10.phx.gbl...
JT
"Ian Boyd" <ad...@SWIFTPA.NET> wrote in message
news:%23nSvonz...@TK2MSFTNGP10.phx.gbl...
Ian Boyd
No.
i'll use an example industry from Kimball's book - plus it's an easy
example.
The fact table is the details on a cash register receipt:
Date Key (FK - Foreign Key)
Time Key (FK - Foreign Key)
Product Key (FK - Foreign Key)
Cashier Key (FK - Foreign Key)
Transaction ID (DD - Degenerate Dimension)
Quantity
Unit Cost
Sub Total
Which then links to the mentioned tables.
Now, let's assume a manager says:
"i need to see all receipts from transactions, including the cashier who did
the checkout, where the customers bought more than $40 worth of ephedrine or
pseudoephedrine containing products in any given 24 hour period."
Now. Where to begin? i could add some attributes to my products table:
"Contains Ephedrine",
"Contains Pseudoephedrine",
Sound reasonable? Perhaps it should be:
"Is Covered By Ephedrine Ban",
Or perhaps it should be
"Is Wanted On That One Special Report"
Now, that's the easy part. Now for the impossible part. i myself can write
a, SQL query that can do a sliding window aggretate on the Subtotal, finding
all the transactions involved in buying the stuff. But i think that there is
no interface in the universe that an end-user could drag-and-drop stuff
around to assemble that sliding window query.
So i'll tell him that he cannot get that report himself in this program. So
instead he'll ask:
"Okay. Can i see all receipts from transactions, including the cashier who
did the checkout, where the customer bought more than $5 worth of ephedrine
or pseudoephedrine on a single reciept. And i'll do the other calculations
myself?"
Now this, also probably impossible to generate a simple user-interface for,
is probably more doable.
So, in this software (that i have yet to find, buy, or write) he's going to
be presented with a "Transactions" fact table. And drawn around that fact
table, joined with little lines, will be some dimension tables. So on his
screen, scattered around, he'll see the various columns available to him.
From that he can see the ones that interest him:
Date
Time
Cashier Name
Cashier Number
Product Name
Contains Ephedrine
Contains Pseudoephedrine
Transaction Number
Quantity
Sub Total
So he drags some fields into the proper space where you drag them in order
to get the columns:
Date Time Transaction Number Product Name Cashier Name
Cashier Number Quantity Sub Total
Magically, this software he is using will know that "Quantity" and "Sub
Total" are summable fields, and will automatically be summed, but grouped by
Product Name, since on a given receipt that can be different.
Now, again, is the impossible part. He needs the computer to add up all the
products that "Contains Ephedrine" or "Contains Pseudoephedrine" These two
attribute fields contains the values "Contains Ephedrine", "Doesn't Contains
Ephedrine", "Contains Pseudoephedrine" and "Doesn't Contain
Pseudoephedrine".
First thing, how does he enter in this filter?
Does he right click on the attribute "Contains Ephedine" and selects "Set
Filter Condition" ? That's not very obvious.
Does he drag the fields to a special "Query Filter" area, select the
"operator" to "equals" and then types in "Contains Ephedrine". Wtf is an
"operator" ?
Can every operator to "expanded" and underneath each attribute name is a
Value like ___
Value not like ____
and he can apply filtering there, similar to SQL profiler?
Not very obivous. In case you're wondering, Access's, Microsoft Query's,
Enterprise Manager's View builders are all not obvious on how to use. They
are not friendly. And yet, it seems to be the only interface i can find.
Moving on past that nightmare. Now we have to say that, for the SUM of all
those products you've filtered on, filter on the total of "Sub Total" is
greater than 5. And that's where i give up. And that's where i am back to
writing a canned report, and adding it to the software as a new report
feature. And the customers are once again saying, "We can't ever get at the
data." Which was the whole point of a data warehouse in the first place.
Is there any end-user software to query a warehouse?
i had a friend, who works at another company send me screenshots of Crystal
Reports Business Objects in action. Not at all usable. In fact, they have a
"crystal reports guy." The managers can't use it, the MIS department has
to. Then what's the point!
minimax
>
> i had a friend, who works at another company send me screenshots of Crystal
> Reports Business Objects in action. Not at all usable. In fact, they have a
> "crystal reports guy." The managers can't use it, the MIS department has
> to. Then what's the point!
I don't know your end-users.
but those I know (in more than 150 companies) are not able to use any
query-tool to get the data out of a data-warehouse by querying with any
tool.
I think usually you have some 'analyists' (maybe 10% of all users),
who have knowledge of datastructures, and know how to handle
query-tools.
80% of the typical users only want to get some specificic INFORMATION
that meens NOT DATA, they are only 'consumers' with know-how of their
part of the business and their need , but not with enough IT-knowhow to
do it with any tool on their own.
so they need an 'author'.
the selection for this group is important!
how efficient this group can access to data, create the new
information/reports for the 'consumers'.
and how many time they will waste, if some data-structures will be
changed for the existing reports.
We do it with an combination of ready-to-go-framework ('CIM-OLAP')
http://www.antares-mapps.de/cimolap.php?menuid=1&active=CIM%20-%20OLAP
based on arcplans tool dynaSight
http://www.antares-mapps.de/dynasight.php?menuid=2&active=dynaSight
http://www.arcplan.com/en/products/products_Platform.html
so that rapid development is supported by pre-definid standard-reports
+ individual customizing using 1 technology
Regards
Thomas Weiser
mailto:inte...@antares-mapps.de
Ian Boyd
> but those I know (in more than 150 companies) are not able to use any
> query-tool to get the data out of a data-warehouse by querying with any
> tool.
i was afraid there was no user-interface possible.
Damn.
Jéjé
using a query tool like Cognos Reportnet required some admin setup + user
training.
but an end user which is a simple consumer, no training required.
but there is no secret... you have to do some admin job to create an end
user & friendly metadata.
professionnal tools help you in this process, but you can create your own
query tool.
and what's appends in the real world... power users or analysts create
reports for other users.
So if the boss has a question, then the analyst could answer the question
using your query tool.
and more complex questions required ad-hoc SQL query created by a developper
or DBA.
you have to setup a good communication process to answer your users
questions.
"Ian Boyd" <ian.ms...@avatopia.com> wrote in message
news:edfvOXgv...@TK2MSFTNGP14.phx.gbl...
exper...@libero.it
http://cam70.sta.uniroma1.it/TechnicalPreview/Default.htm
we have a DWH loader which allows the user to create visually a virtual
query
from remote servers and to join it on a target DWH.
Attached there is also a Reporting System.
If it does NOT fit your needs. We CAN quickly make it fit (all is free,
see the EDP program).
Pamela
Datatime Team
Ian Boyd
> training.
> but an end user which is a simple consumer, no training required.
Is there any example of the user interface that is presented to the end
user? i cannot imagine *any* user interface. i can't even *imagine* one.
Ian Boyd
news:1128676679.9...@g49g2000cwa.googlegroups.com...
But a lot of the icons sure are pretty.
Ian Boyd
i feel bad saying it, because you obviously spent so much time working on
it, but i have idea what i'm doing. This isn't meant for regular people to
use, right? This is the complicated "back-end" setup that i have to do in
order for some other easy "front-end" user interface to do it's work?
Also, i get a lot of errors, and i also just don't know what the program is
wanting me to do.
And i can't seem to load my saved data source design, and i get an SQL
Server operand clash, but i don't know what two fields could be conflicting.
Yeprin
> Is there any example of the user interface that is presented to the
> end user? i cannot imagine *any* user interface. i can't even
> *imagine* one.
>
Hi Ian, I don't have a solution to your current issue but I'd like to ask
you about something you posted some time ago. I found one of your threads
at Google named "Linked server to as400 - for the love of god please help."
I'm curious whether you ever found a solution? I battled with the exact
same thing for weeks on end 3 years ago. I found a solution that sort of
worked, using ODBC, but now I'm battling a slightly different battle, with
V5R2, IBMDA400 linked server, and translation...
Thanks for your insigths into databases, I enjoy your posts :)
sorry folks if this is off topic...
Peter Nolan
I read this with some interest...the reason being in the early 90s we
used to use a product called Data Interpretation System...the current
version is now called meta5 and you can read about it at www.meta5.com.
This is the ONLY BI software I have seen where you can give it to a
fairly sophisticated end user and they can actually build their own one
off analysis to test a theory and if the theory turns out to be true
then a project might be launched and an application might be built to
monitor it. As an example. One time an actuary client of mine built an
entire death experience application for a major life insurance company
in 3 weeks only having the manuals....zip training....not bad!!
Nowadays our so called 'end user tools' require some kind of
'developer' to build something the end users use. A step back in my
opinion...this is what we see with Business Objects, MicroStrategy and
Cognos who are the clear leaders in this space...
The problem being, if the business users can articulate his/her needs
well enough for an IT person to do some construction then the business
user is probably not looking in the area of 'breakthrough analysis'
which is very unstructured and more 'one off'. This is where the meta5
product is so different.
However, given that developers doing something is the way the world is
going with BI I really like Report Services, for what it is, as
reporting tool. So much so I am building a BI product using RS which
will be 'BI in a box' for small to medium enterprises which is not
dis-similar to what you are talking about.
The idea being to provide a suite of reports in web pages that are the
80-90% of all reports needed for standardised businesses.
So, no, there is nothing out there I know of other than meta5 that you
can give to an end user and they can actually do their own analysis
from scratch and make that into a useful application if needs
be.......to get something that is useful 'out of the box' takes quite
some development work....usually tightly linked to a single
application.......and I for one am having a crack at the idea that this
can be done for multiple source applications in a generic model with
generic reports....we shall see if I am right...LOL!!
Best Regards
Peter
www.peternolan.com
Ian Boyd
> same thing for weeks on end 3 years ago. I found a solution that sort of
> worked, using ODBC, but now I'm battling a slightly different battle, with
> V5R2, IBMDA400 linked server, and translation...
i never did get it to work. It really sucks that "ordinary" ODBC client
applications can query the DB2 no problems, but when SQL Server
uses ODBC to try to talk to DB2, it doesn't work.
i'm *sure* the answer is something like, "SQL Server uses some of the
more esoteric functions in the ODBC API, and the IDB ClientAccess
drivers don't fully and properly implement all of the ODBC driver
spec. So, it's not really our SQL Server's fault, it the fault of IBM's
driver."
Which is a fair point, but perhaps SQL Server should be less agressive
with the API functions it uses to talk to IBM's drivers. Sucks for
Microsoft,
but i think MS would be more likely to ship a hackfix sooner than IBM
would fix their drivers.
> Thanks for your insigths into databases, I enjoy your posts :)
That's quite a different reaction to my posts. Most people find me a grumpy,
short-tempered, pessimistic, cranky, cermudgeon. i like to think that i a
refreshing spring breeze to the groups :)
Ian Boyd
> can give to an end user and they can actually do their own analysis
> from scratch and make that into a useful application if needs
> be.......to get something that is useful 'out of the box' takes quite
> some development work....usually tightly linked to a single
> application.......and I for one am having a crack at the idea that this
> can be done for multiple source applications in a generic model with
> generic reports....we shall see if I am right...LOL!!
Another perfect example:
Yesterday, we got a call from a casino client of ours. They were asked
by our government's anti-terrorism/drug money laundering/big brother
to compile a list of all transactions, from 20040401 to 20050331
where a customer dropped off chips and were given a cheque.
Since i wrote the OLTP system that has these transactions, i had to spend
about an hour coming up with a query that could perform this extremely
ad-hoc query. Originally i started using Enterprise Manager's query
builder to show me all rows - but i couldn't even figure out how to perform
the required aggregation on "Transction Details" wrapped up into a
transaction.
In the ideal world, the customer would have a tool that would let create
these
ad-hoc reports on their own. The software already had about 20 canned
reports
that they run either routinly or rarely. But there is no point in adding
this one-off
ad-hoc query to the software. But, it would be better if they could somehow
run the query themselves. Nevermind the fact that it saves me from having to
do it; they should be able to access their own data however they like.
The only thing i can't figure out is an interface that would let you compile
this particular ad-hoc query. So, if any company figures out one, they'll
be rich.
Sergiy Korzh
Not sure if it can help you but did you aee our Easy Query product family:
http://devtools.korzh.com/eq/ ?
--
With the best regards, Sergiy Korzh.
http://devtools.korzh.com
"Ian Boyd" <ian.ms...@avatopia.com> wrote in message
news:eNoleMU5...@TK2MSFTNGP10.phx.gbl...
. . . . . . .
Peter Nolan
Like I said...the guys at Metaphor did figure it out...in 1983...not
sure if they got rich....I know some when on to found their own
companies with far less sophisticated products and did get rich...there
were bought out by IBM in 1987....but then the product suffered inside
IBM because IBM had a bad time itself from 1990-2000.
So if you want such a front end bad enough and are prepared to pay
plenty for it go to www.meta5.com. ;-)
It is expensive. But it is worth it...
One of my clients made over USD3M extra profit per year based on one
decision from one set of intensive ad hoc analysis on regulatory
changes performed by one person....that decision was to launch a new
product and they went from not having a product to market leader in 6
months.....not bad....;-)
As far as I am aware it is the ONLY system where you can give it to the
end user and they can do everything they need to do without IT
support....I have not seen any other and I have kept my eyes open all
these years...
Peter
Peter Nolan
I have a better example....at the board meetings of one of my clients
we put a meta5 workstation in the corner and when the board met there
was a marketing analyst (note NOT and IT guy) at the workstation.
When a board member asked a question that no-one knew the answer to the
marketing analyst had 5 minutes, count them, 5 minutes, to dig up the
best answer possible....this meant the board could make decisions
during the board meeting rather than have to wait for more research to
be done to answer questions members of the board asked before approving
a proposal.....which is what usually happens....that cycle of Q&A a
board level takes months because in most companis boards only meet
every 4 or 8 weeks.
This, for me, was the ultimate in ad-hoc questions that were real
decision support...at board meetings the board considers decisions in
the millions or tens of millions of dollars range....in Australia board
members have a legal duty to due dilligence before approving such
investments....that is, they must be able to show, if asked by the
regulators, substantial evidence that the decisions taken were well
founded at the time the decision was taken. What we did was the
ultimate in high value decision support as far as I am concerned....a
real shame it did not catch on!! Maybe we would not have the burden of
SOX if senior managers at large companies took the time to have such a
system in place in their board rooms.....(like Enron and Worldcom!!)
Peter
Ian Boyd
> sure if they got rich....I know some when on to found their own
> companies with far less sophisticated products and did get rich...there
> were bought out by IBM in 1987....but then the product suffered inside
> IBM because IBM had a bad time itself from 1990-2000.
>
> So if you want such a front end bad enough and are prepared to pay
> plenty for it go to www.meta5.com. ;-)
i tried to view their demo "movie" was their propritarity player wouldn't
play, and i lost interest.
Maybe i'll harder to let a company convince me that i should consider
looking at their product.
Peter Nolan
hhhmmm....you are correct...I just tried the demo myself on a different
laptop and it is not starting......I'll let them know....they use webex
which is pretty widely used....but it does not seem to be starting.....
Best Regards
Peter