Wednesday, February 24, 2010

OBIEE: Text File as Data Source

I have a requirement to pull data from a text file. OBI has the ability to pull from (just about) any database, but can it pull from a text file? Of course.

First I'll create a simple file, call it test.txt. In the file I put
cjustice testing
that "space" between cjustice and testing is actually a tab character (usually represented as -> if you have view formatting turned on). Knowing that it is a tab will be important shortly. Save the file to your root folder, i.e. c:\test.txt

Next up, go to your ODBC Datasources:

Control Panel --> Administrative Tools --> Data Sources (ODBC)

When that opens up, navigate to the System DSN tab and click on Add.

You'll be prompted to choose a driver, select the Microsoft Text Driver (*.txt, *.csv)

select driver

Click on Finish.

Next up, you should see the ODBC Text Setup screen, click on the Options tab to expand the window.

options tab

It should look like this:

odbc text setup expanded

Uncheck the Use Current Directory checkbox (default is usually c:\windows\system32):

uncheck current directory

Then select the Select Directory button and navigate to your root folder, or the place where you saved the test.txt file. You should see your file, greyed out, in the left hand pane.

select directory

Now, at the bottom of the window, uncheck Default (*.*) which should enable the Define Format button

define format

Select the Define Format. For the Format drop down, choose Tab Delimited. Rows to Scan, in my case, will only be 1. You can leave it at the default if you want though.

After you have done that, the Guess button should be enabled for you, go ahead and click it.

define format step 1

See F1 and F2? Those are your columns. Let's name them something meaningful so we can easily reference them later.

In the Name box, it says F1, enter in USERNAME and click on Modify (the button to the right):

modify column name

Do the same thing for the F2 column, name it PASSWORD.

It should end up looking like this:

final screen

Select OK and then select OK again in the ODBC Text Setup screen. You should now see your new data source in the System DSN window.

system dsn

OBI EE Admin Tool
Now, we need to pull this new data source into the RPD. Open up your RPD, go to File --> Import --> from Database

You'll see this window, go ahead and select your new data source:

select data source

You'll be prompted with the Import window, expand the c:\ drive and then find your file

find file

Select Import (at the bottom), wait for it to complete and then close the window. In your physical layer you should see a new "database" with the name "text_test" (or whatever you named your data source). Go ahead and expand that to see what you've got:

physical layer

To verify, right click on the table "test.txt" and select View Data.

view data

All done.

8 comments:

Embeegee said...

I am trying to use XML as a datasource - haven't been able to find anything searcing via Google. You'd think it was easy given that the default sample OBIEE ships with Paint/Sales have XML as their datasources! Or have I overlooked something obvious?

Thx,
MBG

oraclenerd said...

@embeegee

I haven't tried an XML file yet, but it's on my to-do list.

Have you taken a look at the connection pool to see how it's defined?

chet

Embeegee said...

Thanks for your reply. I did look at the connection pool - didn't think I needed to create an ODBC until I saw your post, was just trying to connect to a physical XML file. Then I saw your post and tried to create an ODBC entry, didn't find a driver in my list. Now I'm trying a hack by creating a copy of Paint rpd and modifying it to point to my XML. I am pretty new to OBIEE...

Thanks,
MBG

oraclenerd said...

MBG,

Go here and download the XML and XSL file (cdcatalog.xml and cdcatalog.xsl).

Then go to Import --> Database and in the drop down, select XML. Then find the location where you saved those 2 files, select the XML file first and the XSL file second (XSLT stylesheet).

It should pop up the Import box and you should see the file (table).

Let me know if that works...I'll probably write it up later today as well.

chet

oraclenerd said...

MBG,

I wrote it today. Thanks for the push.

XML File as Data Source

chet

Mariano said...

if there is some limitations about the number of the records? or some performance issue?
we talking about at least 1 millon's record

Unknown said...

I have followed all above steps still not able to see dsn in data source
OS i am using is windows 7 64 bit & OBIEE 11g

oraclenerd said...

Prachi,

Without more information, there's not much I can do to help.

You could try the forums for more help though:

https://community.oracle.com/community/business_intelligence