My current assignment involves building Analysis Services cubes from a SQL Server datamart, populated from an Oracle 10g database server, using SSIS. 

I spent some time over the last few weeks using the Microsoft connector for Oracle by Attunity (available for download here) to populate my SQL Server. In terms of performance, this new connector is a vast improvement over the old Microsoft OLEDB Provider for Oracle (MSDAORA) or the Oracle OLEDB provider (ORAOLEDB.Oracle). If you were previously using a linked server to an Oracle database to populate a SQL Server database, you definitely want to check out that connector. 

One thing that took me some time to find out was how to parameterize the sql command that you pass to the connector.
In the SQL Server connector, you can use a variable containing your view name or your sql command, and you pass this variable to database driver.  

In the Microsoft connector for Oracle by Attunity, not so obvious.. There is nothing in the screen that allows you to use a variable to parameterize your sql command.  

All you can do in this screen is choose either a table name or build a hard-coded sql command. 

After much fiddling with the interface, I finally got around to parameterize my sql command. The trick is to display the properties of the parent container (in this case, the data flow).  There, using property expressions, you can freely modify the Oracle sql command.  

Click on ‘…’ in front of “Expressions”. In the Property listbox, select SqlCommand

  Click on ‘…’ next to “Expressions” and start building your sql statement using the Expression Builder.   

Enjoy!    

 

SQL Server 2005 Books Online (December 2008)

https://www.microsoft.com/downloads/details.aspx?FamilyID=be6a2c5d-00df-4220-b133-29c1e0b6585f&displaylang=en

This release of SQL Server 2005 Books Online includes documentation updates for the SP3 release.

Mosha delivered a new version of MDXStudio (v0.4.11). Available here.

 

Analysis Services does not provide a way to sort dimension members in DESCending order, only in ASCending order.

Fortunately, this sort functionality is easy to implement, using the Data Source View.

Replace your table binding with a named query and create a new calculated column that you will use to order your dimension members.

Here is an example of such query:

SELECT DateKey, (-1)*CONVERT(int, Date, 112) as Days, Date FROM T_DATE

Where 'DATE' is your datetime column and 'DATEKEY' is your primary key.

Then, in the dimension definition, select the dimension key and set the following properties:

OrderBy: AttributeKey

OrderByAttribute: Days

 

And voilà, your dimension members are now ordered by the new attribute you created!

 

 

Sometimes, you need to change the BACK_COLOR of a cell, based on the result of an evaluation, such as 'Change color to YELLOW if Condition A is TRUE'. But what if you don't want to change the BACK_COLOR if Condition A evaluates to FALSE (and leave the back color as it was)? Surprisingly, the answer is quite simple... Just set it to NULL.

For example, change the BACK_COLOR to Yellow if the flag 'NewFact' is greater than 0, otherwise leave it alone:

BACK_COLOR(this) =

    IIF([Measures].[NewFact] > 0

    , 65535 /*Yellow*/

    , NULL /* Don't do anything */) ;

Very clean, simple and it won't mess with any pivot table formatting theme you might have chosen.. I love it!

A cool demo of Microsoft Surface showing interesting visualizations of BI data. This demo was shown at the Microsoft BI Conference in Seattle earlier this year.

Very shortly, I'll be closing the Microsoft chapter and will be entering a new and very exciting territory. I'm creating a small consulting company, specialized in Microsoft Business Intelligence designs and architectures. As you can imagine, taking this decision after having worked at Microsoft for so long was not an easy one to make. But the long and heated discussions I had with many of my friends and co-workers convinced me that this was the right choice to make.

I'm really greatful for the time I spent at Microsoft. I've had the chance to work with amazing people, both in Paris (France) and in Seattle, WA (USA). This company has taught me so much that I'll never have enough words to express my gratitude. Now is the time to return the favor and spread the word in the field about the huge potential of its platform.

Point your browser to http://www.declic-consulting.com and your RSS Reader to http://www.declic-consulting.com/blog if you'd like to stay in touch!

-- Patrice.

Declic Consulting, http://www.declic-consulting.com.

Just came across this announcement of a new Oracle connector for SQL Server 2008. This will be a boon for those wanting to bulk load data from SSIS into an Oracle database. Using the Oracle proprietary interfaces to load data should be significantly faster than using the traditional Microsoft or Oracle OLEDB providers...