 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / General / Data Warehousing / November 2005End-user software to query a warehouse?
So let's say i manage to construct a warehouse. And let's pretend i can 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? The end-users will never need to know anything about fact tables and dimension tables. Try this for using Excel to query a cube http://office.microsoft.com/en-us/assistance/HP052747511033.aspx Guilbert > The end-users will never need to know anything about fact tables > 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. This is a useful site I just found "Software products to work with Analysis Services" http://www.mosha.com/msolap/util.htm Guilbert > "Software products to work with Analysis Services" > > http://www.mosha.com/msolap/util.htm 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. if you need to allow your users to create their own SQL queries using an 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. > So let's say i manage to construct a warehouse. And let's pretend i can > conjure a system to constantly bring in new data from the "live" system. [quoted text clipped - 35 lines] > > Has the computer industry invented such a user interface yet? > if you need to allow your users to create their own SQL queries using an > 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." One appraoch I took a couple of years ago: - 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 ;-) >> if you need to allow your users to create their own SQL queries using an >> easy to use interface, look at Business Objects & Cognos ReportNet. [quoted text clipped - 11 lines] > > "It turns the caps-lock off, and it enter's it's password again." Basically what you are describing is just a basic Excel pivot table? >> if you need to allow your users to create their own SQL queries using an >> easy to use interface, look at Business Objects & Cognos ReportNet. [quoted text clipped - 11 lines] > > "It turns the caps-lock off, and it enter's it's password again." > Basically what you are describing is just a basic Excel pivot table? 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! > 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! 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:internet@antares-mapps.de > 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 was afraid there was no user-interface possible. Damn. all is possible, but "all" has a cost! 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. >> I don't know your end-users. >> but those I know (in more than 150 companies) are not able to use any [quoted text clipped - 4 lines] > > Damn. > 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. 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. > 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... > 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... 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 :) I think we can get close to what you ask: 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 >I think we can get close to what you ask: > http://cam70.sta.uniroma1.it/TechnicalPreview/Default.htm [quoted text clipped - 10 lines] > Pamela > Datatime Team 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. >I think we can get close to what you ask: > http://cam70.sta.uniroma1.it/TechnicalPreview/Default.htm [quoted text clipped - 10 lines] > Pamela > Datatime Team But a lot of the icons sure are pretty. Hi Ian, 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 > 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 [quoted text clipped - 4 lines] > 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. Hello, 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 . . . . . . . > In the ideal world, the customer would have a tool that would let create > these [quoted text clipped - 9 lines] > this particular ad-hoc query. So, if any company figures out one, they'll > be rich. Hi Ian, 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 > 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 [quoted text clipped - 4 lines] > 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. Hi Ian, 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 Ian, 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 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.