WhereScape Builds data warehouses right, now

The current release of WhereScape RED, version 6.5.5, will be the last release of RED supporting the following operating systems:

· Windows 2000
· Windows XP before Service Pack 3
· Windows 2003 Release 1

It will also be the last release of WhereScape RED supporting the following databases versions for metadata repositories and target data warehouses:

· SQL Server 2000
· Oracle 8.1.x and 9.0.x
· DB2 9.1 and 9.5
· Teradata V2R5.x and V2R6.x

This will make the minimum supported Windows version for RED:

· Workstation: Windows XP SP3
· Server: Windows 2003 R2

And the minimum supported database versions for RED metadata repositories and target data warehouses:

· SQL Server 2005
· Oracle 9.2
· DB2 9.7
· Teradata 12

As is the case now, source systems using older versions of these databases and any other databases will still be accessible using RED.

October 21, 2011 - Mercedes-Benz World, Brooklands, on the site of the world’s first professional motor sport race track, was the historic setting for the WhereScape RED product test drive.

Situated in the top floor of the AMG suite, eight hand picked delegates, representing WhereScape Europes’s prospects and business partners, were set the challenge of completing a section of the WhereScape RED Enterprise Data Warehouse training course in only 90mins.

Some healthy competition surfaced as the attendees fuelled by breakfast, and the prospect of winning podium prizes, started to build a data mart from scratch. During the product test drive Senior Business Managers connected with their technical roots, and Technical Managers discovered how their teams could design and build data warehouses 10 to 100 times faster.

As the finishing line drew near, the pit crew of Terry Mooney and Paul Watson-Gover (Senior WhereScape Consultants) ensured that all drivers were prepared for their final exercise. Source tables were loaded, 3NF structures were linked and populated, all ready for a final dimensional transformation and cube generation.

At the chequered flag Richard Noble, Head of Consulting, declared a dead heat, and all attendees were treated to an AMG test drive round the Mercedes-Benz World test track.

The real result – WhereScape RED delivered with great speed, agility and ease… much like the race tuned Mercedes AMG!

RED - Speed, agility and ease of use

October 4, 2011 - A week after the New Zealand Embassy event, it was time to head back into London, to Olympia this time, for The Healthcare Efficiency Through Technology (HETT) conference. This event gave healthcare professionals a chance to learn about and discuss the technological approaches that can help them to achieve greater efficiency and effectiveness in their organisations.
 
This was a very well organised event, it turned out to be one of the busiest so far! There were lots of WhereScape RED demos and business conversations with potential customers and partners in the Healthcare sector.
 
We would like to extend thanks to all the people we spoke to and look forward to the next event.

A busy week for the WhereScape Europe team kicked off on Thursday 29th September. WhereScape Europe hosted a breakfast workshop in central London, and the venue for this auspicious event was the Penthouse of New Zealand Embassy.  What a venue it was, with fantastic views over London, as the photos below prove.

A friend of mine founded a software company a few years earlier than we did.  His definition of “making it”  was when he got to fly in the big planes – visit customers in larger organizations in the major centres….with airports people actually want to go to.

On his definition, WhereScape has definitely “made it”.  Over the last couple of years we have seen more and more leading organizations engaging with the concept of building data warehouses faster, and subsequently utilizing WhereScape RED. 

As the concept becomes more prevalent we find we are no longer just selling to strong willed individuals who want to make a difference, but with full on buying committees.  And data warehouse architects are on those committees.
 
In our mid-market customers the data warehouse architect can be a part time role, taken on by one of the team alongside their other duties.   In the larger organizations the data warehouse architect is a key person or are key people.  In all cases they are responsible for the long term viability of the data warehouse.  They need to ensure that short term decisions don’t have unintended long term consequences, they need to be aware of technical debt, and they can be called upon to be the conscience of the data warehouse team: to say no, or watch out, when everyone else is saying go.
 
There is a very real problem, however, with the data warehouse architects, and it stems from the fact that they are often really smart people.  They go to the conferences and listen to the analysts and they keep up to date with the new topics and advances.  Somewhere in their job description it says “make sure you give vendors a hard time”.  I am sure some of them are paid on the number of times they can trip a vendor up, with extra bonuses for cruel and unusual questions.

Given their reputation, our well-worn path was to go around them; who wants to be given a hard time every time you go to meeting?  It turns out that this wasn’t too hard.  We are often endorsed by strong willed business users who are adept at ushering software they want through the purchasing process.   And the architects were more concerned with bigger picture questions than worrying about getting value from what was already in place.

Then a funny thing happened.  We started to be invited to meetings by architects. They wanted to talk to us.  They wanted to engage with us so they could offer more to their constituents. Not all of them, but often the ones from the larger companies (near the big airports).

We would like to put this down to our superb marketing, wit and charm.  A more likely reason is that the rules have changed for the data warehouse architect.  They have widened the interpretation of architecture to include time to value.  We certainly owe a hat tip to the agile data warehouse and agile business intelligence movement here – they have been leading the call for change.

I recently met the chief data warehouse architect for a global financial institution.  His story was typical of what we are seeing now.  Their new data warehouse strategy included a section on being more responsive and delivering faster to the business – and he wanted to know how we could enable this.
 
This is a fundamental change, and a great sign for our industry.  Our architectural discussions were previously based around compliance and future strategy.  Now the architects are talking to us about value.  And the architects are the right people to be having this discussion.  Considering value up front, giving it a seat at the table, means it can be built into processes, and can influence other decisions.  This can only be positive for business intelligence, and specifically the data warehouse.

Generally we don’t publicise our charitable donations – it is the right thing to do, and that is enough for us.  Now, however, we are after all the exposure we can get.

This year I will be running the ING New York Marathon to raise funds for the Memorial Sloan-Kettering Cancer Center.  And anyone can show their support.

Given some recent events, we have again chosen a cancer charity.  Memorial Sloan-Kettering Cancer Center has an excellent reputation – charity watch have them as one of their top rated charities (A rating) .  With Fred’s Team they also have a long association with running, which is popular pastime around the various WhereScape offices.

The marathon is November 6th, and we will be raising money up until mid October.  I will personally be paying for all travel and accommodation to and from New York, as well as donating.  WhereScape will also be donating – and we would also really like your help.

Giving is easy: we have just kicked off a fundraising page - please note that all donations are in US dollars.  Scott Humphrey has also graciously said that we can be the official charity for this year’s Pacific Northwest BI Summit.  But you don’t have to wait!  This is just like voting – give early and give often. 

Let’s see what we can do.

It is now just over a week since we launched WhereScape 3D, and the response has been phenomenal.

It is available now from the WhereScape web site, free of charge, for an extended beta period of indeterminate length (think gmail not Windows – we are really interested in your feedback!)

We choose to announce the product at the Boulder BI Brain Trust in Boulder, Colorado.  And it wouldn’t have been a (release) party wihout a cake:

Thanks to Richard Hackathorn for the photo, and Claudia Imhoff for the modelling (I would credit Jason and me in the background but I’m not eactly what for)!

To release here was an easy decision – the BBBT brings together some of the best critical minds in our industry.  We had briefed them previously on our thoughts about the new product, and they had provided some great feedback.  Eighteen months later it seemed appropriate that they should be the first people to hear about and see the new product.

At WhereScape our product vision has been driven by our fixation (some would say obsession) with “fast”.  We think data warehouses take too long to build, and once they are built we think they take too long to change. 

When we look at the time taken on a data warehouse project, we break it into three buckets:  before you start (we call it plan), during (we call it build), and after (test).

WhereScape RED does exactly what we designed it to do:  radically compresses build time, cost and complexity.

This increase in speed is highly valued by our clients, but the fastest build time in the world doesn’t matter if the planning is flawed or incomplete.  And unfortunately this is often the case.  Symptoms of this include unexpected “discoveries”, underwhelming deliverables, witch hunts, team changes, or the dreaded “lets bring in the (next set of) consultants”.

WhereScape 3D addresses these problems.  It supports the planning process – something that happens (formally or informally) on all projects, but a process that is not well supported by software, perhaps because of its complexity.

Planning can mean a lot of things.  A simple view is:

But this does not reflect the true story.  You can break any of these areas down to reflect more and more complexity:

In addition we often prescribe (or have prescribed) the input context:

…and sometimes even the output context:

With all these variables (and we have only touched on a small number of them here), the planning process can become very complex very quickly.

This was the challenge with WhereScape 3D – can we come up with a software product that  practitioners will use to help deal with this complexity?

We decided that the key was flexibility.

