As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at firstname.lastname@example.org and provide your corporate email address, company, and name.
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.
Posts: 2 | Location: Seattle | Registered: February 25, 2016
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.
Dennis Tuttle WebFOCUS 8.201M iSeries V7.2 Active, HTML, Excel
Posts: 12 | Location: Maryland | Registered: June 01, 2017
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.
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.
Pilot: WebFOCUS 8.2.06 Test: WebFOCUS 8.1.05M Prod: WebFOCUS 8.1.05M Server: Windows Server 2016/Tomcat Standalone Workstation: Windows 10/IE11+Edge Database: Oracle 12c, Netezza, & MS SQL Server 2019 Output: AHTML/XLSX/HTML/PDF/JSCHART Tools: WFDS, Repository Content, BI Portal Designer & ReportCaster