Much Ado About RStat… By Ali-Zain Rahim As you’ve probably noticed, currently there is a lot of buzz around predictive analytics and Business Intelligence. Though not new, both technologies have become quite popular. As a result, we have received a number of inquiries from customers and prospects about Information Builder’s integrated predictive platform, WebFOCUS RStat. With those inquiries, come some questions that I will endeavor to address in this article. As long as I am at it, I will also share information about new functionality in the latest release of RStat and include a brief outline of the roadmap of future developments. First things first, what is RStat? Part of Information Builders’ business analytics suite, RStat is a fully integrated BI and data mining environment that delivers forward-looking views of business operations and the ability to review past events. RStat and WebFOCUS help companies build and deploy predictive models based on reliable information. Some of the most common questions we receive have to do with the limitations and differentiation between the trial version of RStat and the licensed version, and the expiration of the trial version. The trial version is a completely functional version, just like the licensed version. Licensing basically gives you the capability to deploy, as opposed to use, the RStat techniques. For the trial version, the user is able to export the model and deploy it to a Developer Studio Reporting Server, which is the server that Developer Studio starts up if no remote WebFOCUS Reporting Server is configured.. This server is also referred to as the “localhost” on the development machine. These are the same models that you can deploy to a licensed environment. The only constraint with the trial version is it expires 60 days after installation. The reason for this is so that when the software is downloaded, it has a purpose and use. If not, it could be an indication there is no buy-in from the stakeholders and cause challenges for the project. It is possible to extend the trial, but at current that is by special request and requires management approval. So what happens on day 60 of the trial? You get the message in the R console that appears on Screen 1. Does this mean the work you have already done (i.e., output models) will no longer work? No, but you will need a licensed version to continue to use RStat’s GUI functionality.

Screen 1 Another common question is about copying the .dll to another server. No, this is not supported. The deployment must be done on the machine that the developer will use to create the reports. This leads to questions about the compiler: what version to use. The MinGW from the Information Builders downloads site has two versions – 32bit and 64bit. Download the one that matches the WebFOCUS Reporting Server on which your application will run; i.e., 32-bit WFRS requires a 32bit MinGW compiler. Simply put, you must match to the Reporting Server, not the operating system. Sometimes the deployments may still not work, even after the compilers are correctly installed. This is because the compiler was installed on a 64-bit machine, which needs to be restarted after the compiler is installed. After the common questions, we have some not-so-common, but more frequent, questions. One of the most frequent questions is how RStat’s output models compare to other tools. RStat output is based on Open Source R, which is supported by a consortium of more than 1 million developers and supporters. This question usually comes from users who are not getting the results they expect.

