December 25, 2010 at 3:53 am
Have you ever heard of or used Wherescape RED to do the data warehouse developing? The fact is it is so popular in New Zealand, and maybe already hi-jacked the whole NZ BI industry. However, after I have been forced to use this product to do the MS SQL DW and database project for months, I found it has nowhere can compete with MS SQL + MS visual studio itself. AS an ETL tool, SSIS is far more flexible and scalable. As to version control, source safe or team fundation is far more sophisticated. in terms of documentation, the BI Documenter is certainly much advanced.
What I also found is that Wherescape is also not able to keep up with the SQL server new functionalities? The autogenerated sql script is good on standardized point of view but largely diminished the developer's SQL skills.
Indeed, I was totally lost why we need it under Microsoft SQL evironment? Could anybody give me your thoughts to clear my doubts?
December 26, 2010 at 1:36 am
Hi There,
WhereScape does do things differently than what you are probably used to, but once you get used to it you should be more productive building a data warehouse than using the MS suite. The key thing to get your head around is that RED treats the data warehouse as a holistic thing rather than the fractured way you are probably used to doing in MS tools. For example WhereScape will generate a data warehouse table, the processing to populate it (in SQL), indexes, version history, workflow and documentation in one standardised technology, one place, one tool. Change management is assisted by regeneration of all of the above should a change be made and guided by impact reports generated from the metadata. The MS suite breaks things into silos - ETL in SSIS, table design in something, manual index and table creation in SMSS, documentation in Word (if at all), workflow in SQL agent jobs, cubes in BIDS with nothing really tying the parts together.
As far as processing goes SSIS might have more tasks to choose from but in many situations it is not as fast as SET based processing in the database, is harder to troubleshoot, certainly harder to change (with no real impact analysis) and provides no operational framework. Newer versions of WhereScape RED will support newer database features.
In NZ people generally need to do more with less - that is why WhereScape is popular. I hope that this helps - BTW have you been trained in WhereScape?
December 26, 2010 at 3:43 am
Hi,
Thanks for your reply. But frankly, it still didn't convince me that WhereScape is better to do the DW job in MS environment. I even can't agree with all those deliberate load and staging tables for data transformation purpose and data lineage traceability. And using the cursor for the SCD table population is not set-based at all.
Under MS platform, we use the visual studio to manage everything in one solution, Database, SSIS, SSAS, SSRS and other assemblies. Database schema change is far more easier to manage, control and deploy under VS.
RED metadata tables and SPs sitting inside the production DW database are also making me feel very uncomfortable. What is the mobility of the DW database, without RED it seems useless. This bounding is shocking. The using of ODBC connection for application is also rendering it is clumsy to mass change developer's desktop while a server connection is changed.
Indeed, I'm not saying RED is a bad product. Perhaps, it is still a great product for developing DW in non-MS based environment. At least to me, using RED on top of the MS SQL to developing SQL server based DW is funny. Sadly, without RED two-thirds of my colleague’s even don’t know how to develop a DW, which maybe the greatest value of RED and the status quo of NZ BI industry.
December 26, 2010 at 1:16 pm
No worries. I believe that worldwide most WhereScape customers are on Microsoft. I can understand the ELT approach is different from what you might be used to. ELT uses SQL as the transformation language, and on MS WhereScape RED generates all the T-SQL code for moving and transforming data in the form of stored procedures. This code is optimised out of the box, but this can be edited / augmented in WhereScape and overwhelming developer feedback is that SQL is the prefered technology for developing, troubleshooting and performance tuning data processing (even when using SSIS) because it is so mature and well understood.
At the moment dimension processing does use cursors, but the standard code prunes the cursor using a SET based exclusion reducing cursor processing to only new or changed data - and I have seen it used without modification on large dimensions (10s of millions of members) without incurring a performance hit. Of course you have the ability to change this code if you want to. The next version of WhereScape will provide a SET based option for SCD processing.
WhereScape does use ODBC for reading / writing meta data (only used as last resort for loading or processing data) - and in a team environment it is less confusing if a standard set of ODBC connections are used. All WhereScape code / objects are built in the database, which means these objects can be interrogated using SSMS - or any other database tool.
I know that VS / BIDS provides a common development environment for the MS tools, but the big difference with WhereScape is that the meta data is shared. SSIS doesn't use the same meta data as SSAS or the database - they are developed separately, although in the same IDE. What WhereScape does is relate objects together through the shared meta data - indexes with tables, tables with tables (eg in a star), processing with tables, cubes with stars, workflow with tables, etc - and this provides enormous benefits to developer in WhereScape when making changes or iterating in an agile project.
I am not saying the MS tools are bad - they have good interfaces, but i believe WhereScape provides a more integrated development environment because it has been built to develop all aspects of the data warehouse as one process.
Let me know if you want to catch up off-line. Have a merry one..
January 4, 2011 at 8:39 pm
Thanks NoBIBummer, you make me feel that I'm not alone. I was fed up with Wherescape.
January 5, 2011 at 4:26 am
I've been using SQL Server to build data marts and data warehouses since 1995 and have used every version since version 4.21.
Since 95 I've used other databases for marts and warehouses too.
I was first exposed to wherescape in the US in 2003 on a project at a bank on the west coast.
I loved it instantly!
Having used lots of ETL tools (most of the big ones and some others), I can say that wherescape is a little different.
It can take some getting used to, especially if you've allowed yourself to be clouded by the ETL approach to the world.
This is because it's not an ETL tool. It's a development tool for speeding up the delivery of your warehouse and reducing effort when you have to make changes later (and you always have to make changes later - if your warehouse is successful).
What wherescape does is let you build the things you'd write by hand many times faster than if you were writing them by hand.
I remember writing code by hand, having to write code to schedule my warehouse or mart and having to surf through 100,000 lines of someone elses code to make changes. If I use wherescape I don't have to do this any more. The same is true with an ETL tool, but for different reasons (mainly because there's an extra level of abstraction and I don't get to see the true code with most tools).
I suppose my main point is (based on my experience and observations), if I was to rank approaches by speed of delivery (and everyone building warehouses should be focussed on speed of delivery - delivering before business change occurs and hence before business requirements change), I'd have to say:
1. wherescape / rapid development / agile
2. hand coding
3. classic ETL
If I was to do the same for ease of maintenance:
1. wherescape
2. classic ETL
3. hand coding (unless the code was exceptionally well written and documented)
I said above, I fell in love with wherescape instantly. I've outlined my main reason above, my other reasons are:
- you can see, change and tuned the real code without breaking anything
- there are no black boxes
- contrary to what you've said above, they do seem to keep up with new database features (eg when table partitoning was added to sql 2005, I remember wherescape releasing a version to support this great new feature BEFORE sql 2005 was released! and I can remember other times this has happened)
- it's easy to learn how to use it and become an expert in it, especially if you go on the training
- ...
Finally, I have to take issue with one point you made NoBIBummer. Warehousing is warehousing. A true data warehousing expert is database independent. They will understand the strengths and weaknesses of all major databases. They may even have their own personal favorite database. But to say "a great product for developing DW in non-MS based environment" is intellectually dishonest. How can an approach be ok everywhere except for SQL Server? Are you saying SQL Server 2008R2 is missing major warehousing functionality that all other database have? I'd have to disagree with you on this too, if you are...
July 22, 2011 at 11:50 am
Thanks Lance. I totally agree with you. I thought the other poster's comments were more of a political statement than an honest comment. Experienced DW professionals will say that "it's not the art it's the artist", and Wherescape is another toolset that complements the artist who knows the arts. There is no one tool for everything--the best is experience and what's between the ears. Over time, you realize how to work smart, and that's up to you. YMMV.
Cheers.
August 17, 2011 at 1:32 am
Lance,
I have to say that I've been building DW's for more than 10 years in both MS BI toolset and Wherescape and have to agree with you on this one. Wherescape was specifically built for ELT/ETL, MS just shuffled DTS/SSIS into that ETL/ELT hole they had in their suite.
NoBIBummer,
I've been there so I understand that it's a different interface and once you get used to dragging task by task in DTS/SSIS, moving to RED where you drag tables directly and most SQL is automatically done for you, takes some time to get used to it and see what RED does 'under the hood'.
Regarding whether it makes sense to use RED in a MS environment, I think it complements beautifully with SSRS and SSAS, you get in my opinion the best of both worlds, the strongest of the MS suite (DB+OLAP+Reporting) with a much better ETL/ELT solution than SSIS. SSIS is great for some tasks but managing a 100+ package DW environment is practically impossible, just wasn't built for that. Trust me, been there, and trying to coordinate all the dims to process first and then the facts and so on and ending up in spider web type approaches that don't escalate well.
February 11, 2012 at 2:44 am
i have a question guys,
Wherescape obviously uses the SQL database engine, so an organisation end up paying two license one for the SQL Entreprise edition + wherescape red. In addition wherescape does not have much material online like SSIS as not many people have used it , for example it's market share is very small in australia, compared to the SQL BI suite. For what i have read MS BI suite can do most of what wherescape can. Wherescape obviously follows the kimball approach. Well the kimball group have written a whole book on using the Microsoft SQL BI Suite 2008 R2 to build a successful DW/BI solution.
did any one conduct an official comparison between the two tools in terms of functionaly/cost/ease of use etc??
August 1, 2012 at 4:12 am
As a DBA responsible for a 4TB DWH, WhereScape scares the shit out of me.
Where adding a single column to a table in the Northwind DB during a demo is easy to do, doing the same on a 100GB fact table will block your system for hours. And I'm afraid that using tools like this to be able to be really agile, will allow these things to happen ad hoc, without a DBA even knowing.
August 2, 2012 at 4:05 am
Hi Geert Vanhove,
Adding a column to a table locks the catalog and if you are going to do this in production on a very large table, then you need to schedule it to happen outside business hours/etc and cater for it as it is a major change. The problem with this operation taking so long is a database issue, not the client itself, whether its Wherescape Red or TOAD or SQL server management studio.
You would be crazy to think that the intention of the 'drag-and-drop' of a column functionality in Wherescape RED is meant for doing this willy-nelly in a production environment on very large tables.
If your development environment actually has 4TB and 100GB tables then probably would be a good idea to get a smaller dataset for that environment and get a UAT environment close to the size of prod where you can do performance testing there and not 'open to developers' to constantly perform operations that lock the catalog.
Hope that helps.
Cheers
August 22, 2012 at 9:33 am
Very good discussion. Please take a look at LeapFrogBI. Many of the concerns mentioned in this thread are the motivation behind creating this toolset. http://www.LeapFrogBI.com
- SaaS application (zero footprint browser interface)
- Metadata driven data mart development
- Many times faster than traditional ETL development
- Creates SSIS packages
- Cursors are not used for dimension loads
- Extend functionality in any way using native tools: Visual Studio BIDS / SSDT
- Much higher value proposition than competing products
- No trace left behind. No dependencies after deployment.
- Build a single mart or an entire warehouse
March 27, 2013 at 10:04 pm
Geert Vanhove (8/1/2012)
As a DBA responsible for a 4TB DWH, WhereScape scares the shit out of me.Where adding a single column to a table in the Northwind DB during a demo is easy to do, doing the same on a 100GB fact table will block your system for hours. And I'm afraid that using tools like this to be able to be really agile, will allow these things to happen ad hoc, without a DBA even knowing.
Understand your concerns. And agree with you that WSR should not be used to develop this 4TB datawarehouse.
Nevertheless, I also understand the pain of another team, which would be developing a dozen 1-2gb datamarts (star-schemas) (Inmon model), with a tiny cube attached to it and hundreds of small business rules applied to tables, that source information from this 4TB DWH. The pain will be associated with agility of the projects, quick demonstrability, update of documentation, many many tiny things, which would make their live a nightmare shall they use traditional hand-conding, ssis/bids, etc.
WSR is a savior in this case. The WSR ream may not be even reporting to the DWH manager, but be its own BI stream with a a bunch of visualization tools such as clickview, tableau, excel, sharepoint, etc.
I've seen such structure in large banks, where 4TB datawarehouses are being looked after by the DWH team, but the BI function (including the datamarts, etl, anything else) belongs to the BI team(s)
M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM
April 3, 2013 at 6:42 pm
I have used several ETL tools over the years and between WSR and SSIS, I would prefer SSIS.
WSR does have the advantage of the diagrams that it can draw as sort of documentation, that is about all.
With SSIS you can have optional streams of flow, which I don’t see with WSR.
With SSIS you can utilise the .Net framework where things get tricky via the script component, don’t see such luxuries in WSR.
SSIS requires more discipline and you can create a big mess if you are not careful.
I don’t know why NZ is so hooked on WSR, with SSIS you get a better tool bundled together with SQL server, already paid for, then why go and pay some more money for another tool that is only half as good. In this case it is doing less with more.
April 4, 2013 at 11:47 pm
Hi There, ETL functions such broadcast, split, filter, lookup, join, parallel workflow streams etc are all built into WSR. They are implemented differently though as SQL. If you have used ETL tools for a while, including SSIS, then the concept of doing things in WSR as ELT processing can be tricky to translate - but most (not all) ETL functions can often be implemented more efficiently in SQL using SET based operations. When something that SQL is not good at (which is usually the exception not the rule) eg fuzzy logic, then you would make a call to an outside utility or script from WSR.
Have you had training in WSR? Perhaps that will make life easier.
I don't think ETL developers don't buy WSR. But organizations do. They buy WSR because it aids developer productivity and consistency - it brings together the generated processing (SQL), tables, indexing, operational tasks, workflow, documentation, etc as a single development task. A much wider scope than an ETL tool.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply