Archive

Archive for October, 2010

5 Best Practices for Telling Great Stories with Data

October 25, 2010 Leave a comment

I enjoyed reading this short article today. It is sponsored by Tableau Software, a business intelligence software vendor for analytics and business dashboards.

Data we are moving around, querying every day has stories to tell. Communicating effectively with business leaders largely depend on how we present the data effectively.

I especially liked the example from a consultant about caring for her nephew and relating that story to how data should be “on time”, “as promised”, and should allow people to “follow up”.

image

Categories: Uncategorized

SSRS 2008 – Text Properties

October 21, 2010 Leave a comment

I recently started to use the SQL 2008 Reporting Services in BIDS 2008. There are many nice enhancements in the new version. The first enhancement I noticed is the new Report Data window. Report Data can be viewed by going to View menu then select View Data option at the bottom of the option list.

image  

Report parameters, and Images, and Datasets are nicely organized into their own folders, and no longer hidden.

image

The second change you would notice right away is that everything is a Tablix now. Although both Table and Matrix are available as Report Items in the Toolbox, they are both a type of Tablix with configurable Row Groups and Column Groups. This enhancement makes it a lot easier for data grouping/summarizing.

But the feature I like right away is the ability to set properties for texts, instead of being forced to set properties for the entire textbox as in BIDS 2005.

image 

I am able to set part of the text in a textbox to blue, underlined, and jumping to a URL.

Categories: SSRS Expert

SSRS – Using a Case Statement in a SQL Where Clause

October 18, 2010 Leave a comment

I rarely use case statements in SQL where clause. But in SSRS reports, I find myself use it occasionally. It’s not hard to code it, it’s only the syntax that is tricky. I am very much against writing complex SQL code for any SSRS reports. A stored procedure will be a better solution for complex logics. Using staging is also common for data you need to bring over from other sources.

Goal

There are times in SSRS reports where a different field will be used in the where clause, depending on user’s selection of one or two report parameters. In this example, when user selects “Power On” as the Report parameter, the [PowerOnDate] will be used in the where clause; with the selection of “Power Off” as the Report parameter, the [PowerOffDate] will be used in the where clause.

Solution

You can write dynamic SQL. I am strongly against dynamic SQL, unless it’s your only solution. You can write a stored procedure with IF statement to construct different SQL statements depending on the parameter. I find a stored procedure is a little overkill in this situation. So I chose to write a straight SQL code with a case statement in the where clause.

image

Here is the code with a case statement in the where clause.

image 

Here is the part that shows the syntax.  

image

Categories: SSRS Expert

SSIS – Connecting to 64-bit Oracle (2)

October 18, 2010 Leave a comment

Here is an in-depth discussion about how to make SSIS work with 64-bit Oracle client.

Here is an extract from the above article. I’ll leave all these investigation/installation to our future DBA. For now, I’ll just use the linked server to work around this issue.

image

Categories: SSIS Best Practices

SSIS – Connecting to 64-bit Oracle

October 18, 2010 Leave a comment

Problem

There is no problem with the linked server to the Oracle database on the 32-bit or 64-bit SQL Server. However, on the 64-bit server, SSIS connection to the 64-bit Oracle database failed.

Notice that the Log on to the server section is grayed out.

image

Here is the “oraoledb.oracle.1 is not registered” error from SSIS.

image

Potential Issues

1) Mismatch between 32-bit and 64-bit. I doubt this one. Because the SQL Server is 64-bit on this machine.

2) The port is not open on the firewall. What is the port number for SSIS to connect to an Oracle database?

3) Truly missing the driver. 

Analysis

Since the SSIS connection to the Oracle on the 32-bit SQL Server is working, I’ll start investigation from there.

Here are all the Providers installed on the 32-bit SQL Server.

MSDAORA: Microsoft OLEDB provider for Oracle

MSDASQL: Microsoft ODBC provider

OraOLEDB: Oracle Native OLEDB provider for Oracle

SQLOLEDB: Microsoft Native OLEDB provider

image

On the 64-bit SQL Server, we are missing MSDAORA. I don’t think this is the problem. Since we are not going to use the Microsoft OLEDB provider for Oracle anyway.

image

On the 32-bit server, using either Microsoft OLEDB provider for Oracle or Oracle Native OLEDB provider for Oracle worked fine in SSIS.

Using either Microsoft OLEDB provider for Oracle

image

Using Oracle Native OLEDB provider for Oracle

image

Solution

No solution so far. But since the linked server is working, I’ll use the linked server to extract raw data from the Oracle database.

Categories: SSIS Best Practices

where are BIDS and SSMS?

October 18, 2010 Leave a comment

On one of our servers, the application shortcuts to BIDS and the Microsoft SQL Management Studio are missing for some reason. I finally found them.

SQL Server 2005 SSMS = SqlWb.exe

"D:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe"

SQL Server 2005 BIDS = devenv.exe

"D:\Program Files\Microsoft Visual Studio 8\Common7\IDE\devenv.exe"

SQL Server 2008 SSMS = Ssms.exe

"D:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

SQL Server 2008 BIDS = devenv.exe

"D:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe"

Categories: SSIS Best Practices

Database Developer

October 17, 2010 Leave a comment

Adding to the job description collection.

image

Categories: Uncategorized
%d bloggers like this: