Connecting Progress DB to SQL Report Model

[hambo12]

Ok, big question this one:

I want to create a report model project in MS Visual Studio 2005, connecting to a Progress Openedge Database.

I can successfully connect to the progress database, and pull some data from SQL Reporting Services. However, when I try to create a report model project, I get errors.

Firstly, I create a new data source, and test the connection, and it works fine. The Connection method is ODBC, using "Progress Openedge 10.2A Driver".

I then attempt to create a Data Source View. I can see the tables, and choose which ones I want to include, however, when I click finish, I get the following error:


===================================

ERROR [HY000] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Syntax error in SQL statement at or about "[PUB].[AHFaultAction]" (10713) (pgoe1023.dll)

------------------------------
Program Location:

at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.DataWarehouse.Design.DataSourceConnection.FillDataSet(DataSet dataSet, String schemaName, String tableName, String tableType)
at Microsoft.AnalysisServices.Design.DSVUtilities.ExportDataSet(DataSourceView& dsv, DataSourceConnection conn, ArrayList tableList, ArrayList viewList, ArrayList sysTableList, ArrayList sysViewList, IServiceProvider serviceProvider, ProgressBar progressBar)
at Microsoft.AnalysisServices.Wizards.DSVWizardForm.OnFinish(CancelEventArgs e)

========================

Doing a bit of searching around, and I have found this is most likely an issue with SQL putting square brackets around the statement, which Progress doesnt recognise.
Relevant topic: http://www.progresstalk.com/showthread.php?p=351153

Is there any way to fix this?

I am really doing my head in with this problem. It is holding back some major advances...

 

[tamhas]

Is there any way to fix this?

Don't use Visual Studio?

There is no simple fix from the Progress end. You are going to have to change the SQL.

 

[hambo12]

Damn, thats not good to hear. We have a plan to integrate all reporting services with Sharepoint etc. Its a major push to have all databases integrated with SQL, so I have to get this working...

 

[tamhas]

Note that the problem is only VS putting in the brackets automatically, so don't let it do that. I don't know anything about how it works, but I would think you could assemble your own SQL strings and send them over.

 

[hambo12]

Note that the problem is only VS putting in the brackets automatically, so don't let it do that. I don't know anything about how it works, but I would think you could assemble your own SQL strings and send them over.

Yeah thats just it - How is this done...

 

[tamhas]

No idea ... it's a VS question, not an ABL or SQL question.

 

[hpicon]

Hello, In my Opinion if you really needs to developer with .Net applications , you could use app services using the proxy generator and making direct connections.

the ODBC is obsolet.
:biggrin:

 

[tamhas]

ODBC is just fine for reporting and many other applications. If the .NET end needs to act like an interactive client, then, yes, moving to AppServer or Sonic is indicated, but fit the tool to the need.

 

[4GLNewbie]

In my opinion it's better if you find a different way to go on.

Find a report builder that works correctly with progress ( crystal reports, just to say a name ) maybe using java connection i suggest.
Find into the documentation of the report builder if someway it is usable in association with the .net environement.

Maybe you find out something.
It would be nice if you develop the report-side in a separate way, so you can maintain it easily.

 

[doom1701]

In my opinion it's better if you find a different way to go on.

Find a report builder that works correctly with progress ( crystal reports, just to say a name ) maybe using java connection i suggest.
Find into the documentation of the report builder if someway it is usable in association with the .net environement.

Maybe you find out something.
It would be nice if you develop the report-side in a separate way, so you can maintain it easily.

Report development in SQL Report Services works just fine with Progress, but creating models to enable the client tool is what breaks. My solution is data warehousing things into SQL, and building report models off of that. Sure, on the one hand, it's duplicating some data, but ultimately I don't want my clients being able to hinder the performance of the Progress database, even if I had the ability to design some of the report for them in a Report Model.

 

[tamhas]