The key construct in WhereScape 3D is the model.  A model consists of entities, which in turn can have attributes.  Each of these objects have a huge amount of configuration options.  For instance we can have a logical model of a source system, which can be displayed as:

We can also have a model that is an organization chart, where the entities could be interviews or notes.  To WhereScape 3D they are the same (model, entity, attribute), but they can be made to look quite different:

Models can contain many different levels of information, and can be related.  For example you can have a logical and a conceptual model of the same area, and they can be related.  Models can also be combined in real world ways, at the same time.  For instance we could have a logical model related to a conceptual model (what we call an up and down relationship), and the logical model could also be associated to a source system (left to right relationship).  And we can extend this further – the conceptual model could be associated with interview notes displayed using an organization chart metaphor. We can now go from the original idea in an interview, see how the conceptual model came about, and then see how, when source system data constraints were taken into account, the logical model was created.  Models can also be versioned, so we can see how designs have changed over time.  They can be viewed and edited as time, resources, user stories, interviews, design notes, iterations and other planning viewpoints.

This flexibility quickly breeds complexity, particularly given the different input and output contexts for planning.  To deal with this complexity we provide the ability to template the common planning scenarios as use cases: sets of pre-defined object ensembles, or pathways through WhereScape 3D.  Use cases are importable and exportable.  We have shipped the beta version with three use cases:  documenting a source system, discovering a source system for star schema analysis and documenting a data warehouse   WhereScape 3D is capable of many more use cases – look for others soon on our marketplace.  We expect use cases to come from the user community as well as ourselves, and will be slip-streaming them into the extended beta program.

If you would like to learn more about WhereScape 3D, you can schedule a web demo and we will run you through its paces. or for a limited period of time you can freely download the beta version.

We are really excited about WhereScape 3D, and the positive impact it will have on the data warehousing planning process.  Thanks to Huib, Jason, Jason, Penny, David, Wayne and Kurt and all the other people who have contributed to its success.  And watch this space – there is a lot more to come.

For WhereScape:
Scott R. Humphrey
Humphrey Strategic Communications
(503) 644-9709
mailto:humphrey@strategic-pr.com

WhereScape Introduces Data Driven Design Tool

WhereScape 3D scopes, sizes, costs and de-risks data warehouse, data mart and business intelligence projects before they begin

BOULDER, Colo.— May 20, 2011 – WhereScape, the developer of WhereScape RED, an agile Integrated Development Environment (IDE) for managing data warehouses, today introduced WhereScape 3D, a data driven design tool for planning and reality-testing data warehousing and business intelligence (BI) projects. Using WhereScape 3D, organizations are able to de-risk projects by planning accurate, user-tested projects up front, in hours or days rather than weeks or months.

WhereScape 3D extends the company’s leadership position in the data warehouse development tools market, providing a pre-ETL solution and making risk-assessed agile development a reality in the BI markets. WhereScape 3D is available via a free trial for a limited time by registering for the WhereScape 3D beta program. The announcement was made at the Boulder BI Brain Trust (BBBT), a gathering of leading BI analysts, experts, and practitioners who attend half day presentations from interesting and innovative vendors. The BBBT was founded by Dr. Claudia Imhoff, president of Intelligent Solutions, Inc.

WhereScape 3D steps data warehouse project teams through the process of source and target exploration and modeling, user functionality verification, scoping, sizing and costing activities, producing both complete project design documentation and an implementation-ready design that can be exported, as metadata to be utilized by products such as WhereScape RED, WhereScape’s flagship data warehouse lifecycle management platform.

Regardless of where the design team begins its process, WhereScape 3D provides end-to-end technical assessment tools to ensure that the flow of data from source to target in your warehousing environment is feasible, meets performance and capacity guidelines or constraints, and delivers the business value user communities expect.

Based on the power of the WhereScape Data Driven Design™ methodology, WhereScape 3D formalizes and accelerates investigation and risk assessment activities, enabling designers to begin where they actually are in the project lifecycle, including:

• Starting with a detailed, systematic profiling existing source systems, in order to understand what sorts of dimensional or normal-form warehouses and marts can be built from the available source data.
• Analyzing a new or updated source system against an in-place data warehouse or data mart to determine what impact those sources systems will have on existing BI infrastructure.
• Proofing a custom schema design, or a purchased or licensed data model against available source system data.
• Working backwards from prototyped or implemented reports, dashboards or analytical applications and their data requirements into warehouse or mart design and source system data availability.

