BI Architecture and Design Guide

BI Architecture and Design Guide.

SQL Server 2005 Logon Triggers

Every time a user connects to the database a trigger is fired. In the logon trigger you simple insert a row into an auditing table. Yuo can read about SQL Server 2005 Logon Triggers here.

What's New (SQL Server 2008)

Here you can read about new or improved features of Microsoft SQL Server 2008.

MERGE Statement in MS SQL Server 2008

MS SQL Server 2008 introduces new MERGE statement. You can perform INSERT, UPDATE, and DELETE operations in a single statement using MERGE. The MERGE syntax allows you to join a data source with a target table or view, and then perform multiple actions based on the results of that join.
You can read about new MERGE statement at MSDN Site.
Here are examples of MERGE from MSDN:

MERGE Production.ProductInventory AS pi
USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail sod
JOIN Sales.SalesOrderHeader soh
ON sod.SalesOrderID = soh.SalesOrderID
AND soh.OrderDate = GETDATE()
GROUP BY ProductID) AS src (ProductID, OrderQty)
ON (pi.ProductID = src.ProductID)
WHEN MATCHED AND pi.Quantity - src.OrderQty = 0
THEN DELETE
WHEN MATCHED
THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty;

MERGE Departments AS d
USING Departments_delta AS dd
ON (d.DeptID = dd.DeptID)
WHEN MATCHED AND d.Manager <> dd.Manager OR d.DeptName <> dd.DeptName
THEN UPDATE SET d.Manager = dd.Manager, d.DeptName = dd.DeptName
WHEN NOT MATCHED THEN
INSERT (DeptID, DeptName, Manager)
VALUES (dd.DeptID, dd.DeptName, dd.Manager)
WHEN SOURCE NOT MATCHED THEN
DELETE;

SQL Server 2005: CLR Integration Team Blog

SQL Server 2005: CLR Integration Team Blog - samples, tips and tricks, insights from the CLR Integration team at SQL Server.
CLR Trigger for Automatic Registration of UDXs on CREATE ASSEMBLY

Lutz Roeder's Programming.NET Site

Lutz Roeder's Programming.NET site. You can find there tools and source code for .NET, C# and Visual Basic. Reflector is a popular class browser. Resourcer is a .NET resource editor.

Regular Expressions Resources

Regular-Expressions.info - the premier website about Regular Expressions.
www.regexlib.com - the regular expressions library and a good collection of regular expresions related tools and books from around the internet.

Improving Performance with SQL Server 2005 Indexed Views

The document Improving Performance with SQL Server 2005 Indexed Views describes the improved indexed views capability of SQL Server 2005 Enterprise Edition. Indexed views are explained and specific scenarios in which they may provide performance improvements are discussed.

Implementing Row- and Cell-Level Security Using SQL Server 2005

The paper Implementing Row- and Cell-Level Security in Classified Databases Using SQL Server 2005 describes how SQL Server 2005 can be used to support row- and cell-level security (RLS/CLS). The examples provided in this white paper show how RLS and CLS can be used to meet classified database security requirements.

Some Interesting SQL Server Articles

Comparing Tables Organized with Clustered Indexes versus Heaps
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
Database Concurrency and Row Level Versioning in SQL Server 2005
SQL Server 2005 Partitioned Tables and Indexes
SQL Injection

SQL Server 2008 November CTP

The SQL Server Connect Team in conjunction with the SQL Server Product Team announced the release of the November CTP of SQL Server 2008 Katmai.
Here is a direct link to the download on the Download Center. Installers and media ISOs available.

Microsoft Office PerformancePoint Server 2007 RTM Release

Microsoft pleased to announce that Microsoft Office PerformancePoint Server 2007 is now generally available.

Product Information:

• Available in both 32 and 64 bit editions
• Evaluation editions are available are available on the Microsoft Download Center:
• 32 bit edition (http://www.microsoft.com/downloads/details.aspx?FamilyId=6372C24F-67DD-42DD-B034-748907B23420&displaylang=en)
• 64 bit edition (http://www.microsoft.com/downloads/details.aspx?FamilyId=3ADAC793-DEF0-4BA8-A9AB-228979B8DB40&displaylang=en)
• Enterprise editions are available for MSDN/TechNet subscribers under Applications within the left pane of the MSDN home page.
• Enterprise editions are also available on Volume License. Learn more http://www.microsoft.com/performancepoint.
• PerformancePoint Server 2007 Volume Licensing (http://www.microsoft.com/business/performancepoint/howtobuy/default.aspx)
• General Volume Licensing information
  (http://www.microsoftvolumelicensing.com/userights/ProductPage.aspx?pid=255)
• English, French, German and Spanish Languages are available now. Additional languages are coming soon and include:
• Brazilian Portuguese
• Chinese Simplified
• Chinese Traditional
• Czech
• Danish
• Dutch
• European Portuguese
• Finnish
• Italian
• Japanese
• Korean
• Polish
• Russian
• Swedish
• Microsoft Connect is only for pre-generally available releases. The CTP4 Release will remain on this site until November 30th for your evaluation purposes.

MDX Studio

Mosha Pasumansky announced his MDX Studio.

Microsoft SQL Server Webcasts & Events

Microsoft SQL Server TechNet On-Demand Webcasts (English)
Microsoft SQL Server MSDN On-Demand Webcasts (English)

Microsoft SQL Server Webcasts & Events (German)

CTP4 of Microsoft Office PerformancePoint Server 2007 is now available

August 26, 2007

Microsoft announced the ENU (English) release of Microsoft Office PerformancePoint Server 2007 CTP4 software. See the "What's new in PerformancePoint Server CTP4" document from the following Connect site for details on new changes.

MSConnect and Download
You can download the files within the "Download" section of Microsoft Connect (https://connect.microsoft.com/default.aspx).

SQL Server Samples & Community Projects

Here you can download SQL Server Samples & Community Projects.

BIDS Helper

Here is a link to BIDS Helper - the set of VS.Net add-ins that extend and enhance the functionality of the SQL Server BI Development Studio. I find these add-ins very helpful for work in the BI Development Studio.

BIDS Helper feaures:

• Analysis Services
• Aggregation Manager
• Calculation Helpers
• Column Usage Reports
• Delete Unused Aggregations
• Deploy Aggregation Designs
• Deploy MDX Script
• Dimension Health Check
• Dimension Optimization Report
• Measure Group Health Check
• Printer Friendly Dimension Usage
• Tri-State Perspectives
• Show Extra Properties
• Update Estimated Counts
• Validate Aggregations
• Visualize Attribute Lattice
• Integration Services
• Create Fixed Width Columns
• dtsConfig File Formatter
• Expression and Configuration Highlighter
• Expression List
• Smart Diff
• General
• Enable/Disable features

SQL Server 2008 July CTP

August 1, 2007
SQL Server 2008 July CTP is now available

Configuring Connection from MS SQL 2005 (x64) to Oracle 10g (x64) on Windows 2003 Server (x64)

These 3 links have helped me to configure access to Oracle from MS SQL on Windows 2003 Server (x64):
Troubleshooting Oracle Publishers
Linked server from MSSQL2005 64 bit to Oracle (in Russian)
SSIS & Oracle (in Russian)

Now, here are the steps:

1. Download and install Oracle Data Access Components (ODAC) x64.
2. Place correct sqlnet.ora and tnsnames.ora to the network\admin folder of the ODAC home.
3. Run Oracle Universal Installer, select Installed Products->Environment and place the ODAC home to the top.
4. Restart the computer.
5. Open SQL Server Management Studio
- Provide the Server Name, Authentication, Username and Password to connect to your SQL Server at the dialog box. This will authenticate you and log you into Management Studio.
- Expand the Server Objects folder by clicking on the + sign
- Now, expand the Linked Servers folder and you will see Providers folder. Expand the Providers folder. You will see Oracle Provider for OLEDB. Right click on it and go to Properties. Check the check boxes for "Dynamic Parameters" and "Allow InProcess". Click OK and the dialogue box will close.
- Next, right click on the Linked Servers folder and choose New Linked Server
- In the New Linked Server Properties dialogue box, do the following:
----- Type in a Linked Server Name, for this example, we will call it 'Tiger'
----- Under Server Type, select Other Data Sources - for the provider name, select Oracle Provider for OLEDB
----- In the Data Source field, type in the Oracle Service name (SQL Net Alias)
----- In the Product Name field, type in "Oracle"
----- Leave Provider String blank.
- Now, click on the "Security option" on the left pane.
----- Select the radio button "Be made using this security context" at the bottom of the box.
----- Type in the User ID (your Oracle login), ie. Scott in the "Remote login" field and your Password, ie. Tiger in the "With Password" field.
- Go to the "Server Options" tab in the Linked Server Properties.
----- While leaving the default selections selected, also click on "Collation Compatible". Select "true" from the drop down button.
----- Click OK. This will create your Linked Server named Tiger.
6. Verify that the registry settings at
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI and
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSDTC\MTxOCI
are correct:
OracleOciLib = oci.dll
OracleSqlLib = orasql10.dll
OracleXaLib = oraclient10.dll
7. If the Oracle OLEDB provider is installed, make sure that it is registered. To register the provider DLL, execute the following command from the directory in which the DLL is installed, and then stop and restart the SQL Server instance:
regsvr32 OraOLEDB10.dll or regsvr32 OraOLEDB.dll.

Now we are able to work with Oracle from MS SQL, and it's fine, but... on one of my hosts I've got the next problem: the Oracle linked server in MS SQL Database Engine works ok, connection from SSAS to Oracle works ok, but Oracle connection in SSIS packages doesn't work! So, let's do the next steps:

as the x64 ODAC driver doesn't work with SSIS in x64, I've tried to make ODAC working with SSIS in x32 subsystem.

Steps:
1. Download and install by default ODTwithODAC1020221 (x32).
2. BIDS, DTExecUI, DTExec are started with next command files:

set path=C:\oracle\product\10.2.0\client_odac_2_x32\BIN;%path%;
"C:\Progra~2\Microsoft Visual Studio 8\Common7\IDE\devenv.exe"

set path=C:\oracle\product\10.2.0\client_odac_2_x32\BIN;%path%;
"C:\Progra~2\Microsoft SQL Server\90\Tools\binn\VSShell\Common7\IDE\DTExecUI.exe"

set path=C:\oracle\product\10.2.0\client_odac_2_x32\BIN;%path%;
c:
cd \
cd "C:\PROGRA~2\Microsoft SQL Server\90\DTS\Binn"
DTExec /Server SERVERNAME /SQL "Test Oracle Driver Package" /Reporting E

In x32 subsystem all works.

About This Blog

I'll post here interesting and important things about Business Intelligence solutions based on the Microsoft BI stack (SQL Server, SSAS, SSIS, SSRS, .NET Framework and C#, Windows Server). I use MS SQL Server and Oracle Database as a data warehouse database platform.