I'm always a bit suspicious when someone tells me they need to move reporting off of the production database because of the load. It happens, I'm sure, but I wonder how many times the actual problem is a poorly writing query or missing index and the like. There is an inherent problem with shoving information off to another database because it is always out of date and typically only partial data. That might have been a reasonable compromise in the past when horsepower was expensive and disks slow, but it is really necessary now?

I suppose the solution of replicating the database for DR and reporting on the replicate is pretty reasonable one ... but that's not SQL Server.

 

[doom1701]

I'm always a bit suspicious when someone tells me they need to move reporting off of the production database because of the load. It happens, I'm sure, but I wonder how many times the actual problem is a poorly writing query or missing index and the like. There is an inherent problem with shoving information off to another database because it is always out of date and typically only partial data. That might have been a reasonable compromise in the past when horsepower was expensive and disks slow, but it is really necessary now?

I suppose the solution of replicating the database for DR and reporting on the replicate is pretty reasonable one ... but that's not SQL Server.

I'm detecting a little bit of dislike for SQL Server.

None the less, if you think you can design a database that can effectively query (indexed or not) 5 years of sales data and accumulate totals for numerous periods for comparison, all while having no performance affect on your production system, then you are a database god--or at least you don't work for Infor.

The bulk of the ERP designers that are developing ERP systems around Progress couldn't design a database to catalog their comic book collection, though.

 

[tamhas]

I've done some similar things using Actuate on a separate server, but accessing the production database. There was more performance issue from heavy production tasks than from reporting.

 

[4GLNewbie]

The question that i have is..

You have all the five years data online on your software production system? Then as user i would expect that reports analyze all data that i can query into the software..
And that surely will affect your db performances..

But your system will already be affected by n-years inserted data sooner or later!

What i can say it is that you can:
1. build some views to divide oldest data and this year one, i.e. .. and go on leaving all data together ( and your reports will be divided into quick ones and slow ones )

2. move oldest data to another db ( removing it from the one you are using right now ) and create a second production site to let the users view the oldest data and query this one only in read-only ..

I assume you will not update oldest data.
I Hope that is of some help.

 

[tamhas]

There is no reason that a big table will be slower or poor in performance unless you are lacking the right indexes or otherwise doing poor queries. Dividing data into old data and new data simple complicates the reporting process. There is no reason to read five years of data unless you want to report on five years of data and, if that is what you want to do, the only way to make it faster is to denormalize it to provide report oriented summaries so that you don't have to read so many records.

 

[4GLNewbie]

Sorry but i do not understand the idea that all data ever been inserted in a system must be maintained online on the same db, but may be it is my personal fault.
Even using indexes if there is a lot of things to read will take a longer time than if you have less things to read.. am i wrong?

Oh it's only my personal opinion, i want to say this clearly.. it would be my personal way to proceed.

So do you think if he recreates statistics or exports and imports all the data, it will go better?

 

[TomBascom]

You shouldn't keep data that you don't need.

But if you do need the data then having it in a single db is obviously simpler than having it in multiple databases.

Yes, dumping & loading, running update statistics and adding indexes if they are needed will definitely help. Update statistics can be a huge win for SQL reporting. Especially if it has never been done.

Having lots of data might impact reporting processes -- but only because you are processing lots of data. Lots of data should never impact OLTP processes because you should be using a proper index for all such access and indexed data access should not be sensitive to table size. (One way to tell that your indexes need attention is that if OLTP access degrades with db size you either are using the wrong indexes or you lack the correct indexes...)

 

[4GLNewbie]

So he could try to regenerate the statistics, analyze the queries to discovery if they're properly structured and check if this two things speed up the reporting processes.

If it does not work, he could think if he needs all five years data or not.. and what could be the solution.

Good luck!

 

[tamhas]

There is nothing wrong with 5 years of data as long as you ask reasonable questions. If you want to look at the sales of a particular item over 5 years and have an index on item, then you aren't reading anything like the whole table. If you try to report on quarterly sales totals for 5 years, then you are going to be reading the whole table and should expect the impact. The only way around the latter problem is to de-normalize and provide other summary tables.