Maxim Oleznyuk's BI Blog

Microsoft Planning Scenarios

2011-11-26T22:55:00.000+01:00

http://www.microsoft.com/download/en/details.aspx?id=19191

SQL Server Technical Articles

2011-10-24T10:42:00.003+02:00

If you work with Microsoft's BI stack, these articles can be very useful.
SQL Server Best Practices
SQLCAT Articles
Analysis Services 2008 R2 Performance Guide

Microsoft Enterprise Architecture

2011-09-18T14:49:00.001+02:00

Here you'll find technical content, news, and community resources covering enterprise, application, and systems architecture.

Microsoft Application Architecture Guide, 2nd Edition

2011-09-13T00:24:00.000+02:00

I highly recommend the Microsoft Application Architecture Guide, 2nd Edition for developing solutions using the Microsoft platform and the .NET Framework.

Microsoft SQL Server Large Data Warehouses

2011-09-10T21:31:00.000+02:00

Here are some links to the articles about the performance of SQL Server large data warehouses:
MSDN: The Data Loading Performance Guide
MSDN: We Loaded 1TB in 30 Minutes with SSIS, and So Can You
MSDN: Introduction to New Data Warehouse Scalability Features in SQL Server 2008
and many more Microsoft SQL Server 2008 White Papers and Microsoft SQL Server 2008 R2 White Papers

Implementing Best Practices for Large Analysis Services Databases

2010-08-17T22:04:00.000+02:00

Watch here

Downloads 2008

2008-12-04T22:33:00.001+01:00

SQL Server 2008 Enterprise 180-day evaluation
SQL Server 2008 Books Online
Visual Studio 2008 Downloads

SQL Server 2005 Enterprise Evaluation Edition
SQL Server 2005 Service Pack 2
SQL Server 2005 Books Online
Visual Studio 2005 Professional Edition 90-Day Trial
Visual Studio 2005 Team Suite 180-Day Trial
Visual Studio 2005 Service Pack 1

SQL Server Samples & Community Projects
BIDS Helper
MDX Studio

BI Architecture and Design Guide

2008-03-28T12:07:00.003+01:00

BI Architecture and Design Guide.

SQL Server 2005 Logon Triggers

2008-01-30T12:46:00.001+01:00

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)

2008-01-29T07:39:00.001+01:00

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

MERGE Statement in MS SQL Server 2008

2008-01-29T07:33:00.002+01:00

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

2008-01-24T11:15:00.003+01:00

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

2008-01-24T11:06:00.000+01:00

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

2008-01-21T20:17:00.000+01:00

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

2008-01-21T16:23:00.001+01:00

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

2008-01-21T14:52:00.002+01:00

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

2008-01-19T18:10:00.001+01:00

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

2007-11-20T07:34:00.001+01:00

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

2007-11-20T07:30:00.001+01:00

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:

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.

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

2007-10-11T11:12:00.002+02:00

Mosha Pasumansky announced his MDX Studio.

Microsoft SQL Server Webcasts & Events

2007-10-11T11:02:00.000+02:00

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

2007-08-27T19:06:00.000+02:00

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

2007-08-18T22:03:00.001+02:00

Here you can download SQL Server Samples & Community Projects.

BIDS Helper

2007-08-10T14:04:00.002+02:00

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

Integration Services

General

Enable/Disable features

SQL Server 2008 July CTP

2007-08-03T16:10:00.001+02:00

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)

2007-07-20T12:32:00.002+02:00

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

2007-07-20T11:30:00.003+02:00

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.

News

2007-07-19T12:39:00.000+02:00

July 12, 2007
CTP3 of Microsoft Office PerformancePoint Server 2007 with new Monitoring and Analytics functionality is now available!
You can download the files within the "Download" section of Microsoft Connect (https://connect.microsoft.com/default.aspx). Prior to getting access to the download page you must take the mandatory survey (if you haven't already) and agree to the Licensing terms. Additional product information about Office PerformancePoint Server 2007 can be found at www.microsoft.com/performancepoint.

June 30, 2007
Microsoft® Visual Studio® 2005 Service Pack 1 is available.

June 12, 2007
Updated SQL Server 2005 Books Online (May 2007) are available for download here.

June 04, 2007
SQL Server 2008 (Katmai) Community Technology Preview (CTP) is available for download here.

March 29, 2007
SQL Server 2005 Analysis Services Performance Guide goes live!

March 06, 2007
SQL Server 2005 Service Pack 2 is available for download here.

Best Practices and White Papers

2007-07-18T19:53:00.001+02:00

Analysis Services
SQL Server 2005 Analysis Services Performance Guide
OLAP Design Best Practices for Analysis Services 2005
Analysis Services Processing Best Practices
Real-time Business Intelligence Using Analysis Services
Identifying and Resolving MDX Query Performance Bottlenecks in SQL Server 2005 Analysis Services
Analysis Services Many-to-Many Dimensions: Query Performance Optimization Techniques

SQL Server Data Warehousing
SQL Server 2005 Data Warehousing
Business Intelligence and Data Warehousing in SQL Server 2005
SQL Server 2005 Data Warehousing White Papers
Strategies for Partitioning Ralational Data Warehouses in Microsoft SQL Server
Choosing Microsoft SQL Server 2005 for Data Warehousing
High Performance Data Warehouse with SQL Server 2005
Manage Large Data Warehouses with SQL Server 2005

Documentation and Info

2007-07-18T08:19:00.000+02:00

SQL Server Home
Microsoft SQL Server 2005 Home
Microsoft SQL Server 2008 Home

SQL Server Analysis Services
MSDN: SQL Server Analysis Services Books Online
MSDN: SQL Server Analysis Services Tutorials
TechNet: SQL Server 2005 – Analysis Services

SQL Server Integration Services
SQLIS.com - SQL Server Integration Services
MSDN: Integration Services
MSDN Forum: SQL Server Integration Services

SQL Server Database Engine
MSDN: SQL Server Database Engine Books Online
TechNet: SQL Server 2005 – Database Engine

Other
MSDN: SQL Server 2005
MSDN: SQL Server 2005 Books Online
SQL Server 2005 Project REAL
Microsoft BI and PerformancePoint Server
Microsoft Office PerformancePoint Server
The many-to-many revolution by Marco Russo

Oracle
Oracle Database Online Documentation 10g Release 2 (10.2)

Literature

2007-07-17T19:04:00.002+02:00

Here are links to some useful books.
I've chosen all these books as very helpful for developing Business Intelligence solutions on the MS BI Platform.

Data Warehousing and ETL
The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling , 2nd Edition by Ralph Kimball, Margy Ross
The Data Warehouse ETL Toolkit: Practical Techniques for Extracting, Cleaning, Conforming, and Delivering Data by Ralph Kimball, Joe Caserta
The Data Warehouse Lifecycle Toolkit: Tools and Techniques for Designing, Developing, and Deploying Data Warehouses by Ralph Kimball, Laura Reeves, Margy Ross and Warren Thornthwaite

Microsoft BI
The Microsoft Data Warehouse Toolkit: With SQL Server 2005 and the Microsoft Business Intelligence Toolset by Joy Mundy, Warren Thornthwaite, Ralph Kimball
Microsoft SQL Server 2005 Analysis Services (SQL Server Series) by Edward Melomed, Irina Gorbach, Alexander Berger, Py Bateman
Applied Microsoft Analysis Services 2005: And Microsoft Business Intelligence Platform by Teo Lachev
Microsoft SQL Server 2005 Integration Services (SQL Server Series) by Kirk Haselden
Fast Track to MDX by Mark Whitehorn, Robert Zare, Mosha Pasumansky
MDX Solutions: With Microsoft SQL Server Analysis Services 2005 and Hyperion Essbase by George Spofford, Sivakumar Harinath, Christopher Webb, Dylan Hai Huang, Francesco Civardi
Data Mining with SQL Server 2005 by ZhaoHui Tang, Jamie MacLennan

Microsoft SQL Server
Inside Microsoft SQL Server 2005: The Storage Engine by Kalen Delaney
Inside Microsoft SQL Server 2005: T-SQL Programming (Pro-Developer) by Itzik Ben-gan, Dejan Sarka, Roger Wolter
Inside Microsoft SQL Server 2005: T-SQL Querying (Solid Quality Learning) by Itzik Ben-Gan, Lubor Kollar, Dejan Sarka

.NET Framework and C#
CLR via C#, Second Edition (Pro Developer) by Jeffrey Richter