Hi,

Can we build cubes off the text files / data files instead of from
relational tables. Please advice.

Thanks,

Sam

Yes, you can do this. You need a flat file ODBC reader. I've used the Data
Direct flat file reader (http://www.datadirect.com) without any problems.
The Micfrosoft flat file reader does not supply enough metadta information
to be ran with Analysis Services.

--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


"Prasad" <Prasad (AT) discussions (DOT) microsoft.com> wrote

Thanks for the useful info.

Does this mean we also have to build dimensions this way. Also how will we
define the relationships (links as we define with cube editor) between
dimensions and the flat file.

Can you please share any documentation or is there a place in "BOL" that i
can refer to?

Best Regards,

Sam.

"Dave Wickert [MSFT]" wrote:

Hi Dave,

I was at this website and i could not identify the appropriate driver to
load flat file. Can you please be more specific?

Thanks,

Sam.

"Dave Wickert [MSFT]" wrote:

Yes. It was the ODBC Driver 5.1
See this page:
http://www.datadirect.com/products/o...onnectodbc.htm
It has a text driver. An evaluation version is available.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


"Prasad" <Prasad (AT) discussions (DOT) microsoft.com> wrote

You have to configure the system so that dimensions are never joined to fact
tables.
This means it must be fully optimized (Optimize Schema wizard in the Cube
editor).
I've used the driver for both dimensions; fact table partitions, and both.

--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


"Prasad" <Prasad (AT) discussions (DOT) microsoft.com> wrote

Thanks Dave.

Did you gain any time by loading the cube from text file Vs a relational
table? Also is there any docuemnation / bench marks.

Thanks,

"Dave Wickert [MSFT]" wrote:

I've never seen any published performance numbers.
I typically only use this approach in 2 scenerios:
1) the only way to move data is flat files, e.g. dealing with older systems
without direct connectivity, or systems w/o ODBC access (for example,
porting a system from Essbase since it doensn't have an ODBC driver), or
2) if the native ODBC drivers which have performance problems -- for
example, a project I was on used some of the older DB2 ODBC drivers which
considerable throughput problems. This wasn't evident with one or two
streams, but doing 10-12 partitions in parallel was terrible. Moving the
data via flat files allowed us to parallelize the processing and get much
better overall througput.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


"Prasad" <Prasad (AT) discussions (DOT) microsoft.com> wrote

Hi Dave,

Thanks for all the information.

I installed ODBC 5.1 Text Driver from Data Direct. I created a DSN with the
text file as source and defined all columns.

Now when i build the cube using cube wizard, i was able to select the Data
Source (Text File) and bring in the text file as my fact table. But the issue
is that all my dimensions are created off the DB2 relational tables using a
different Data source.
Hence they are not availble for selections as "Existing Dimensions".

My question is there a way to includes 2 Data Sources while creating th
cube. 1) for Dimensions built from the DB2 relational tables. 2) Text file
that acts as a fact table.

Thanks very much for your help.

Sam.

"Prasad" wrote:

Sorry, No.
AS2K only supports one data source.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


"Prasad" <Prasad (AT) discussions (DOT) microsoft.com> wrote