Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

MS SQL + MS Visual Studio VS Wherescape RED Expand / Collapse
Author
Message
Posted Saturday, December 25, 2010 3:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 7:20 PM
Points: 5, Visits: 158
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?

Post #1039196
Posted Sunday, December 26, 2010 1:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 11:44 AM
Points: 15, Visits: 83
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?
Post #1039238
Posted Sunday, December 26, 2010 3:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 7:20 PM
Points: 5, Visits: 158
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.
Post #1039242
Posted Sunday, December 26, 2010 1:16 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 11:44 AM
Points: 15, Visits: 83
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..
Post #1039281
Posted Tuesday, January 4, 2011 8:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 15, 2011 11:31 PM
Points: 1, Visits: 16
Thanks NoBIBummer, you make me feel that I'm not alone. I was fed up with Wherescape.
Post #1042791
Posted Wednesday, January 5, 2011 4:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 9, 2011 1:19 PM
Points: 1, Visits: 9
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...
Post #1042939
Posted Friday, July 22, 2011 11:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 22, 2011 11:45 AM
Points: 1, Visits: 0
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.
Post #1146827
Posted Wednesday, August 17, 2011 1:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 2, 2012 3:50 AM
Points: 2, Visits: 11
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.
Post #1160969
Posted Saturday, February 11, 2012 2:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, September 22, 2013 5:23 PM
Points: 1, Visits: 43
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??
Post #1250683
Posted Wednesday, August 1, 2012 4:12 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 18, 2013 6:53 AM
Points: 243, Visits: 101
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.
Post #1338407
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse