Hi,

We have a project requirement which need to create large reports running by thouands of users. These reports will make queries against multiple data sources(Oracle, Domino, Web Service and flat file).

We have two options: One is by WebFocus to access those data sources directly and another is to create data mart between WebFOCUS and data sources since we are considering fetching data optimized in WebFocus.

Anyone has the same situation before? What is your opinion?

Thanks in advance.


-Maria

Data mart if the data does not have to be realtime. Though you could rebuild with Report Caster at intervals during the day.

Browser-based reporting should be as fast as you can make it.

Maria, please add your product suite, releases, and platform(s) to your profile signature so that we can better help you.

Maria

If you say multiple data sources then my first idea is creating a data mart. The advantage is already mentioned. The disadvantage could be that the data is not always real time.
Building a data mart it self is a separate project, you should know a lot upfront. What are the reports, complexity, number of fields, indexes, (de)normalization, how do you handle with the static data (which are not always real static)

Just some thoughts, and not really easy to answer....

Thanks for your response.

We are using WebFocus 7.11 hosted on the Window 2000, web server in WebLogic 8.1. We also have Lotus Notes adapter, Oracle adapter and web service adapter.

Building data mart means huge work load. For example, since we have data sources as Oracle, web service, Domino. If we try to build data mart, we keep the same type of data source or transfer all of them to Oracle? If transfer all to Oracle, we may not need to Web service adapter and Lotus Notes adapter.

So my understanding is that we can utilize WebFocus for fast design and development. But again, how about performance issue related to data fetching as it will be running by thousands users?

Also, if it is run by Reporting caster, i will assume it will not allow the user to select parameter, is that correct?

Maria,

You would build the data mart with Report Caster, not the reports.

As Frank indicated above, the building of the data mart should be separate from the reports but you could build the data mart with WebFOCUS utilizing the adapters you currently have. You could store the mart in Oracle or a FOCUS file or a flat file (not recommended, can't use indexes).

Create a file design that incorporates data from all of the sources, might be in separate files or tables, but make them all the same type. That way when you report against them in a join structure, the query will be efficient.

Also, I am assuming that when you mention multiple data sources, you are talking about accessing them in the same program. If not, then just go against the one that you need with lots of WHERE clauses and indexes and such.

If you need to combine them in a single query, a data mart is your best bet. Hetergeneous joins are bad.

Also put that release information that you gave us in the signature section of your profile. That way, it will show up every time you post.

If your objective is fast response for your customer base and you don't need real time information for most or all of your reporting needs, datamart would be best. The design of the datamart can be intimidating. Do you need to maintain long history or is the purpose of your project to look at more current information.

We have a datamart that uses IB IWAY to build our point in time census information.

Thanks for all your input.

Based on your input, since our application don't need real time data, datamart will be the best option.

How to creat data mart with WebFOCUS utilizing the adapters I currently have, where to store it? On WebFOCUS or another server?

Leah, Thanks for your help. Can you show me some architecture templatesT so that i can have a whole picture how it looks like.


It is my first time to deal with data mart.

Thanks a million for your support.

-Maria
-----------------------------------------
Prod: WF 7.1.1; Win2k; WebLogic 8.1
Dev: WF 7.1.1; Win2k; WebLogic 8.1
Primarily self-service;Oracle, SQL Server,

Maria,

We have had good luck with the following archtecture:

All 'live' operational data for our business transaction based ERP system is Oracle.

A 'replica' copy of selected ERP tables needed to support Business Intelligence reporting is maintained in MS/SQL. This keeps BI reporting from degrading ERP performance. Every 5 seconds a 3rd party data replication product updates these replica tables. They are always up-to-date for BI activity.

Another set of MS/SQL 'tables' has been designed to optimize BI ad-hoc and scheduled queries. They utilize FieldNames and data formats that are most meaningfull to end users. If reporting applications and users need tables joined, filtered, or aggregated to get data, those processes are included and thus invisible to query writers. I've called these 'tables' because sometimes they are true physical MS/SQL tables, and sometimes they are only MS/SQL Views of the replica tables and/or special MS/SQL tables. All true MS/SQL tables in this schema are updated or recreated daily or more often by MS/SQL scheduled procedurs.

This has produced an overall environment where all newly developed WebFOCUS reports MRE/RCaster/Dashboard query the 'pre-digested' data and all run very quickly from data that is as up-to-date as needed.

Of course, we have a lot of "legacy" reports feeding directly from Oracle, and we're either redesigning them to the new architecture, or switching them to the replica tables.

We've pulled this off in only a 2 years with 2-3 persons that are FOCUS knowledgable and with the assistance of a good MS/SQL DBA.

Chris Burtt

Maria,

Don't have a template, we used MySQL for storing information and some open source design tools. I was on the fringe of the effort. Use the IWay data migrator for the population of the information.

If you don't have Data Migrator, you can use Report Caster in a pinch. People here do it all the time to build data marts.

Maria, there is no template. Based on your reporting requirements, you will have to design your mart. What kind of file you store it in is up to you and your design requirements.

It seems most of your original data sources is Oracle or SQL.

My situation is a little bit different, we have data source from web service and Domino. It will be a huge effort if we want to replica data from web service and Domino. Since we have already bought adapter from IBI for web service and Domino, it maybe a easier way to replica these data through those adapters. But i don't know if it is doable.

As suggestion, we can build data marts through Report Caster. Do you have any document link for it?

Thanks

-Maria


-----------------------------------------
Prod: WF 7.1.1; Win2k; WebLogic 8.1
Dev: WF 7.1.1; Win2k; WebLogic 8.1
Primarily self-service;Oracle, SQL Server

I think you are not understanding what we are saying, Maria. A data mart is not a replication of what you already have; it is a subset.

Therefore based on your report design criteria, you will have to design a data mart that handles that design. The recommendation would be to extract what you need from all your disparate data sources and combine them in a single data source (may be multiple tables). You can do this in a focexec. Then you schedule the focexec to run in Report Caster.

As for a link, please go to the IBI techsupport site and look for the documentation menu item. You should be good to go from there.

Maria

I think that as I already said, your case is maybe a bit to complex to do it just by some advices here.
Thousands of users, millions of records sounds impressive but I'm sure IBI can give you several examples of clients who are bigger than that.
The design is however very important when you talk about performance.
I would not recommend to do all that reporting on the same machines, you might need more than one, and beneath that you have to worry about uptime, back-ups etc.

We use ETL (Datamigrator) and that works great. We do the daily extract and build of a small data mart.

I would suggest to spend some money on a proper investigation, or else start with a small pilot.

Thanks for all your input.

Since we are developing reports through developer studio, i am not familiar with focus language.

Ginny: What is focexec? Is it the executable file? If yes, where do we store the subset when running this focexec in the report caster?

Thanks a million.

Maria
----------------------------------------------
Prod: WF 7.1.1; Win2k; WebLogic 8.1
Dev: WF 7.1.1; Win2k; WebLogic 8.1
Primarily self-service;Oracle, SQL Server

Maria,

A 'procedure' in Dev Studio creates a 'focexec' in the application directory that you are working in.

Where the 'subset' is stored is up to you and depends on the type of file that you are creating. If it is a fixed for FOCUS file, putting an APP HOLD appdirname in your procedure or focexec would create the file in that directory provided your procedure contains an ON TABLE HOLD AS filename FORMAT format.

I think if you don't know what a focexec is, you may be in way over your head. Have you checked into training or consulting?

Ginny;

Thanks for your help.

Most of our reports created are SQL reports. I will assume it is dynamic. Once the user make the selection for parmaters in the drop down list, the fex file will take it and talk with the Oracle through connection string.

If we use fixed focus file to store the subset(datamart), how can we let the another report(fex) to read the data from this fixed file?

Thanks

-Maria

Maria

If you say "SQL reports" I suppose that means reports based on a SQL database.
You select the fields with a SQL script and holds the results in an SQLOUT file.
An other way to do this is creating an master file based on the SQL database or SQL view.
Once the master is created you can build your report in DevStudio by selecting the fields and placing them in the report.
If you execute the fex it creates the sql script in the background and puts the result in your final report.
The data is not stored in an other datamart or file unless you want that to be done.

BTW ask for some training, in the end that will be more effective than asking it here. We can better give help if you understand the basics.