OLTP vs OLAP (Data Warehouse) as a DataSource in Analysis Services 2005

Hello,

Can I import an OLTP (Reltional DB) as a Data Source into SQL Server
Analysis Services 2005 and then use the Cube Wizard and the new Data
Source View feature to create the OLAP model ?

Or do I have to first design an OLAP Data Warehouse with a Star Schema
and then import this DW as a Data Source into my Analysis Services
Project.

With SQL Server 2000 , OLAP would be the way to go..but with SQL
Server 2005 , it seems as though the wizard and data source view
features do half the work for you.

I have an OLTP DB and am not sure which route I should take ! Any
suggestions / input would be much appreciated.

Thanks in Advance...

Regards
RusszeeHere's the followup answer to my own post...

Just what I thought...It's the new UDM all the way in SQL Server
2005 !!!

"SQL Server 2005 Analysis Services can create UDM cubes without the
intermediate step of building a star schema data warehouse. The UDM
enables organizations to build reporting applications directly against
a production system and doesn't require an intermediate data warehouse
as in the past. This is because the UDM doesn't require the data to be
in a star or snowflake schema, but can connect to any data source
whose data is stored in a third normal form. "

Source : http://www.microsoft.com/technet/pr...5/solvngbp.mspx

OleDb Source Error - Connecting to Oracle 9i

Hi,
I have my datasource in Oracle9i and destination is Sql2005. I am connecting to Oracle 9i through OLEDb provider and when I connect to my DataSource using OLE DB Data Source, I get the following error:

Warning at "guid code": Cannot retreive the column code page info from the oledb provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale Id will be used.

Could anyone please help me trouble shoot this problem.

Regards,
Sudhakara.T.P.
sudhakaratp@.hotmail.com

Hi,

Click on the OLE DB Source component and check for the properties, you have to keep the property "Alwaysusedefaultcodepage" to true.

Hope this will help you.

Regards,

Raju

|||

Hi Raju,
Thank you very much for your help.
Well, I was not aware of this option and it helped me a lot to finish my work.

Regards,
Sudhakara.T.P.
sudhakaratp@.hotmail.com

OLEDB Datasources and parameters

I have discovered some shortcomings in the way inline table valued function parameters are treated in the OLEDB datasource. You can select the user designed function ine the Generic Query Builder and test it with the required parameters. However when you attempt to set up the parameters for the result ing SQL Command Text you get and error message to the effect that the parameters cannot be retrieved from the datasource. Once again this is disappointing because Report Services seems to deal with the parameters perfectly well.

Dick Campbell

I've never had any problems using parameters in OLEDB Sources. How are you defining the placeholders for your parameters? For OLEDB they should be a single "?".
A sample SQL statement would look like the following:
Select Col1, Col2 from MyTable where MyDate between ? and ?
The first ? would map to Parameter0 and the second would map to Parameter1.
Larry Pope
|||I am using ? as you suggest but I am calling an inline table valued function. The format is "select * from function(?,?,?) as function".

OLE DB DataSource w\ stored procedure not populating column metadata

I'm having some issues getting OLE DB Data Sources to work w\ stored procs in SSIS. Here's the situation.

I have an OLE DB Data Source set up to call a stored proc w\ no parameters. The stored procedure loops through a set of databases and inserts data from each database into a results table. I'm attempting to return the results table to SSIS, but the Available External Columns are not populating. However, previewing the query in SSIS does show results. The insert in to the results table is done by a call to sp_executesql.

I've tried setting the results table up as a temp table, table variable, and static table. I have NOCOUNT set ON and am only returning one recordset. I've seen the other threads in here about similar problems, but none of their solutions seem to work for me.

Any help would be much appreciated....

I believe that there are known problems around using SPs in OLE DB Source components. Its something to do with requiring that the query that returns the data is the first statement in the proc. This enables SSIS to understand the metadata of the returned result set.

I know this doesn't help you in your case though. I do not know if there is a workaround or not having never faced this problem before myself. I hope what I've said above goes some way to helping though.

-Jamie

|||I think OLE DB Command will execute the Store proc|||Try converting the sp to a multi-statement table-valued function, giving Data Access Mode for your OLE DB source as SQL Statement and specifying the statement SELECT * FROM your_table_valued_function(); the wizard will gussy the SQL up