In troubleshooting, we typically find in these cases that the results would have been correct if the data being fed matched the requirements of the model. Yes, data going in can change the output of the model. That means you need to know the required fields and field types of a model, and then pass what is needed. I will cite two examples. One was a customer project and the Gamma algorithm (new functionality added in RStat 1.5 (Screen 2) and the other involved an internal staffer trying to create a demo. In both situations, we found that the data types being provided into the model to create the scored output were incorrect. The models provided output, but the numbers were totally off.

Screen 2 New Functionality Previously I mentioned new functionality in RStat 1.5, and I will leave you in suspense about it no longer. Here are some details:

1. Change the SVM "option" blank entry box to a dropdown list with all choices: • C classification: C-svc • nu classification: nu-svc • bound-constrant svm classification: C-bsvc • Crammer, Singer native multi-class: spoc-svc • Weston,Watkins native multi-class: kbb-svc • novelty detection: one-svc • epsilon regression: eps-svr • nu regression: nu-svr • bound-constraint svm regression: eps-bsvr

2. Enable NNet "skip" option as a drop-down list with value TRUE (default) or FALSE 3. PMML export for Ada boost 4. Rpart Rule Export Columnar - Export sorted decision tree rules in separate columns 5. Advanced GLM GUI - Add more family options and link functions: • Family options include: Normal (Linear), Gaussian, Poisson, Logistic, Multinomial,

Binomial, Negative Binomial, Gamma, Inverse Gaussian. • Link functions include: log and identity.

For a future release, we are looking at the following list of functions and are open to any suggestions from our customers. We are aiming for the third or fourth quarter of 2014 for the release, depending on the complexity of the algorithms and functionality.

1. Anomaly detection 2. Chi-Square test 3. Bayesian Network algorithm 4. Association Rule Sequence Detection 5. Stepwise as a regression model option 6. Forecasting models and time series 7. ARCH & GARCH Models in Time Series 8. Confirmatory Factor Analysis 9. MCA - Multiple Correspondence Analysis 10. UDF export for Hyperstage, Greenplum, Netezza

For future RStat article series, we will revisit RStat best practices and also look at possible reasons for challenges associated with the predictive projects:

1. Not having a specific goal in mind at the beginning 2. Waiting for perfect data 3. Thinking it is only data that you need, and that no is SME needed 4. Shooting for the stars on the first try 5. Garbage in, garbage out 6. Thinking that you have all stakeholders on board 7. Creating a model but having no way to present it

Installing a Standalone Distribution Server By Gerry Snyder Customers often prefer to install the ReportCaster distribution server on a separate machine from the WebFOCUS client. And even when the distribution server resides on the same machine as the WebFOCUS client, a failover distribution server typically will be installed on a separate machine. Installing a standalone distribution server is a straightforward process. Whether you have a typical or a customized install, simply uncheck WebFOCUS as a component of the installation (Screen 1).

Screen 1 On the next screen, supply the connection information to the WebFOCUS repository of your existing WebFOCUS installation. Typically, the WebFOCUS client has already been installed and a repository has been created. You want the distribution server to connect to that repository. See Screen 2.

Screen 2

As an option, change the default hostname and port number. Click “Next” and then “OK” to install (Screen 3).

Screen 3 Let’s look at the installation of a standalone distribution server. Notice that under the WebFOCUS80 directory (Screen 4), there are other directories in addition to the ReportCaster directory. In particular, there is a client directory and a configuration directory. Both store files used by the IBFS security system.

Screen 4 When IBFS is configured for External Authentication or Authorization, the configuration settings are stored in the webconfig.xml file located in the config directory. If the External Authentication or Authorization setting is WFRS, the WFRS node information is stored in

the odin.cfg file in the client directory. See Screen 5 for an example of the Security settings in the WebFOCUS Administration Console.

Screen 5 Notice the Authentication Type is WFRS, the User Group Membership is EXTERNAL, and the External Group TYPE is WFRS. This means that in addition to verifying the user’s password, the reporting server also will be providing the user’s Group membership. These settings are stored in the webconfig.xml file under ibi\WebFOCUS80\config. Notice also that the WFRS Authentication Node is EDAAUTHN. EDAAUTHN is a node defined under Reporting Servers in the WebFOCUS Administration Console (Screen 6).

Screen 6

The definition of EDAAUTHN is stored in the oding.cfg file under ibi\WebFOCUS80\client. See Screen 7.

Screen 7 When the distribution server executes a schedule, it connects to the WebFOCUS repository through the IBFS installed with it to retrieve the scheduled report for execution and verify that the schedule owner is authorized to create and run the schedule. The distribution server uses the webconfig.xml and odin.cfg files installed with it and residing in the ibi\WebFOCUS80\config and ibi\WebFOCUS80\client directories of the machine where it is installed. If these files are not synchronized with the files on the WebFOCUS client, IBFS will not be able to authenticate the schedule owner and the schedule owner will not be authorized to access the scheduled report. The schedule will fail. The solution to this problem has three steps:

1. Copy the updated webconfig.xml file from the …ibi\WebFOCUS80\config directory on the WebFOCUS client to the …ibi\WebFOCUS80\config directory on the distribution server.

2. Copy the updated odin.cfg file from the …ibi\WebFOCUS80\client directory on the WebFOCUS client to the …ibi\WebFOCUS80\client directory on the Distribution server.

3. Edit the webconfig.xml file on the Distribution server and, if necessary, change any references to the WebFOCUS installation path to match the path on the Distribution server.

Step 3 is necessary because the machine with the WebFOCUS client may have the ibi\WebFOCUS80 directory in one location while the machine that has the distribution server installed may have the ibi\WebFOCUS directory in a different location. Screen 8 shows two webconfig.xml files from a WebFOCUS client and Distribution server installed on different machines.

Screen 8 In a future release of WebFOCUS, this information will be stored in the WebFOCUS repository, making it unnecessary to manually synchronize files on separate machines. Until then, keep the files on the distribution server in synch with the WebFOCUS client for successful schedule execution. Autocomplete and Maintain By Mark Derwin When asking users to enter data into a form, it is always a good idea to give them assistance. This could involve date pickers, list/combo boxes, radio buttons and checkboxes. In an upcoming version of WebFOCUS 8, we make this process even easier with a new property called Autocomplete. Autocomplete takes the letters that the user starts to enter and displays valid values that match the input string. There are multiple ways to determine what are the valid values for each field. I will discuss three ways of populating the lists. In past releases, we were able to simulate an Autocomplete feature by attaching JavaScript functions to the input field and populating an additional list box. And in early releases of WebFOCUS 8, we were able to use jQuery functionality to produce the same list that is now available automatically. In my example I have created a stack (STK) from the Movies sample file and placed it on

my form. When I select the Moviecode_Edit field, its properties sheet opens (Screen 1). Notice that there is now an Autocomplete property.

Screen 1 Selecting the Autocomplete property pulls up the “Insert Autocomplete Text” dialogue box. From this box, you can either hard-code the values to be displayed in the Autocomplete dropdown box or enter the name of a stack/field combination. I always recommend using the stack/field combination. Even if the values are static, I would rather compute them in the code then hard-code them into a dialogue box. In my code, I am displaying three ways of loading up a stack, which will serve as the values for the Autocomplete boxes. See Screen 2.

Screen 2

For the Moviecode field, I just load the stack directly from the database using the FOR ALL NEXT command. This command goes out to the database and loads in all of the Moviecode values. Since this is the key field, I know that all of the fields will be unique and that the retrieved records will be sorted. I attach movstk.moviecode as the Autocomplete value for the Moviecode_Edit field. For the ratings field, I create RateStk.Rate and just compute the five values into that stack. This is a static list that I then assign to the Autocomplete parameter for the Ratings_Edit field on my form. While this list could change, it is easier to edit it in the code than to have to edit it on the form. For the category field, I use the EXEC command to kick off a Table request (XGetCat) and bring back only the unique values for category. By doing this, I only get the distinct values and if an additional category is added to the database, it will automatically show up on my list. I assign Catstk.Category to the Autocomplete property of the Category_Edit field (Screen 3).

Screen 3 The last thing we need to do is change the z-index value for the fields that may be covered by the Autocomplete dropdown lists. I have changed the z-index for all of those editboxes to 0 (zero). This is so the list will be displayed over the other fields and not behind them. See Screen 4.

Screen 4 Now when we run the application, and start typing into the moviecode field, we get only the values from the database that contain the entered data. The same will happen for category and rating (Screen 5).

Screen 5

For this feature to properly display, you must run the application from a browser. If you run it from the MDE or Developer Studio, the viewer is launched, and this feature is not available from inside the viewer. The Autocomplete feature works exactly the same on handheld devices as it does on your browser. This feature makes filling in forms even easier for your users. Expanding a List by Clif Kranish One of the most common ways to exchange data between systems is the delimited flat file, sometimes called CSV for “comma-separated value,” although other characters can be used as a delimiter, such as a tab or stile. What if an individual field contains delimited values, and you want each value to read as a new row? As it happens, the same facility can be used. It doesn’t matter if the field is in a relational database or is a delimited flat file itself (with a different delimiter, of course). So consider the Movies database from movielens.org. It contains an ID, the title and a list of genres for each movie delimited by a stile (|) character (Screen 1).

Screen 1

Viewed in the Data Management Console, the synonym for this table looks like this:

Screen 2 To provide access to each value in the list, we can add a segment. To do so, follow these steps:

1. Open the synonym with the field that contains a list (Screen 2). 2. Right-click on MOVIES and select “Insert and Segment Manually.” 3. If the properties panel is not displayed correctly, click on SEG1 and select

“Properties.” 4. For the SEGSUF pulldown list, select “Delimited Flat File;” for the POSITION pull-

down, select GENRES; and for RDELIMITER, enter |. When you are done, the properties for the SEG1 segment should look like Screen 3.

Screen 3

Click on FIELD1. For FIELDNAME and ALIAS, enter GENRE; for ACTUAL enter A20V. See Screen 4.

Screen 4

Optional Step: If you don’t want the list field to appear in InfoAssist and other tools, you need to select the field, which here is GENRES. In the Properties panel, scroll down to ACCESS_PROPERTY and check the checkbox for INTERNAL. See Screen 5.

Screen 5 On the quick access toolbar, click on the save icon to save the synonym. Now you can use the GENRE field in reports. Note that when a movie has multiple genres associated it, you will get multiple rows of output (Screen 6).

Screen 6 You can also use GENRE as a BY field, as on Screen 7.

Screen 7 Note: This functionality requires Release 7.7.06 or 7.7.05M Gen 137 or 7.7.04M Gen 791.

WebFOCUS App Studio: SQL Report and SQL Chart Wizards By Maria Volant In WebFOCUS Developer Studio, users interested in creating SQL reports would normally use the SQL Report Wizard. In the last screen of the SQL Report Wizard, users would be presented with the option to turn the request into a chart or a report. In our new WebFOCUS App Studio Windows-based GUI product, which will replace WebFOCUS Developer Studio, users can choose from the start whether to create a SQL report or a SQL chart. This allows users to select data that is more meaningful for the type of procedure they are trying to create. App Studio now gives users access to the SQL Report Wizard and introduces the SQL Chart Wizard. App Studio employs the Microsoft Windows ribbon framework to provide a development environment that lets users build advanced reports much more easily and quickly. When you start WebFOCUS App Studio, you see the Home tab across the top of the App Studio window (shown by the red rectangle), which provides simple-to-use and intuitive options for creating files and procedures (Figure 1).

You use the Report button on the Home tab to access the SQL Report Wizard, and the Chart button to access the SQL Chart Wizard. Like the SQL Report Wizard in Developer Studio, the SQL Report Wizard and the SQL Chart Wizard in App Studio enable you to

execute SQL commands that retrieve data from an RDBMS via a SQL pass-through query. Note that in order to build a report or chart with the SQL Report or Chart wizard, you must first configure the WebFOCUS Reporting Server with one of the supported database engines (e.g., Microsoft SQL Server, Oracle, etc.). To start creating an SQL report, click the Report button on the Home tab. This will open the Report Wizard shown on Figure 2.

Clicking the Create SQL Report button in the Report Wizard opens a navigation dialog that allows you to select the location for the SQL report. Once you select the location and click Next in the navigation dialog, the SQL Report Wizard - Welcome screen opens, as shown on Figure 3.

From here you can proceed to include, type or import the desired SQL statements. When you click Finish in the SQL Report Wizard - Summary of SQL options screen, the App Studio Report canvas opens with the resulting SQLOUT table, where you can complete and save your SQL report. To create a SQL chart, you can start by clicking the Chart button on the Home tab. This opens the Chart Wizard, as shown on Figure 4.

As with the Report Wizard, clicking the Create SQL Chart button in the Chart Wizard opens a navigation dialog where you can select the location for the SQL chart, and clicking Finish in the navigation dialog opens the SQL Chart Wizard - Welcome screen, as shown on Figure 5:

Once you have included, typed, or imported the desired SQL statements, clicking the Finish button in the SQL Chart Wizard - Summary of SQL options screen opens the App Studio Chart canvas, where you can complete and save your SQL chart procedure. The SQL Report Wizard and the SQL Chart Wizard features in App Studio are pretty similar to the SQL Report Wizard feature in Developer Studio, but accessing these facilities is easier and more intuitive. For more information on the SQL Report and Chart wizards, or on any of the many features available in App Studio, please see the online help. Why Don’t My Numbers Make Sense? By Art Greenhaus There’s nothing worse than going through all the gyrations needed to produce a requested report, only to have the numbers appear to be “off.” Sometimes, PEBKAC (problem exists between keyboard and chair) is the cause, in that a calculation was specified incorrectly. Other times, it’s GIGO (garbage in, garbage out), in that the data was

corrupt, and could never be made to match. And of course, sometimes the cause has to do with how computers do things. As a simple example, consider the following: TABLE FILE CAR SUM SEATS PCT.SEATS BY COUNTRY ON TABLE COLUMN-TOTAL END Regardless of each COUNTRY’s total for SEATS, one would expect the total to be 100 percent. Yet, the output is this: PCT COUNTRY SEATS SEATS ------- ----- ----- ENGLAND 15 20 FRANCE 5 6 ITALY 10 13 JAPAN 8 11 W GERMANY 34 47 TOTAL 72 97

Obviously 97 percent is not quite 100 percent, so where did the missing 3 percent go? Well, in this case, it’s a function of how the percentage is formatted. The default format for any prefixed field is the format of the field on which it is operating. (The exceptions to this default are CNT. and PCT.CNT., which we’ll discuss later.) In this case, the format for SEATS is I3, which store only whole numbers and display only three digits. What that means is the “true” percentage for ENGLAND’s SEATS should be 20.833333… percent, but because of the format, we only hold the 20. The decimal portion (.83333…) is lost. This loss of decimal values, for the entire report, adds up to 3 percent. So, what can you do about it? Some try to dynamically change the format to something that keeps the decimal component, like this: TABLE FILE CAR SUM SEATS PCT.SEATS/D6.2 BY COUNTRY ON TABLE COLUMN-TOTAL END Unfortunately, the conversion to the new format is done after the calculation using the original format. The result is hardly any better:

PCT COUNTRY SEATS SEATS ------- ----- ----- ENGLAND 15 20.00 FRANCE 5 6.00 ITALY 10 13.00 JAPAN 8 11.00 W GERMANY 34 47.00 TOTAL 72 97.00 With that said, perhaps the easiest way to get the desired result is to re-DEFINE the original field, with a new format: DEFINE FILE CAR SEATS/D3 = SEATS; END

This will transform the original I format, to a D, which will internally keep the decimal components, and round as needed. The result using this is much better: PCT COUNTRY SEATS SEATS ------- ----- ----- ENGLAND 15 21 FRANCE 5 7 ITALY 10 14 JAPAN 8 11 W GERMANY 34 47 TOTAL 72 100 As mentioned, there are two prefixes that do not take the format of the field in which they are operating: CNT. and PCT.CNT. The reason is fairly obvious. You can COUNT a field that is alphanumeric, such as SUM CNT.COUNTRY. The default format for a CNT. prefixed field is I5, which may be expanded to I9, with issuance of the SET COUNTWIDTH=ON command. In a similar vein, the percentage of the count, as part of the total count (PCT.CNT.) has a default format of F6.2 (under the premise that you would get a maximum of 100 percent). A second issue, though less serious, can still be thought of as not making sense. Consider the following: DEFINE FILE CAR XX/I1 WITH COUNTRY = 1; END TABLE FILE CAR SUM XX TOT.XX COMPUTE PERCENT/F6.2 = 100 * (XX/TOT.XX);

BY COUNTRY WHERE COUNTRY EQ 'ENGLAND' OR 'FRANCE' OR 'ITALY' ON TABLE COLUMN-TOTAL END This produces the following result: TOT COUNTRY XX XX PERCENT ------- -- ---- ------- ENGLAND 1 3 33.33 FRANCE 1 3 33.33 ITALY 1 3 33.33 TOTAL 3 9 100.00

While it may appear correct, someone may ask, “How can three records of 33.33 add up to 100.00?” As we saw before, it’s because, internally, floating point formats (D or F) store more digits of precision than they display, and round on output. However, for an accountant, where the decimal portion represents cents, this is not acceptable. Is there any option? Of course there is. (I wouldn't raise the issue if there were no options, would I?) If, instead of defining the calculation as a F6.2 format, I use a P6.2 format, packed formats store digits, up to the precision requested, the result becomes the following: TOT COUNTRY XX XX PERCENT ------- -- ---- ------- ENGLAND 1 3 33.33 FRANCE 1 3 33.33 ITALY 1 3 33.33 TOTAL 3 9 99.99

While mathematically more correct then before, we're still left with the issue of the sum of percentages not adding to 100 percent. Unfortunately, this is one situation that cannot be resolved both ways. Either we add the column, or we recalculate the percentage. The fact that they're slightly different is something that will always occur, because we're trying to deal with a number (1/3) that cannot be represented exactly in a finite number of digits.