Is Power View supposed to be a replacement for Oracle BI Answers in OBIEE 11g

In response to my last blog, I received the question: ‘Is Power View supposed to be a replacement for Oracle OBIEE 11g?’ This blog is aimed at answering that question. Before we start, however, I would like to say that these opinions expressed here are mine, and no-one else’s opinions. Therefore, if I’ve got some of the Oracle details incorrect, please do feel free to correct me and I will be grateful that you’ve pointed me in the right direction.

Power View, as I’ve said previously, is about helping business users to ‘surf’ their way through their data. It is designed to help them to think fast about the results of the data, and ask questions of it. It is therefore contrasted with other reporting packages which require a more ‘developer’ oriented focus, such as Business Objects WebI, Microsoft SQL Server Reporting Services.

My experience with users would make me put Oracle BI Answers in more of a ‘developer’ pot rather than a ‘think as fast as you click’ pot. If you take a look at the Microsoft Books Online Power View documentation, it shows you how easy it is to create small data visualisations in Power View. There is no discussion of variables, or syntax, or anything that requires typing.

On the other hand, when we look at Oracle BI Answers manual, we find that the discussion changes to include variables, custom date/time strings, formatting results and so on. Although this may be in the reach of many business users, it isn’t for everybody. This is in contrast to Power View, which requires minimal typing, drag-and-drop functionality with no requirement to create new formulae, making it simpler to use and assumes that the underlying data model is clean, correct and in place.

I’m not criticising the Oracle BI Answers solution; instead, I’m saying that it seems aimed at report writers who expect to do additional work to meet a particular reporting requirement. On the other hand, Power View is aimed at those users who expect the data model to be cleansed and sorted for them already, without requiring further work to deliver the data visualisation other than point, click and publish.
To answer the initial question: Is Power View supposed to be a replacement for Oracle OBIEE 11g? I don’t think that is the case. I look forward to your comments on this question.

Connecting SSIS and Oracle: Issue and Resolution #1

Connecting from SQL Server SSIS to Oracle can be problematic, to say the least! I’m logging them on my blog as I find them. One error message that turned up recently was the following:

 cannot resolve connection

This can mean that there is a line missing from the Oracle file called SQLNET.ORA. For some older implementations of Oracle, the Oracle TNSNAMES.ORA file may have the connections defined as .world rather than simply HOST.

This is resolved by ensuring that the SQLNET.ora file contains the appropriate following statement:

names.default_domain = world

Alternatively you can remember to add ‘.world’ to all of your connection strings. The easiest thing, however, is to ensure that the SQLNET.ora file has the ‘.world’ information added.

Hope that helps!

SSIS and Oracle: challenge of ‘Unable to extend temp segment’ in Oracle

If SSIS is being used to retrieve large amounts of data from an Oracle database, the SSIS package may fail with the following error message:
ORA-01652: unable to extend temp segment by 256 in tablespace
This error basically means that Oracle could not extend the temporary segment for the required number of blocks in the user’s allocated tablespace.  There are a few solutions to this issue:
  • Increase the users temp tablespace
  • Although it’s an Oracle error, it may show up structural design issues with the package itself. Thus, the SSIS package could be changed so that, instead of one large query, the Oracle database is serving up smaller chunks of data at a time. This may mean ‘daisy-chaining’ a number of components, who each serve up smaller parts of the larger query that was initially being executed.
As always, it’s best to have a chat with your friendly Oracle dba owner of the source database for their thoughts.

Add to Technorati Favorites

Creating Oracle Tables using SSIS

It’s possible to use SSIS to create a new Oracle table for you. However, there are a few ‘gotchas’ that it’s necessary to look out for.
It’s possible to use the ‘OLE DB Destination’ adapter to insert data into Oracle.  Thus, if a new Oracle table is required, it’s possible to select the destination Oracle database, and use the ‘New’ button to generate code for you automatically. This will bring up a text editor, which may have some content similar to the following snippet:

“ETLLoadID” VARCHAR2(255),
“Column1” NUMERIC(10),
“Column2” NUMERIC(10)

This will create a table called “MyTable” for you, which initially sounds like good news. When the SSIS package runs, it will deliver the data into the destination Oracle table.
Don’t get comfort from those green SSIS boxes, however. It’s important to be careful with those quotation marks.
Thus, when you try to select from the Oracle table, using the following statement, it won’t work:
select ETLLoadID from MyTable
This does not work, and generates the following error message:
ORA-00904: Invalid Column Name
Then, the select statement does not work; it needs quotation marks around it.  This will work better:
select “ETLLoadID” from MyTable
In order to find out exactly how the table has been implemented, it’s possible to use the DESCRIBE command. This is like SP_HELP, and provides you with data you need for the table.
Instead of using SSIS to generate tables, I now create my own tables in Oracle since it gives me the control I need, and means that I don’t have to worry about quotation marks. If you need a useful link to help you with the syntax, here it is.
I hope that helps!

Add to Technorati Favorites