Performance Point Server - Integrating SSRS2008 Report View

Hello Everyone, 

Here is another quick tip, Reporting Services 2008 would be fully supported using SP2 (see my previous post).
To those of you who need to use SSRS2008 today, using PPS SP1, here is a workaround:

Image 1: SSRS2008 Integrated Report

Thats It,

Have Fun,

Eran

Performance Point Server SP2 pre-release nuggets

Hello everyone,

The dev team has relased some nuggets regarding the upcoming release:
1. The support for SQL Server 2008 has been established:
    a. SSRS report view now supports the SSRS2008 connection.
    b. Setup up over SQL Server 2008

2. Support for virtualization environments:
    As previously mentioned by Norm's blog post:
   http://blogs.msdn.com/normbi/archive/2008/10/14/performancepoint-server-sp2-hyper-v-is-supported.aspx
   Performance Point Server would support virtualization (using Hyper-V),
   you can read further about it at:
   http://support.microsoft.com/?kbid=957006

    Since Planning Server requires wide topology, this issue could change the future architecture of planning implementation.

I'll update you as the Beta test proceeds.

Stay tune,

Eran

How to set Hide columns and Rows by Default – Analytical Grid & Chart

Analytical Grid and Chart, are wonderful reports to enhance your dashboard.

However they pose several hurdles that require us to overcome.

Since interfering with the MDX manually (such as setting NON Empty statement), could cause to navigation loose

(See the latest post at the performance point team blog: http://blogs.msdn.com/performancepoint/archive/2008/10/20/mapping-dashboard-filters-to-analytic-charts-and-grids.aspx)

We need a work around to hide empty columns and Rows by default.

The Scenario is very easy:

Step1 : Complete the Report Design using the Designer

Step 2: Switch to Browse mode:

1. Use the "Filter Empty columns" and "Filter empty rows" to activate filter.
2. Click the "OK" button to resume Design mode.

Step 3: Verify Filter Settings:

Check Settings had been enabled for the report

Thats it,

Publish report and have fun,

Eran

SQL 2008 Deep Dive Experience!

Hello Everyone,

I'm happy to invite you all, to an in-depth training on SQL Server 2008.

The training would be held at Airport City, December 7 -8 2008.

This two days training will reveal the accumulative knowledge as well as best practices for building, deploying and migrating to SQL Server 2008.

It's an opportunity to meet face to face with leading experts from the world (such as Solid Quality Learning), and leading experts from product team.

The training would ne conducted at 4 deferent tracks:

DBA, BI, Labs and Decision makers.

For more information:

http://www.microsoft.com/israel/promotionalMail/SQLDeepDive/index.html

See you all there,

Eran

SSIS 64bit – Using Oracle Provider

Hello everyone, running an Oracle Provider over a 64 bit system could be a pain in the…
The purpose of this document is to explain the main issues running an SSIS solution using Oracle as a data source under 64bit. This document would explain the problems. Offer solution and work  around procedures.

Symptoms:
When dealing with Oracle as a data-source we face two known issues:
1.            The case of the Numeric Type Casting:
                Oracle uses a data –type named numeric for numerical data. the numeric data 
                type  usually requires   setting precision format e.g. numeric(18,2). 
                While the OLEDB   Provider can handle the numeric type. It does so only when precision is set.when 
                facing a meta-data of type numeric where precision wasn't set. an exception is thrown requires us
                to use casting within the Oracle query. This is not to terrible if you have single table/query. But try 
                to imagine maintaining hundreds of queries ?

2.            Provider  Data rate:
                The purpose of 64bit hardware is to accelerate the performance.
                Using a 32 bit provider  on a 64bit hardware limit the performance of the package. (OLEDB              
                Provider uses COM objects and x86 type system).

Solution Scenario:

The Following solution would handle both issues. The proposed solution is to download and setup a 64 bit generic provider. The only question is which provider and what scenario ?

 Prepare Step:  Remove Previous Oracle Client and or Installations

1.       Using Oracle Install to remove all Oracle objects (including ROOT)

2.       Remove specific Oracle Assemblies from GAC:
Look under %WindowsDir% for the Assembly folder and remove all Oracle specific Assemblies (Mainly Oracle Policy and Oracle Data Provider).
beware not to remove Microsoft generic assemblies (System.* or Policy..Microsoft)

 Phase 1: Upgrading to 10g (64bit) 10.2.0.3 - Provider
The following phase would increase your provider throughput and allow you to execute a 64bit calls to the Oracle Hardware, However it wouldn't solve your Numeric casting issue.
This step is supported by two 64bit Hardware schema:

1.            For x64 base Hardware:
                Download and Install the specific provider for x64.
                http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html

2.            For IA64 based Hardware (Both IA64 and Itanium 2):
                Download and install the specific Itanium provider.
                http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html

                Remark:               Keep in mind that specific Itanium Processors, such as Montecito                                             requires additional patch offered on the same link!.         
               

Phase 2:  Establish an Oracle Connection
1.            Use your Oracle DBA to establish your TNS_ADMIN Settings and set your             "tnsnames.ora" file.
2.            Use the Registry to set a TNS_LANG with equivalent settings as Oracle Server.

Phase 3:  Upgrading  to Oracle Data Access Components (ODAC) 11g  - 11.1.0.6.21
This phase would solve the need to cast the SQL query to support the numeric data-type. By using the Oracle Managed Provider for .NET. a Package can be developed without the need to cast the numeric field. Performance Test shows and estimated throughput of 1Milion rows per minute.

Users of the x64 hardware, please download and install the following component:
Oracle 11g ODAC and Oracle Developer Tools for Visual Studio

http://www.oracle.com/technology/software/tech/dotnet/utilsoft.html

Phase 4:  Establish an Oracle Connection
Since the 11g ODAC usually setup an additional Oracle Root , we need to establish additional Oracle Setting for the additional Oracle 11g Root.
1.            Use your Oracle DBA to establish your TNS_ADMIN Settings and set your             "tnsnames.ora" file.
2.            Use the Registry to set a TNS_LANG with equivalent settings as Oracle Server.

3.            The Registry Setting for the 11g provider , would appear under the WOW64 entry  within the registry.

Using the SSIS designer within visual studio, be sure to choose the following provider:

1. Download the Oracle 11g ODAC and Oracle Developer Tools for Visual Studio for x86 (32 bit) and 
   extract the assemblies files.
   
2. Using the GAC utility to register assemblies
   
3. Go to machine.config of the 64bit which could be located at:
   %%WindowsDir%\Microsoft.NET\Framework\x64\v2.0.50727\Config
   And look for the Oracle Data Provider which is located in the "<system.data>" section.
   Copy the settings (alter to match the public key of the x86 assemblies) to the machine.config file of 
   the x86 settings.
   
4. restart the server 

Do not forget to execute SSIS package using either SQL Server Job (Execute SSIS package step), or by using the dtexec command line.

Eran