We've been working with WebFOCUS for a couple of years and the one single thing that made the biggest impact for us was moving to sql based synonyms.

We don't do any joins and very rarely add metadata through the synonym editor.

I've had many cases opened through the help desk and even worked with a few field engineers and it seems like the concept just isn't something that they have been familiar with at all.

So I'm curious to know how many others are doing the same thing or if many of you even know you can. The question occurred to me as a potential Summit topic.

Any feedback would be welcome.

Thanks,

This message has been edited. Last edited by: FP Mod Chuck,

In 99% of cases I've seen, masterfiels are created on tables.

Hello DennisET,
I am in the Seattle region, and i have shown several customers how to use SQL queries as start point for their Synonyms.
I would urge you to look more into the power of the metadata layer. Building defines and using the BV/ Dimension views adds alot of power to the tools, let alone establishing a trusted set of computed columns for end users to use. This will greatly enhance the experience for people using IA+ to create content.
Yes, a Presentation at summit on using SQL queries as base would be a good addition to the event.

Hello Randy,

One of the reasons we got into SQL synonyms was because the way joins worked in the editor. All reporting fields had to be in a top-down hierarchy so there would often have to be several joins to the same table in order to use the same detail. In some cases my sql has 20+ tables with different layers of aggregation that I'm not sure how I could have even done using the cluster join editor.

From a maintenance perspective, adding calculated fields in the SQL query is also easier for us. Date conversions, type casting and even field formatting is all done in the sql so the synonym itself is just a list of fields.

Admittedly our target audience isn't the power user working with IA+ but the end report consumers. All the columns are vetted and when many different reports are generated the totals always exactly tie with each other. Most of the time it's a few different key fields looking at the same summary from a different perspective. And we've built apps that make it easier for the users to make those pivots without having to run additional reports.

I used the GUI tools for a year and it was a big struggle each time I needed to add data to the model. Rather than throwing errors my reports would have completely unrealistic numbers which I found were based on how the joins worked, duplicating records thousands of time and converting a simple order totals around 40,000 into numbers in the trillions. I spent more time trying to work through making our data work the way the tools needed than using the tools to get the reports we wanted.

Honestly, I think our project would have failed if I hadn't been able to generate the data models entirely in SQL and then generate a synonym from that for the reporting. Someone more experienced who knew how to work around the issues would have also succeeded, but the ability to fail so miserably in the hands of new users isn't exactly the story IBI pushes, just the opposite.

Don't get me wrong about the products, they provide a platform that is serving us well. We just had to learn to work around part of the development environment to achieve success ourselves.

I have just been surprised that what we found as a critical key for success doesn't seem to be widespread in the community.

A good Masterfile can be difficult on some databases and I think you have this with any tool. Generating masterfiles is done in a couple of seconds, but joining the togheter, checking SQL, etc can take days, but this is something you have to do with any tool.

Nowadays you have possibilities to put some SQL expressions inside masterfiles, this can already help a lot for specific solution and still use the masterfile.

We have had considerable success creating synonyms against complex stored procedures. WebFOCUS treats the output from the stored procedure as a dataset.

I can forward you my slides from a webinar I gave on this subject.

Thanks Michael, I would like to see your materials. I sent a direct email with my contact details.

Dennis - Thank you so much for starting this thread!

I am part of a development team that creates portals, reports, and visualizations for business users.

We use the WF Synonym Builder to build access/master files (.acx/.mas) for views, tables, and stored procedures (Oracle). We have also used HOLD FORMAT SQL_SCRIPT to create basic subqueries (inside the WHERE clause).

We then JOIN, in our WebFOCUS code as required. Our standard during development is to turn on SQL tracing, to observe the SQL generated, and insure that it is optimized.

We are starting to get requirements that necessitate joining to subqueries (in-line views) and subquery factoring (WITH clause).

I've been thinking about the best way to handle these requirements, when I read this thread.

So just now, I popped my subquery factoring clause into an external sql script (.sql) and used the Synonym builder to build my .acx/.mas files.

I then JOINed from my table to the subquery and much joy and happiness ensued.

So again, many thanks for starting this thread.

Regarding your feedback request:
* No, I didn't know you could create sql script based synonyms. And now I do! 
* It sounds like using only sql script based synonyms aligns perfectly with your user requirements, and the WebFOCUS product.
* Yes this topic would make an excellent Summit presentation.