Data Warehousing: SQL Server Specifics (Warehousing)

These are several suggestions for setting up data warehousing from LH to SQL Server.

 

Warehousing Defaults

 

These are the suggestions for the default data type mappings.

 

 

For the CHAR type, adjust the DataSet and SQL Server type to the maximum length expected.  Use the Details button in the Create Warehouse Procedure wizard to check the maximum length or other details (like the description) of the LH field.

 

For the LH DECIMAL type, if more than 4 decimal digits of precision are required for a specific field, change the corresponding SQL Server type to float and the DataSet type to DOUBLE.

 

For the LH TEXT and VARCHAR types, if a field is expected to contain more than 254 characters, change the corresponding SQL Server type to text and the DataSet type to LONGVARCHAR(65531).  Similarly for the LH VARBINARY type, if it will exceed 255 bytes, change the corresponding SQL Server type to image and the DataSet type to LONGVARBINARY(65531).

 

The SQL Server type tinyint is used instead of bit because the bit type cannot contain nulls and cannot be indexed.  Likewise, avoid the text andimage SQL Server types, because certain Transact-SQL features cannot be used with these types and these data types use relatively large amounts of disk space due to a 2k block allocation algorithm.

 

 

Create Table script

 

Recent versions of SQL Server support the primary key clause in the create table statement, however it is suggested that you use thesp_primarykey stored procedure to declare the primary key so that your scripts will work on SQL Server from version 1.1 through today.

 

From the purchase order example, the create table scripts generated by the Create Warehouse Procedure wizard are as follows:

 

create table purchase_orders

       (

       po_no   int          not null primary key,

       po_date datetime         null,

       po_desc varchar(254)     null

       )

 

create table purchase_order_lines

       (

       po_no     int   not null primary key,

       po_line   int   not null primary key,

       stk_no    int       null,

       qty       int       null,

       unit_cost money     null,

       qty_rcvd  int       null

       )

 

In each create script, remove the primary key clause and add a call to the sp_primarykey stored procedure after each create table statement:

 

create table purchase_orders

       (

       po_no   int          not null,

       po_date datetime         null,

       po_desc varchar(254)     null

       )

exec sp_primarykey 'purchase_orders', 'po_no'

 

create table purchase_order_lines

       (

       po_no     int   not null,

       po_line   int   not null,

       stk_no    int       null,

       qty       int       null,

       unit_cost money     null,

       qty_rcvd  int       null

       )

exec sp_primarykey 'purchase_order_lines', 'po_no', 'po_line'

 

This uses one of the handiest features of Transact-SQL:  the ability to put multiple Transact-SQL statements into a single executable script.  There are some exceptions to this feature; for example, you cannot combine any other Transact-SQL statements with a create procedure statement.

 

 

Clear Table script

 

The syntax  delete * from <table>  is not valid in Transact-SQL.  Instead, use  delete from <table>  (sans asterisk).  Alternatively, the Transact-SQL statement  truncate table <table>  is the fastest way to delete all rows in a table, but does not use transaction logging.

 

 

Having it Both Ways

 

If your Warehouse Procedures need to be able to conditionally switch between SQL Server-specific scripts and generic ANSI-SQL scripts, use the Connection Object properties XO_SOURCENAME$ and XO_SOURCETYPE$ to determine what connection definition and type (ODBC, SQLServer,Oracle) are being used:

 

QryGetProperty(Handle, QRY_CONNECTION$, hXO)

XOGetProperty(hXO, XO_SOURCETYPE$, Type)

begin case

  case Type _eqc "SQLServer"

    * SQL Server specific functionality goes here

  case Type _eqc "Oracle"

    * Oracle specific functionality goes here

  case OTHERWISE$

    * generic functionality goes here

end case

 

 

Using Stored Procedures

 

Just about anything you can do in a Transact-SQL script can be done in a Transact-SQL stored procedure.  There are several reasons to use stored procedures:

 

1.         Stored procedures are pre-compiled, so an execution plan already exists and is often cached if the procedure is being called repeatedly.

2.         Security is simple:  Instead of granting rights on the table, rights are granted on the stored procedure.  This way, no one needs rights to modify the table.

3.         The Transact-SQL logic is in the stored procedure itself, so the scripts that have to be repeatedly sent over the network are used only to execute that procedure and are therefore as tiny as possible.

 

Using the purchase order example insert script:

 

insert into purchase_orders

       (

       po_no, po_date, po_desc

       )

values

       (

       :po_no, :po_date, :po_desc

       )

 

This script creates a stored procedure which handles the insert:

 

create procedure insert_po

       (

       @po_no   int,

       @po_date datetime,

       @po_desc varchar(254)

       )

as

       insert into purchase_orders

              (

              po_no, po_date, po_desc

              )

       values

              (

              @po_no, @po_date, @po_desc

              )

 

Replace the automatically generated insert script with the following script, which executes the stored procedure to insert one row:

 

exec insert_po :po_no, :po_date, :po_desc