“With careful application of data driven design principles and attention to the organizational structure of the delivery team, data warehouse design and population projects can now be placed on a much firmer basis of realistic and reliable scoping and sizing,” said Dr. Barry Devlin, a founder of the data warehousing industry and among the foremost worldwide authorities on business intelligence. “Tools to support data driven design, such as that provided by WhereScape 3D, separate from and prior to the design and development of ETL processes is key to enabling these skilled data practitioners to fulfill their role.”

Bank of New Zealand Extending Data Warehouse Infrastructure

The Bank of New Zealand used WhereScape RED to quickly and cost-effectively consolidate multiple financial data marts into a Microsoft SQL Server based centralized data warehouse. According to Dave Thompson, Head of Business Intelligence for Bank of New Zealand, incorporating WhereScape 3D into the Bank’s data warehouse infrastructure represents an opportunity to further reduce the risks and costs associated with designing, developing and extending data warehouses. “We are excited about WhereScape’s new data-driven design methodology and are evaluating opportunities for WhereScape 3D as we continue to build out our data warehouse infrastructure.”

Key features of WhereScape 3D include:

• Supports multiple aspects of the data warehouse planning process
• Combines data and design into the same process
• Supports and associates conceptual, logical and physical models
• Embedded data warehouse knowledge
• Generates data warehouse specific design documentation
• Out of the box support for common use cases, as well as creation of custom use cases.

“WhereScape has been devoted to the idea that well-designed tools can reduce the time, cost and risk of data warehouse development,” said WhereScape co-founder and CEO Michael Whitehead. “WhereScape 3D enables organizations to scope, size, eliminate risk and deliver data warehouses, data marts and business intelligence environments fast by focusing on the source systems, the target schema as well as end-user needs and expectations before they start the build process. It provides a central repository for planning information from interview notes to designs, and extends the concept of lineage to the planning process.”

About WhereScape

WhereScape design, develop, sell and support WhereScape 3D, the industry’s first data warehouse planning tool; and WhereScape RED, the industry’s first and best integrated development environment for building, deploying, managing and renovating data warehouses.

WhereScape’s products are used in every kind of business intelligence project found in today’s commercial environment: from normal-form enterprise data warehouses and data vaults, through user access layers and tiers of dependent data marts, to standalone data marts and reporting systems.

Our more than 400 customers are active and satisfied users of our technologies, and report that, with WhereScape’s products, they are able to build data warehouses with fewer people, few if any diversions, missed deadlines or unmet user expectations, and in record time: in days or weeks, instead of months or years. WhereScape has offices in Portland Oregon, Auckland New Zealand, and Wokingham UK. For more information, please visit www.wherescape.com.

All products or company names herein may be trademarks of their respective owners.

The partners announced in the SQL Server Fast Track Data Warehouse 3.0 release at TDWI Las Vegas were the kings of the industry: the likes of HP, IBM, Dell, EMC and Intel.  And oh yeah, there was also one software company – WhereScape.

Right after Microsoft did their 3.0 release, we did our announcementabout WhereScape’s new solution for SQL Server Fast Track.  The positive reaction started at the reception, and carried through the entire conference – customers, analysts, partners and even competitors were all over us to find out what it meant.   Just is case our explanations on the night were a little flavored by enthusiasm and alcohol, this post will cover the announcement in more detail.

Micrsoft have blogged about it here.  The actual WhereScape press release is here.  Doug Leland, General Manager of Business Platform Marketing Group in Microsoft’s Server and Tools Division made the following comment in the release:

“Microsoft is committed to providing new reference architectures that complement our appliances and enable our customers to achieve superior ‘out-of-the-box’ performance at the lowest cost.   WhereScape’s product complements SQL Server and will help our customers accelerate their development cycles and get into production faster and with confidence, thanks to the use of pre-tested Reference Architectures in Fast Track Data Warehouse 3.0.”

It’s great to see one of the leaders in the data space talking about getting to value quicker!

If you look at our announcement slide, you could be excused for thinking we had entered the hardware business.

Actually we were trying to show support for the Fast Track 3.0 reference architecture.  You can read about it on the Microsoft website here.

The previous version of the Fast Track reference architecture looked something like this:

 - basically a set of reference architectures and platforms that reduce risk (Microsoft and the hardware partners have done the configuration work) and dramatically decrease the time it takes to get a valid, proven, balanced system up and running.

It always seemed to us that there was a unique opportunity to take this further.

What about if you could include an integrated development environment to help build data warehouses faster? 

Microsoft and their partners have done a superb job of getting customers to “create table” quickly – what could we do to help get customers to “select from”?

That’s where WhereScape RED comes in.  We have invested a bunch of time working with Microsoft to make sure that WhereScape RED supports the best practice for building data warehouses.  The defaults are what you (and Microsoft) would expect – things like set based processing in the database, using minimal logging etc etc.

Our customers have already been experiencing the time to value advantages of WhereScape RED:

What we have done now is make sure that our customers can continue to build rapidly, whilst also supporting the Fast Track 3.0 Reference Architecture.

The best news for Microsoft customers is that they still get to use the Microsoft BI stack they know and like.

WhereScape RED builds SSIS packages for loading data, generates T-SQL for transforming data within SQL Server, and builds SSAS cubes for aggregation.  All the objects such as the cubes or indexes are all native SQL Server objects and can be manipulated and extended using WhereScape RED or any other development tool of choice.  Of course we will always encourage people to use WhereScape RED so that you can take advantage of the integrated meta data for documentation on demand, automated lineage information, integrated work flow, auctomatic versioning, simplified promotion….the list goes on…

The combination of a development environment optimised for building SQL Server data warehouses fast, in conjunction with hardware and SQL Server 2008 R2 optimized for data warehousing makes Fast Track unique.  We are thrilled to be part of Microsoft’s reference architecture, and anytime that we can take part in a release (and the party the next night) with the likes of HP, Dell and IBM, well, that works for us.

For WhereScape:   
Scott R. Humphrey        
Humphrey Strategic Communications         
(503) 644-9709       
humphrey@strategic-pr.com 
TDWI Booth #501  

           
WhereScape Launches New Solution for SQL Server Fast Track Data Warehouse 3.0 
More than 200 Microsoft SQL Server customers utilize WhereScape RED
to enable rapid data warehouse development and management

Las Vegas, NV  – February 14, 2011 – WhereScape, the developer of WhereScape RED, an agile Integrated Development Environment (IDE) for managing data warehouses, today announced a new offering for Microsoft’s Fast Track Data Warehouse 3.0 which provides reference architectures, technology, and best practices for building data warehouses more quickly, less expensively and at lower risk using Microsoft SQL Server 2008 R2.  The announcement was made at the TDWI World Conference, where WhereScape is demonstrating  the agile approach of WhereScape RED in an integrated Microsoft Fast Track 3.0-enabled SQL Server data warehouse environment in booth #501.

WhereScape is the first independent software vendor (ISV) to develop a solution for Fast Track Data Warehouse 3.0.  WhereScape has worked with Microsoft to ensure that WhereScape RED Fast Track 3.0 Edition utilizes the best practices of the Fast Track guidelines.   Specifically, WhereScape RED Fast Track 3.0 Edition includes:

• Faster loading with SQL Server Integration Services (SSIS) – WhereScape RED will leverage the power of SSIS to load data into the staging area of the data warehouse by generating packages automatically. 

• Efficient processing – Once the data has been loaded, all processing is defined and executed in the database using efficient, auto-generated, easy to read, easy to modify, documented and re-generated SQL.  All generated SQL is SET based, minimally logged and optimized for SQL Server 2008 R2.

• Easy aggregations – WhereScape RED provides built in processing to add aggregations of the data in the database or in SQL Server Analysis Services as an integral part of the data warehouse management and workflow. 

• Built-in Scheduler – WhereScape RED incorporates an integrated workflow engine which provides parallel processing, workflow dependencies, centralized monitoring, error handling and restarts.

“Microsoft is committed to providing new reference architectures that complement our appliances and enable our customers to achieve superior ‘out-of-the-box’ performance at the lowest cost,” said Doug Leland, General Manager of Business Platform Marketing Group in Microsoft’s Server and Tools Division.   “WhereScape’s product complements SQL Server and will help our customers accelerate their development cycles and get into production faster and with confidence, thanks to the use of pre-tested Reference Architectures in Fast Track Data Warehouse 3.0.”

