What front end tools do you use?
-
My workplace is finally in a position to start looking at data warehousing and implementing BI on a large scale and since we don't have a BI person, the DBA team is being asked to investigate options. So I'm hoping someone can help me out.
What BI tools do you use? Do you actually use SQL Server Analysis Services on the back end? What about the front end (dashboards, etc.)? What tools have you used in the past and what tools do you use now?
As far as the front end tools, do you have horror stories or "it saved my butt" stories for the ones you do use?
I'd like to hear from unbiased database people (as opposed to the straight-out-of-Dilbert vendor reps I've been talking to). So, please, chip in with your two cents.
Thanks in advance for any thoughts or assistance on this.
-
Good day.
We have been using the Cognos toolset for the past 5 years on a SQL backend. Before that, we used SAS, and are currently investigating the use of SQL2005, QlikView, Proclarity ect.
If cost is a factor, SAS and Cognos is rather expensive, but works well with large datasets.
The Cognos products have served us well, but the upgrade from our current version to Cognos v8 could cost us the same as a redeployment of front -end tools. Web-based reporting, dashboards, Olap analysis and ad-hoc reporting by users are available. security is good. They have a support website with knowledge base for Query resolution.
Qlikview has a good graphing and reporting capability, but models could be too distributed for our environment. It relies on memory to do data analysis and uses it's own data repository. Does not use olap, but speed of analysis is good. Customisable by using VBscript. Data imports relies on combination of VB and SQL coding.
Proclarity has recently been bought by microsoft, and might soon be part of the BI toolsets supplied with SQL2005.
We currently use MS-DTS, Datastage (IBM) and Decisionstream (Cognos ) as ETL tools. These could all be replaceable with Microsoft's SSIS that comes bundled with SQL2005.
We are looking at SQL2005 with analysis server ect due to the lower cost and greater ease of interfacing with our current system.
Regards, Business Intelligence Project Leader - South Africa
-
My company just changed to use Hyperion.
In my old company, the developers developed in-house reports using Actuate report (terrible tools), Active X report (OK) and microsoft reporting service (now it is part of SQL Server 2005).
Depending on your users which type of reports they are looking for, there are so many vendors.
-
I forget to mention we are using SQL Server 2000 and we use DTS and stored procedures for ETL tools.
My old company spent millions of dollars buying Ab inito. It was a great tool. But whatever it does, DTS could do the same thing. The DBA also made a comparison using SQL Server 2005 SSIS vs Ab inito. The response time is the same. So if you want to spend money, you can consider Ab inito.
-
DanielG's feedback was very helpful for me. thankz
-
We are also looking at different products but before building a data warehouse (which, for us, would take a long time) we are considering using Crystal Business Intelligence which is similar to a virtual warehouse. This "should" be able to be set up more quickly while a real data warehouse is being built. We've not purchased it yet, but it might be another option for you.
-
We're currently using SQL Server 2005 for our production DBs and SQL Server Reporting Services 2000 for our operational reports (haven't upgraded to SRS 2005 yet cause we're still trying to get away from MS Access reports).
If I'm understanding the general flow of this conversation correctly, people who have some of the older SQL Server versions tend to use third party tools and those who have 2k5 tend to use Analysis Services, SSIS and SRS. Right? If I'm wrong, why did you choose a third party tool over what was available in SQL Server?
What about these other products that you've used? Were you around when they were implemented? If so, did they require you moving your data from your database to their data warehouse before you could implement the presentation layer? Or did it hook directly into your data warehouse?
Has anyone created their own dashboards from scratch? Have you been involved in the creation of the warehouse or BI solution? How difficult was implementation and what problems did you run into that you would recommend avoiding?
Oh, yeah. I've got a lot of questions. I'm very much the BI rookie, so any help or reference material would be appreciated.
-
The reason my company chose Hyperion is it is not just a reporting tool. It is financial management - a planning tool for finance people, this is the main reason why.
We still use SQL Server as our main data warehouse, DTS (SSIS in SQL server 2005) for ETL tools.
-
We haven't decided on the type of datawarehouse yet because we also need data from Oracle and AS/400 and the developers from those systems are unfamiliar with SQL Server Analysis services. I feel that since we are short-staffed selecting the right product with the shortest learning curve needs to be considered. I, too, welcome suggestions from others. 🙂
-
We also have Oracle and AS400, we use SQL Server 2000 DTS to extract data from Oracle and AS400 and put them in SQL Server 2000 dimension and fact tables. Then you can build the mutli-dimensional data warehouse using analysis service or other tools. Most tools are compatible with SQL Server.
First of all, you have to work with the users to identify what kind of data and what kind of report do they need. Then you need to design the data model. It will help you to find out which tool to use.
-
For front ends (ie client tools to view cubes in particular), a lot of people love Cognos and many Analysis Services shops use ProClarity. Look for Proclarity inspired functionality to appear in server based products coming out of microsoft in the near future. I would also very seriously consider the new Office 2007 offering re: what Excel, Sharepoint and other office tools (check out what Visio can now do). If you're an Office shop then the upgrade which will probably 'have to happen' at some point may happen faster with the BI functionality sell.
While the comments re: Hyperion are true (it is more than just OLAP reporting, it's got budgeting, forecasting, planning etc , typically the items now grouped under the term BPM - Business Performance Management), Cognos also has positioned itself in this space and then there are niche players who offer one or more of the BPM tools. Watch again for Msft to come out in the near future with their own suite that allows business users the same (or similar V1) functionality (see link).
As far as building an EDW or datamart, whereas int he past for a 'real' ETL you normally called Informatica or DataStage, the latest incarnation of DTS (now called SSIS - Sql Server Integration Services) is extremely good -> good = fast & extendable, two things that you'll quickly find you need if you're working on complex or large projects/datasets.
To balance out all of the Msft coolaid, if you're looking for a solid BI suite (ie in use by some reasonable names in the private sector and prob govt (ok, i didn't check for them) that is absolutely, completely FREE (yep, yessum it's open source and free) check out Pentaho (http://www.pentaho.org). This platform (runs on windows plus *ix varients) has ETL, Reporting, OLAP, 'spreadsheet services', dashboards and data mining (did i mention it was free?). There is a Professional version also for 'larger-scale deployments' but definitely talk with them first to determine if that's what you need. For any microsofties, the interface (in fact the overall approach) is a little different than perhaps you've been used to working with. Just FYI, it also uses/supports MDX as it's query langauge which means if you've been using AS then your query skills won't have to be modified (too much
).One last comment on the 'virtual warehouse' approach, maybe get your potential suppliers/vendors to do a 'bake off', I have heard of some data sets of ~6M rows not performing overly well in a 'virtual' cube scenario 9don't believe it was Crystal as Linda mentioned but can't be sure) where as we routinely put 50M++ rows (we recently did a 1.2Bn row build) in to AS 2005 and it eats it up.
Steve.
-
I belive my boss wants something we can present to our VPs besides just basic reports. It's not just about the graphical "nifties". We have the problem of having too many reports for the higher ups to want to go through. They want something they can glance at and get financial and product trending, do some quick analysis off of (are we making money or losing money?) and then make decisions based off the high-level view.
An additional concern is that they want to look at this stuff before they go into meetings so they can question the business unit more closely on practices and procedures. And lastly, most of our data is going to be going into the "warehouse" on a daily basis (only 24 hour lag time).
Thank everyone for the suggestions you've given me so far. If you have anything additional to add or any changes to recommendations based on what I just posted, please let me know.
-
You're definitely looking towards Dashboards (possibly scorecarding but that's another topic) for your senior management. I would just add the following points to look for/be aware of:
- even thoug your current requirement is high level exec reports, be aware that if mid-managers are being toasted or roasted based on this data, they'll want access to it also but more likely at a more detailed level to allow for enhanced decision making and earlier problem identification. So make sure what ever you implement will support all levels of current and anticipated requirements (ie a point solution, say a standalone dashboard product will possibly cause more trouble than good as you'll have to rebuild/rewrite using something else to produce the more detailed data for mid-management
- re: looking at stuff before meetings - whatever product you go for, ensure you check out i's printing capabilities. Althugh many execs are a lot more aufait with viewing on screen, there is often a need for mid-high quality print copies of what they're viewing on screen. Some web-based products don't print as well as they display.
- Also consider (maybe raise the question) of offline access. With everything going web-based this is quite often a little harder to achieve (this is compounded by the hige incrase in data volumes too). Both Cognos and Excel allow for 'local' cubes to be built and viewed when offline, I'm sure there's others that support this too.
If you're really starting out from scratch, if you haven't had them already, have a series of first round presentations from vendors and ask them to show the 'full' suite/offering. This will let you see where you 'could' get to. using this information, look at where you need to be now and where you like to be in 12 months and cull out vendor who may only be point solutions or others who require implementation of everything at once (big bang).
HTH,
Steve.
-
Hey Brandie,
What you're describing is really in the vein of KPIs, Scorecards and Dashboards (ultimately Performance Management). Sounds like the VPs want to set forth a strategy and then insure that the strategy is being met with outliers (good and bad) rising to the surface.
If that's the case, I would consider those technologies that best leverage SSAS KPIs and render them in an actionable way. Microsoft Office Sharepoint Server (MOSS) and WSS 3.0 offer some of these capabilities married with the next generation of Microsoft's Business Scorecard Manager and their newly acquired ProClarity software (to be called Performance Point). Marrying these capabilities with some structured drill down analysis is key and the ties between MOSS, Performance Point and Reporting Services shoudl be pretty good.
There is a small software company called Cizer that seems to still have a niche play alongside of these technologies and they are probably another one you might want to explore.
There are other front ends that consume SSAS to varying degrees (Business Objects, Cognos, Strategy Companion, Panorama and others) but in many of these cases their parity with the fully SSAS feature set is just not there, yet.
All the best,
Trey
Trey Johnson | Chief Business Intelligence Architect | Cizer Software (www.cizer.com)
Who? - Cizer - http://www.cizer.com/about.htm - Blog - http://www.sqlserverbi.com/
What? - Products enhancing Microsoft Business Intelligence - http://www.cizer.com/products.htm
Wow! - Empower your Developers.... NEW Drop In Reporting - http://www.cizer.com/cnr-drop-in-reporting.htm
How? - BI Training - http://www.cizer.com/training.htm - Cizer Solutions - http://www.cizer.com/solutions.htm -
Thanks, but that's my problem. I wouldn't be building the data warehouse becuase I don't have the time (being the only DBA), and the 'developers' are not familiar with DTS or Analysis Services. Gotta work with what we've got and what we know, I guess.
🙂
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply