WhereScape RED Builds data warehouses, fast.

In the simplest kind of data warehouse environment, the WhereScape RED metadata and all data warehouse tables exist in a single database or user.  A Teradata user is preferred (over a database) as the metadata includes some procedures that use dynamic SQL.  Procedures containing dynamic SQL may only be compiled under a user in Teradata.

For the simplest kind of environment, a user called dssdemo can be created and granted sufficient privileges to get started with WhereScape RED using this SQL:

CREATE USER dssdemo FROM dbc AS
PERMANENT = 100000000, SPOOL = 100000000, TEMPORARY = 100000000
,PASSWORD = wsl;
GRANT ALL ON dssdemo TO dssdemo WITH GRANT OPTION;
GRANT SELECT ON DBC.TVM TO dssdemo;
GRANT SELECT ON DBC.Dbase TO dssdemo;
GRANT SELECT ON DBC.ErrorMsgs TO dssdemo WITH GRANT OPTION;

The grants in this example may be considered excessive, but are simple to achieve.  The worst case scenario results in the user being dropped by another user they’ve on-granted some privilege to.

In a more complex example, a Database called Test_Grants owns the development data warehouse.  There are two developers, called John and Mary.  Test_Grants has three users:
- john
- mary
- metadata

The users john and mary are used by John and Mary to log into Teradata using WhereScape RED.  The user metadata owns the WhereScape RED metadata and all compile stored procedures generated by WhereScape RED.  The WhereScape metadata is installed and upgraded whilst signed in as metadata.

John has four databases for storing tables and views:
- john_load
- john_stage
- john_model
- john_view

Similarly, Mary has four databases for storing tables and views:
- mary_load
- mary_stage
- mary_model
- mary_view

Here is the database/user structure:

Once created, the metadata user has the following grants on itself:

These are insufficient for WhereScape RED and need to be changed to:

Note: a tick indicates a privilege has been granted, a green circle indicates a privilege has been granted WITH GRANT OPTION.

Each developer and the metadata user also require the following grants:

1. GRANT SELECT ON DBC.TVM
2. GRANT SELECT ON DBC.Dbase

The metadata user also required the following grant:
3. GRANT SELECT ON DBC.ErrorMsgs WITH GRANT OPTION

The first two grants are required by WhereScape RED to compare physical tables and metadata definitions when Validate against database is used.  In all cases, WhereScape RED includes LOCK ROW FOR ACCESS in SELECTs from these two tables.

The third grant is given to all users by default without the WITH GRANT OPTION.  WhereScape RED generated stored procedures use the DBC view ErrorMsgs to look up error message details.  In order for the executer of stored procedures be able to query DBC.ErrorMsgs, the metadata user must be able to on-grant select on DBC.ErrorMsgs.

As mentioned above, metadata installation and upgrade (validation) must be done as the metadata user.  For the ODBC connection DemoTDAT (in this case also with a TDPID of DemoTDAT), the following information is entered to install the WhereScape RED metadata:

The developers (John and Mary) also need some grants.  These examples work thru the details for the john user.  Everything also needs to be repeated for other developers.

The john user needs to be able to:
1. Read, write and delete data in the WhereScape RED metadata tables (in the metadata user).

2. Create, replace and drop stored procedures in the metadata user.
3. Create and drop tables in his four development databases.
4. Read, write and delete data in his four development databases (directly and via stored procedures compiled in the user metadata).

Grants to enable “Read, write and delete data in the WhereScape RED metadata tables” and “create, replace and drop stored procedures in the metadata user” (grants from metadata to john):

Grants to enable john to create and drop tables in his four development databases and to directly read, write and delete data in his four development databases:

Grants to enable john to read, write and delete data in his four development databases via stored procedures compiled in the user metadata:

Grants to enable john to create and drop views in the john_view development database and to directly read, write and delete data in his model database (john_model):

Note: It may be advisable to use database roles rather than direct grants.

Once these privileges have been added, sign into WhereScape RED as john, with the Meta Database set to metadata:

If required, create projects for each developer:

Then ensure each table or view is created in the correct database:

 

This approach can be expanded to the most complex scenarios, including multiple staging databases, etc.

WhereScape RED ships with a pdf of the user guide, but it is also available in hardcopy from lulu.com.  The SQL Server/DB2/Oracle version can be ordered directly from http://www.lulu.com/content/4586805.

Schemas were introduced into SQL 2005 to provide security containers for objects (eg tables and views). They allow DBAs to manage security at the schema level - any objects created in a schema inherits the schema’s security properties.

Just as usefully schemas also provide a nice meta-data container for objects so that you can use a schema to group objects together - for example to relate a set of objects together relating to a specific department or function.

WhereScape RED creates and manages all of its objects in the dbo schema - this ensures that no issues (naming or security) are introduced when managing or updating the database objects due to schemas. In order to make use of schemas for security and grouping over user tables and views WhereScape consultants have released a procedure that manages synonyms within schemas to point back to the dbo objects. This means that users can be shut out of the dbo schema and restricted to see only the objects within the schemas that they have access to.

For example we can now have a project called Sales (in the Schema project group) that contains fact_sales, dim_date and dim_customer. Running this procedure will create a Sales schema in SQL, synonyms in that schema pointing to fact_sales, dim_date and dim_customer, and optionally a Sales SQL Server user limited to the Sales schema. As with projects the same object can happily exist in more than one schema.

The procedure (admin_schema_maintenance) is available by emailing support. When it is run or processed within RED it manages schemas and synonyms (and optionally users to access those schemas) based on RED’s projects. The script works by creating SQL Server schemas with the same name as RED Projects that sit under a Project Group called Schema (you need to create this group and projects). The procedure will create synonyms for any Dimensions, Views or Fact tables that exist in these projects in those schemas. The following parameters control procedure options:
SCHEMA_MAINTAIN_SCHEMAS - 0 or 1 will drop any existing schemas (except dbo) and recreate them
SCHEMA_MAINTAIN_USERS - 0 or 1 will drop and recreate users with access only to their schema
SCHEMA_PRINT_ONLY - 0 or 1 will only print the generated SQL not execute it

The procedure can be added to RED by creating a new Procedure called admin_schema_maintenance and then pasting in the procedure code. The procedure is not part of RED and can be added, changed, run and scheduled as required. We would recommend that the script be tested in your environment prior to running in production.

NOTE: Do not forget that synonyms are dependent objects in SQL Server - if they interfere with development then drop the synonyms for those objects being recreated - or drop all synonyms during development. To do this:

• An easy way of removing synonyms for a single object is to move that object out of the Schema project(s) and rerun the procedure.
• A simple way of removing all synonyms quickly is to rename the Schema project group (eg Schema_Off) and rerun the procedure.

Considerations should be made for migrating objects to production. It may be easier to drop all synonyms in production first by renaming the Schema project (as above), run procedure, migrate objects over, rename Group back to Schema and rerun procedure.

Between December 9th and 12th we presented a cut down 2 hour user group around NZ in order to provide a year end update on WhereScape and WhereScape RED. We had a good turn out in the 3 centres that we presented to.

In summary the main points of the presentation were:

• Our partnership with IBM – what this means. Basically IBM will distribute RED in their “Balanced Warehouse” initiative which aims to improve the speed to value proposition of their DB2 and associated warehouse solution. This is big news for us as it endorses our approach and of course WhereScape RED – they searched for a suitable product to fill this space in their solutions’ software stack and they made the approach to WhereScape.
• Perry did a bit on “You are in good Company” which went through new customers locally (big ones were The Warehouse and BNZ), internationally (some Aussie govt depts. And US companies) and company growth.
• I covered a piece on v5.6.4 – which was a recap of things that have been added recently, such as:
  o Native ODBC Loads – for fast loading of data from other databases
  o Retro fit objects to load in a data warehouse model, or an existing data warehouse.
  o Export objects – allow you to create files from your data warehouse objects
  o Check-in / Check-out – allows team members to lock areas of the data warehouse from other developers to ensure no one overwrites another team members work.
  o Improved Application Load – generates DDL files so that production administrators can decide how to apply changes into production by running the RED generated DDL.
  o SQL 2008 support.
• I then covered new additions in v6 of RED. These items are in beta and will be released soon, including:
  o IBM DB2 support (out now)
  o Improved MS OLAP management - managing OLAP shared dimensions, cubes and security roles as separate RED objects
  o New documentation layout – that utilises FRAMES and one HTML file per object. The index displayed down the side includes project and project group hierarchies.
  o New Project management screen for easier management of projects.
  o Improved integration with other Source Control systems.
• To be released in v6 are the new RED Editions:
  o RED Mart (current RED ) - builds dimensional data marts and data warehouses
  o RED Store – builds and manages data store objects (persistent load data).
  o RED Warehouse – builds and manages 3rd Normal form data warehouses.
  These editions can be mixed and matched eg Data Store and Dimensional in the same RED repository or in separate ones.
• We had Scott from IBM talk about Master Data Management and Data Quality.
• Finally we closed on a discussion around the viability of a 2 day User Work Group made up of Advanced Training (eg MDM, Prototyping), Customer stories and workshops.