Microsoft SQL Server is the most popular database for WhereScape RED, with greater than 200 Microsoft customers enjoying productivity gains, ease of use, and cost benefits of the SQL Server and WhereScape RED combination.  Joint customers utilize WhereScape RED to build SQL Server data warehouses and marts quickly, make changes efficiently, and automatically document the implementation..he I

“WhereScape is proud to announce the first ISV solution for Fast Track Data Warehouse 3.0 that delivers a complete set of enterprise-ready technologies at the lowest total-cost-of-ownership,” said WhereScape CEO Michael Whitehead.  “WhereScape RED helps accelerate the development of SQL Server-based data warehouses and enables organizations to build data warehouses in less time, with less money, while mitigating risk, and resulting in fully documented implementations.”

Fully Integrated with Microsoft SQL Server
WhereScape RED provides native support for Microsoft SQL Server.  The same WhereScape RED agile integrated development environment is used to build SQL Server data warehouses as well as Analysis Services cubes. The benefits are compelling – organizations only need to learn one development environment, leveraging end-to-end metadata from the source system through to the cube layer. WhereScape delivers an integrated workflow and scheduler for the data warehouse and the cubes, automatically maintained data lineage information, and full documentation from source system extract through to the data warehouse objects. 

About WhereScape
WhereScape enables companies to get value from their data warehouses faster. Its flagship product, WhereScape RED, transforms traditional approaches to building and managing data warehouses with a truly agile methodology. WhereScape RED is the only comprehensive Integrated Development Environment (IDE) for data warehousing that supports the entire data warehouse management life cycle, integrating source system exploration, schema design, metadata management, warehouse scheduling and enhancement into a single, simple integrated design.

More than 400 customers worldwide are using WhereScape RED on a variety of platforms. Projects utilizing WhereScape RED typically come in under budget, ahead of schedule, with improved performance, greater transparency and built on more solid foundations over the systems they replace. WhereScape has offices in Portland Oregon, Auckland New Zealand, and Wokingham UK.  For more information, please visit www.wherescape.com.

###

All products or company names herein may be trademarks of their respective owners.

The latest WhereScape RED beta (6.5.3.2) is now available for customer download.

A huge amount of effort has gone into this release.  There are a large number of new features added based on customer feedback and suggestions in the WhereScape forum. There are enhancements to fix some of those minor but ultimately annoying little things, while others are radically new features that we will be extending for a while.  Look for the official release in a few weeks time, but in the meantime customers can download the beta from today.

Significant changes include:

1. SSIS Loads.  Ability to leverage the extract and load performance available from SSIS as part of the WhereScape RED processing. WhereScape RED does this by generating and executing an SSIS package dynamically at run time. Any errors or messages resulting from execution are passed back into the WhereScape RED workflow metadata to drive subsequent workflow actions.
2. TPT Loads on Teradata.  Provides a faster method to move data from any ODBC source into your Teradata database.
3. New Data Type Mapping Facility.  Enables custom data mappings to be used during drag/drop operations of source to target mappings.  The standard sets have also been enhanced to cater for newer data types.
4. Set Based Dimension Update.  For SQLServer and Oracle, it is now possible to generate set based updates for dimensions.
5. Restructured Reports Menu.  The reports menu has been restructured to make easier to find the required meta report.
6. Reports Output Tab. The reports output has been redirected to a new output tab.
7. Application Load Dialog Restructured.  This restructure has allowed for new options such as the control over indexes during the application load process.

The full release notes can be viewed here.

At one of our long term client sites the production data warehouse server was running out of space and for various technical and political reasons the disk space could not be extended quickly or easily.

The IT manager was looking into all the options which were either too expensive or would not be completed before the production server ran out of space and wasn’t sure what other quick options were available.

I suggested I could extract the data that was older than two years from the 15 largest tables and store on an external USB drive and also copy this data to an additional USB drive once all historic data had been extracted.

The plan was given the go ahead and I went out and purchased two 1TB external USB drives for around $100 each.

The process I used was:
Use database utility (BCP for SQL Server in this case) to extract data from the fifteen largest tables  to text files for historic data only.
Take a copy of the DDL of the tables as they are now in case the data ever needs to be reloaded.
Take a copy and edit this DDL to create copies of all the tables with a suffix of “_ARCHIVE”
Use database utility (BCP for SQL Server in this case) to reload the data files extracted into the “_ARCHIVE” tables to ensure data extracted will reload (this will highlight any problems with commas, pipes etc and will ensure your delimeter is correct).
Once all extracted data files have loaded successfully, map a drive to the production server from your local machine and copy to 1st external USB storage device along with each table DDL, unload script, load script in separate folders.
Once all these have copied successfully, copy all files again to the second external USB storage device from the 1st external USB storage device.
Once all these have copied successfully, carefully delete the data that was archived from the production tables and shrink the database if required.
Store the external USB storage devices in separate locations in client site machine rooms and/or off site safe locations.

The whole process only took a couple of days and can also be run as a background task. A year later I repeated this exercise and was able to re-use all the load/unload scripts with a change to the data values and output filenames.

So, for only $200 and a couple of days of my time, the client has ended up with a rapid data warehouse archive process that is also mirrored and the data can be reloaded with confidence at any time if required.

SEATTLE – (Business Wire) WhereScape, the developer of WhereScape RED, an agile Integrated Development Environment (IDE) for managing data warehouses, today announced native support for Microsoft SQL Server Integration Services (SSIS), Microsoft’s platform for building enterprise-level data integration and data transformations solutions. WhereScape customers can now use SSIS to load data into a data warehouse which is designed and managed by WhereScape RED and leverage SSIS data movement functionality as part of its data warehouse development, management and processing activities. The announcement was made at PASS Summit 2010, the premier conference for SQL Server professionals, where WhereScape is exhibiting in booth #400.

Once data is loaded into a SQL Server data warehouse, WhereScape RED works within SQL Server’s powerful database engine to transform data and build data warehouse objects within the data warehouse or project mart in a fraction of the time that it typically takes with traditional methods. WhereScape RED will continue to support the native SQL Server load utilities it has used to import data into the database, such as BULK INSERT, linked servers and ODBC.

Microsoft SQL Server continues to be the most popular database for WhereScape RED, with well over 200 Microsoft customers, including Coinstar, a leading provider of automated retail solutions and IPC, an independent SUBWAY® franchisee-owned and operated purchasing cooperative. These customers enjoy productivity gains, ease of use, and cost benefits of the SQL Server and WhereScape combination.

“SQL Server Integration Services excels at loading data from different sources. Once the data is loaded, WhereScape RED builds all the downstream SQL Server objects such as facts, dimensions, and Microsoft Analysis Services cubes in the database 10 times faster than with traditional, hand-coding methods,” said WhereScape CEO Michael Whitehead. “SSIS provides WhereScape customers another option to load data that is extremely fast, efficient and flexible. WhereScape developers enjoy full technical and user documentation for change management and data lineage.”

Fully Integrated with Microsoft SQL Server

WhereScape RED provides native support for SQL Server as well as IBM DB2, Oracle and Teradata. The same WhereScape agile integrated development environment is used to build SQL Server data warehouses as well as Analysis Services cubes. The benefits are compelling – organizations only need to learn one development environment, leveraging end-to-end metadata from the source system through to the cube layer (and where supported the client tool as well). WhereScape delivers an integrated workflow and scheduling for the data warehouse and the cubes, automatically maintained lineage information and documentation from source system extract through to the data warehouse and the cubes.

About WhereScape

WhereScape provides a product, WhereScape RED, which transforms traditional approaches to building and managing data warehouses with a truly agile methodology. WhereScape RED is the only comprehensive Integrated Development Environment (IDE) for data warehousing that supports the entire data warehouse management life cycle, integrating source system exploration, schema design, metadata management, warehouse scheduling and enhancement into a single, simple integrated design.

More than 350 customers worldwide are using WhereScape RED on a variety of platforms. Projects performed using WhereScape RED typically come in under budget, ahead of schedule, with improved performance, greater transparency and built on more solid foundations over the systems they replace. WhereScape has head offices in Auckland, New Zealand, Portland Oregon, and Wokingham, UK. For more information, please visit www.wherescape.com.

All products or company names herein may be trademarks of their respective owners.

For WhereScape:
Humphrey Strategic Communications
Scott R. Humphrey, 503-644-9709

Portland, OR  – October 25, 2010 – WhereScape, a Teradata Select Software Partner, announced today that the company’s flagship Integrated Development Environment (IDE) for managing data warehouses has been selected to play an integral role in Teradata’s end-to-end data warehouse demonstration that debuted yesterday at the 2010 Teradata Partners User Group Conference and Expo.  Delivered to a customer audience by Teradata staff, the demo features WhereScape’s agile ELT (extraction load transformation) tool.   Teradata’s “Data to Decisions” showcases end-to-end capabilities via data warehousing tools, products, services and third-party software offerings.  The hands-on Teradata workshop and demo suite was unveiled at the 2010 Teradata Partners User Group Conference and Expo currently taking place in San Diego, Calif.  WhereScape is demonstrating WhereScape RED in booth #425.

The “Data to Decisions” demonstration performs the entire data warehouse lifecycle; from the time data appears in an organization through the time decisions are produced.  In the demonstration, WhereScape RED is being utilized to expedite data delivery from the source systems and properly transforming it for loading into a Teradata data warehouse.  WhereScape’s framework explicitly supports Teradata normal-form model table methodologies, just as Teradata practitioners would generate if they were handcrafting the functionality.

WhereScape RED builds Teradata objects (tables, indexes, join indexes etc) and generates set based procedures and scripts.  It utilizes Teradata for both its ELT transformations and metadata storage. The software generates strictly native Teradata scripts (FastLoad, MultiLoad and TPT), set- based code and objects, which are all documented and all automatically included in WhereScape’s integrated and open metadata repository.  Companies such as Sirius Radio, United Rentals and Vodafone are utilizing WhereScape RED to rapidly build, deploy and maintain Teradata data warehouses and analytic platforms. 

According to Lance Miller, Director, Teradata Services Marketing who co-presented the workshop, “WhereScape RED provides a proven alternative for quickly prototyping, building and deploying Teradata data warehouses from multiple source systems that can be quickly adapted as business needs dictate.”

“We are delighted to be contributing the data delivery portion of Teradata’s end-to-end data warehouse demonstration,” said Mark Budzinski, general manager, WhereScape USA.  “Teradata customers worldwide have benefited from WhereScape’s agile approach to data warehouse development—one that significantly lowers the cost and time required to deploy, and just as importantly enables change to the warehouse as required.”

About WhereScape

WhereScape provides a product, WhereScape RED, which transforms traditional approaches to building and managing data warehouses with a truly Agile methodology. WhereScape RED is the only comprehensive Integrated Development Environment (IDE) for data warehousing that supports the entire data warehouse management life cycle, integrating source system exploration, schema design, metadata management, warehouse scheduling and enhancement into a single, simple integrated design.

More than 350 customers worldwide are using WhereScape RED on a variety of platforms. Projects performed using WhereScape RED typically come in under budget, ahead of schedule, with improved performance, greater transparency and built on more solid foundations over the systems they replace. WhereScape has head offices in Auckland, New Zealand, Portland Oregon, and Wokingham, UK.  For more information, please visit www.wherescape.com.

WhereScape is out and about in a big way over the next couple of weeks.  If you would like to find out more about agile data warehousing, or just want to see how to build data warehouses fast, stop by for a chat at any of the following events:

• Teradata Partners Conference & Expo – October 24-28 2010, San Diego Conference Center, San Diego, CA, where WhereScape is a Gold Sponsor.  Visit us at Booth 425 or attend session 2790 “Teradata Live: An End-to-End Demo of the Teradata Data-to-Decisions Value Chain”.
• IRM UK Data Management, Information Quality & DW/BI Conferences Europe 2010 -  November 3-5, Radisson Blu Portman Hotel, London, UK, where WhereScape is again a Gold Sponsor.  Visit us on the exhibition floor, or attend “Agile Data Warehousing Development – The New Approach” 15.20-16.05, November 4th.
• 2010 PASS Summit, The Premier Conference for Microsoft SQL Server Professionals – November 8-11, Seattle, WA.  Visit WhereScape on the expo floor.

In addition, WhereScape Distributor MIP is the Platinum Sponsor at the Data Warehousing & Business Intelligence 2.0 Summit and Exhibition, October 26-28 2010, Swissotel, Sydney, Australia.  See them at the Expo or listen to Steve Hitchman’s talk on “AGILE Data Warehousing Development – A New Approach” and Phil Considine on “AGILE Data Warehousing Implementation”.

Three continents in three weeks – every opportunity to come and find out what all the buzz is about.  If you can’t make any of these events but would still like to catch up, just contact us at info at wherescape dot com.