Maintaining prompt selections in multiple Web Report Sections (v4.3)

Angela Hall
Angela Hall|January 21, 2013

SAS Technical Support has a wonderful how-to guide for using one set of prompt values across multiple sections of a Web Report Studio report. This works great in 4.2, however there is one additional item that I discovered in a 4.3/9.3 install. With the addition of PROC STP and SAS Information Maps using this new proc when calling the stored process, the prompt values in a stored process are not forwarded automatically to the information map or any other mechanism using PROC STP without the use of the OUTPUTPARM option. Since Information Map Studio creates the PROC STP code automatically using the metadata available for the stored process, you must define the output parameter in the prompt/parameter definition area.

For the SetDate parameter definition (step 7), you also need to select the 'New' button in the Output Parameters area at the bottom of the Parameters screen. Then create two output parameters for Year and Month.

  1. In Step 7 of the original instructions, note that at the bottom of the Parameter page is the button for creating a New Output Parameter.Click New for Output Parameters
  2. Select the New button twice and create two output parameters, one for Month and one for Year. Enter parameter information for both month and year
  3. The end result is that the bottom of the Parameter page looks like this:Results from Output Parameter Creation

For the GetDate stored process the original instructions said you can skip step 7, but in version 4.3 you will need to define the output parameters just as you did above so that these two values are passed.

You can see the result in the "Prdsale with Year Month Prompt" information map if you select Test, pick the Month Year Filter and choose 'View SQL'. You will then see the following code:

PROC STP program='/Reports/SetDate(StoredProcess)';
    outputParam month year;
run;

More detail on how PROC STP works is provided in my 2nd book The 50 Keys to Learning SAS Stored Processes.

Post a Comment

Cleaner OLAP cube physical folder structures

Angela Hall
Angela Hall|November 5, 2012

Each spring and fall, I start clearing out the kid's small clothes, old toys, etc. It can sometimes go too far and reach into my "digital pack-rat" issues. (Wait, what does that mean? Well, just ask the person who last week requested detail on a project I worked 3 years ago, I keep everything hence my self-coined term "digital pack-rat".) My OLAP folder was getting messy because when OLAP cubes are refreshed in-place and users are still accessing the cube, a second generation of the cube is created.

For illustration purposes, I have included below a sample folder structure of a simple OLAP cube created from SASHELP.SHOES. The cube was created at 11:07am, then updated in-place at 11:15am. But at 11:18am, when I ran the update in-place code again I was still viewing the cube in SAS Enterprise Guide. Therefore, since the gen0001 folder was locked for viewing, a gen0002 folder was created. This is the back-end magic for update in-place to work correctly.

You can not just delete the old generational folder (gen0000) when everyone is done using it. Per the SAS OLAP documentation on archiving and deleting prior generations a cube's new generations typically look through prior generational folder. But cleaning up this folder isn't just for saving space or reducing a pack-rat's digital footprint, each version of the cube remains completely viable and can therefore become less efficient over time.

To combine everything back together, you will need to run a full refresh of the cube during off-hours (which adds its own complexity), or run an COALESCE_AGGREGATIONS option in PROC OLAP as shown below.

PROC OLAP
   CUBE                   = "/Projects/Cubes/Shoes"
   COALESCE_AGGREGATIONS;

Included is what my new clean (and "digital pack-rat" busting) folder looks like:

More information on how to update cubes in place using SAS code is available in my prior post on OLAP refresh best practices.

Post a Comment

So many autoexecs; three tips for BI administrators

Angela Hall
Angela Hall|October 29, 2012

BASE SAS users are already familiar with the autoexec file. This is a .sas file that typically resides in the installation folder of the SAS executable. Instructions for setting it up in UNIX, Windows, and other environments is readily available on the SAS website. In SAS BI configurations there are autoexec.sas as well as autoexec_usermods.sas SAS programs scattered throughout but which one(s) should be edited? Three tips:

  1. Avoid future conflicts: SAS recommends that no matter where they are located, you should edit the "_usermods.sas" version to avoid conflicts in installing future upgrades/updates.
  2. Where do you need the information: You can preassign SAS libraries, set default options, point to macro and format locations, or create global macro variables with these autoexec files, just choose wisely where you need the information available.
  3. Precedence is important: There is also a main autoexec.sas file for the entire SAS installation at: C:\\Program Files\\SASHome\\SASFoundation\\9.3\\autoexec.sas. This is the granddaddy file that drives everything else. Of course an option here could then be overwritten by an option down the stream in another autoexec_usermods.sas file. So understand the order of precedence.

Purpose

directory

filename
Affects all servers within the application server context \\SASApp appserver_autoexec_usermods.sas
The SAS Schedule Server can execute jobs on the BatchServer. Changes to this file only affect the BatchServer jobs. \\SASApp\\BatchServer autoexec_usermods.sas
Only the Connect Server \\SASApp\\ConnectServer autoexec_usermods.sas
Only the OLAP Server \\SASApp\\OLAPServer autoexec_usermods.sas
Only the Pooled Workspace Server \\SASApp\\PooledWorkspaceServer autoexec_usermods.sas
Only the Stored Process Server \\SASApp\\StoredProcessServer autoexec_usermods.sas
Only the Workspace Server \\SASApp\\WorkspaceServer autoexec_usermods.sas
Affects all services within the SASMeta application server context (recommended for use by SAS Administrators managing SAS applications) \\SASMeta appserver_autoexec_usermods.sas
SASMeta - Batch Server only \\SASMeta\\BatchServer autoexec_usermods.sas
SASMeta - Workspace Server only \\SASMeta\\WorkspaceServer autoexec_usermods.sas
Scheduling Server \\SchedulingServer autoexec_usermods.sas
Share Server \\ShareServer autoexec_usermods.sas
Post a Comment

Verifying a SAS BI installation

Angela Hall
Angela Hall|October 22, 2012

After installing SAS Business Intelligence, I would also suggest verifying that all is working appropriately before adding users or importing content from another machine. (My template checklist is included below.) The act of creating the content is an extremely important test (to immediately rectify issues with accessing data, or saving to the SAS Content Server).

For installers who want to be super fast about this, a few items can be built in advance and run from a BASE SAS or Enterprise Guide with relatively little tweaking on any environment. PROC INFOMAPS will create an information map for your use and PROC OLAP creates OLAP cubes without going through the clicks of a SAS OLAP Cube Studio. That could then quickly set up the content to build/test the remaining items.

Sample OLAP Cube Code (v9.3)
Sample Proc INFOMAPS code

Checklist for Verification

  Interface Function
1 RDC onto Server by SAS Administrator Ability to stop and restart all SAS Services
2 SAS Management Console Import tables from an access library (Oracle, SQL, etc)
3 SAS Information Map Studio Create sample information map
4 SAS Web Report Studio Create and save sample report
5 SAS OLAP Cube Studio Create sample OLAP Cube
6 SAS Enterprise Guide Open sample OLAP Cube
7 SAS Enterprise Guide Create sample Stored Process
8 SAS Enterprise Guide Open SAS data table into project
9 SAS Portal View sample Stored Process
10 SAS Portal View sample Web Report Studio Report
11 SAS Portal Ability to change user preferences
12 SAS BI Dashboard The BI Dashboard interface opens successfully
13 SAS BI Dashboard Create indicator data from an Information Map
14 SAS Add-in to MS Office (Excel) Open sample OLAP Cube in Pivot Table
(Word) Create sample graph report off data table
(any) Execute Stored Process Sample
15 SAS DI Studio Ability to view data and execute a sample ETL process
16 SAS DI Studio Ability to schedule a task
Post a Comment

Go on a date with SAS BI Dashboard, part 2

Angela Hall
Angela Hall|October 15, 2012

Last week I provided the steps for how to go on a date, at least by using dynamic prompts in BI Dashboard. Now that you have seen how to create them, lets discuss making them smarter. For this example, the customer is asking for two prompts to affect a single chart. These prompts will have a start date and an end date. If the user selects an end date that is before the start date, the report will fail to generate results. For example, data starting in June 2012 but ending in October 2011. Or in code terms a nonsensical where clause such as:

"01jun2012"d <= date <= "01oct2011"d.

Here is an example of the BI Dashboard smart dating in action. Notice that when selecting June only October-July appear.

You too can create this to avoid possible user "errors". Included are the steps:

Step 1. Edit the stored process

In order to use the same stored process for both prompts, I will need to make some minor changes to the code.

%global sdate; Globally define the new parameter sdate for start date.
%macro dt;
data test;
format date monyy5.;
%do i=0 %to 11;
date=intnx('month', today(), -&i); 		This code is the same.
%if %length(&sdate) > 0 %then %do;
if date > input("&sdate", mmddyy10.) then output;
%end;
%else output;;		 Test for the sdate existance using %length, if it exist only output end dates greater than what was selected.
IMPORTANT TIP: Date values are passed from BI Dashboard as text and resemble mmddyy10 format.
%end; %dt
... 		Everything else is the same. Refer back to the rest of the code in Part 1 post.

Step 2. Add a prompt to the stored process metadata

This will be used in BI Dashboard to define the relationship between the indicators. The prompt is optional as the code will work with or without a selected value so I did not select the prompt option 'Requires a non-blank value'. As you see below, I named the prompt sdate to match the code &sdate above.

Step 3. Create the second (end date) indicator

Since the data source itself does not change, I do not need to edit the indicator data object. A new indicator for the end date prompt needs to be created.

Extra TIP: Dynamic prompts assume that you will be leveraging a range initially. For these prompts, I select any range I have defined but when I am editing the properties of the prompt I change the prompt type from 'Combo Box with gauge' to just a 'Combo Box'. Then all the range questions disappear.

Step 4. Define interactions between the start date and the end date indicators

In the dashboard edit screen in BI Dashboard, add both the start date and end date indicators.
Select the start date indicator and choose the 'Interactions' task off the menu bar.

Select the interaction type 'Filter data on remote server' and choose source data 'date' with target data 'sdate' - as seen below.

Post a Comment

Go on a date with SAS BI Dashboard, part 1

Angela Hall
Angela Hall|October 8, 2012

Don't give up on the dating game. Just because there is no prompt type specifically for dates, it can still be accomplished with some stored process matchmaking. BI Dashboard offers a dynamic prompt indicator which provides the ultimate flexibility in developing prompt based reports and dashboards.

The dynamic prompt indicator allows you to generate a drop down box of values dynamically from a specified indicator data element. In this post (part 1), I will explain how to create a drop down box of the last 12 months with no required monthly maintenance (adding months, running ETL scripts to populate a source table, etc). Next week, in Part 2 I will describe how to define interactions between multiple date indicators so your dates are smarter.

Step 1. Develop a stored process to create a data source for the indicator data

Included is the code to create a temporary data table (work.test) which publishes the data to a package 'DynamicDateList' for the BI Dashboard to pick up.
Note that this code dynamically creates a list of the last twelve months (including the current month) in descending order. Some customer require that a default selection is setup, reducing the amount of clicks to get the most often requested report. In BI Dashboard, the dynamic prompt displays by default the first record in the table. For this example, the code is written to create months in descending order so the current month will appear as the selection.

%macro dt;
data work.test;
format date monyy5.;
%do i=0 %to 11;
date=intnx('month', today(), -&i);
output;
%end;
run;
%mend;
%dt
%let temploc = %sysfunc(pathname(work));
data null;
length path $32767;
rc = 0;
pid = 0 ;
description = '0';
name = '';
call package_begin( pid, description, name, rc);
call insert_dataset( pid, "WORK", "test", "dynamic date list", '', rc);
call package_publish( pid, "TO_ARCHIVE", rc, "archive_path, 
            archive_name, archive_fullpath","&temploc", "DynamicDateList", path );
call symput( '_ARCHIVE_FULLPATH', path);
call package_end( pid, rc);
run;

Important Note: The stored process must be registered to generate a package result.

Step 2. Setup the indicator data

Choose 'Stored Process' as the data source and choose the stored process created in Step 1. If the published data set name ('test' in this example) doesn't appear there is an error in either the stored process or within the metadata registration. To troubleshoot, I would recommend moving to the SAS Stored Process web application (http://server:port/SASStoredProcess) to run the same stored process and review the log message there.

Step 3. Create a dynamic prompt indicator leveraging the indicator data

After saving the indicator data from Step 2, create a dynamic prompt indicator using this new indicator data. As you can see the default selection is October (Oct12), the first record in the test data table.

Check back next week on how to make the date prompts smarter in part 2!

And for another example of using a stored process to dynamically generate a data result for a dashboard indicator, check out my 2nd book The 50 Keys to Learning SAS Stored Processes.

Post a Comment

Always learning something new, two awesome Enterprise Guide tricks

Angela Hall
Angela Hall|September 17, 2012

Who says anyone is an expert at something? I definitely do not when it comes to SAS (well, anything for that matter). Each time I sit through a presentation at a conference I learn something new. During the SAS Western Users Conference, fondly know as WUSS, this month I learned two new and awesome tricks for using SAS Enterprise Guide.

Trick #1 - Jump through the log

Ok, ok, I should have known about this already given that my co-author Tricia Aanderud posted about this trick in April. But as most of you know, it is difficult to keep up with everything out there.
Note in the below example I have 13 pages of logs that I would need to jump through to see the first issue. All WARNINGS and ERRORS are accessed via the up/down arrow.

Trick #2 - Create additional process flows to organize your work

When organizing projects, multiple process flows allow you to select areas of the project to run or schedule. I've had some awfully large SAS Enterprise Guide projects in my past. Using this trick would have saved me manual time selecting individual items that I wanted to run in sequence.

Select File New Process Flow to create additional process flows


In the example below, I have 3 process flows ('Initial Review', 'Analysis', and 'Report').

Multiple process flows allow you to run select areas of project

If you don't think you can learn something new, I challenge you to attend a regional SAS User Group conference this fall. Bet you will discover some new awesome idea or trick to implement back at your workplace!

Post a Comment

Defining a custom color palette for BI Dashboard bar charts

Angela Hall
Angela Hall|September 12, 2012

Out of the box, the simple bar graph uses the same color for each bar.

A frequent request from users is to define a standard color palette for the dashboard indicators so distinct bars are the same color in each indicator or from refresh to refresh. With most dashboard tricks, like the dynamic dashboard range, it is all about the data. The steps are to create a numeric value for each bar's categorical variable (such as region), create a range that matches these numeric values and defines the color for each, and the use a range enabled bar chart to leverage these colors but still display the bar's numerical value (such as sales total).

Let's walk through this in more detail.

Step 1. Set up the data

For this example, I used a SQL query to pull in the total sales for three regions in the sashelp.shoes data set. Then with a SQL case expression, I added a 'color' column where 1 is for Africa, 2 is for Asia, and 3 is for Canada.

The important change to note is that for the data mapping tab, the color column will be changed so that the value displayed is the total sales. This means that the graph will leverage the color to plot, but the actual data value shown to the user will be the total sales.

Step 2. Define a custom color range

Create a range that defines which colors to use for which corresponding number. In the example screenshot below, I chose to have Orange for Africa (or defined as 1 in the data), Blue for Asia (stored as 2), and Red for Canada (and marked as a 3).

Step 3. Use a bar chart that leverages range

The final step required a bit of exploration, but what turned out the best was the use of the "Bar chart with bullet" indicator. I used the data and range defined in the prior steps, and then specified that the 'color' value was the range (#1 in the screenshot below), the 'region' was the x-axis value (#2), the bar value was 'color' (#3) and the bullet value is 'total_sales' (#4). You'll notice in the chart the y-axis label states 'color(millions)'. Just add the term 'Total Sales' to the y-axis title field to correct this as well.

Post a Comment

Testing recommendations for SAS BI Dashboard & SAS Web Report Studio

Angela Hall
Angela Hall|August 8, 2012

With limited time and budget, you can still thoroughly test SAS reports built in SAS BI Dashboard and SAS Web Report Studio by considering how all the tools were used to build the end report. An understanding of the functionality within each part of the chain from raw data to final report helps build out the test cases to effectively verify accuracy without clicking on every single button on the final report.

  1. Raw data to summarized data table
    Sometimes this is not considered at all, but the ETL scripts written to create the summarized data are worth at least a peer code review. Other options are to use SAS Enterprise Guide and to generate proc summary or proc freq results to compare the raw data to the summarized table. The point here is that instead of verifying that the data in each web report is correct, verify the numbers from the summary table itself are accurate.
  2. Metadata and Information Maps
    For subsequent steps in creating the data source, metadata should be confirmed (especially SAS formats for any improper truncation of values) and custom columns in SAS Information Maps should be tested. Prompts and filters built in SAS Information Map Studio can be verified at this point. To get the most value out of the time spent - the important item for the tester to understand is how the prompt or filter were built. I would recommend more testing when multiple filters are combined (expressed in Boolean logic AND/OR), and error insert testing with the simple prompts/filters (one variable used in the selection). Error inserting means that you are entering in prompted values that you know will NOT return results, to ensure that the end user either is not allowed to enter incorrect values or results are presented in a way that the end user has requested or is expecting.
  3. BI Dashboard and Web Reports
    The design of these reports is critical understanding for the tester. What content uses which information maps, what ranges are leveraged in which indicators, and where interactions exist between indicators are all required to build successful test cases. If any measures are defined in the web report itself, these should be verified as accurate - but all other data elements that are pulled straight from table/information map should already have been validated from one of the above two sections.
  4. Links between BI Dashboard and Web Reports
    With the design of individual reports, the interaction between tools needs to be verified. But note that testing the linkage from one combination of prompted values is typically enough to verify that the functionality is working as expected. If different data sources are used to create the dashboard and the web report studio prompt, it is much more efficient to verify that the data values are identical using proc freq statements than to click your way through tens or even hundreds of possible values using the user interface.

Of course, the most important thing is to do SOME testing of your reports before handing them off to your user community. The last thing anyone wants is an email from an executive questioning the accuracy or complaining that the report simply doesn't work. Loosing their trust will just makes your job harder in the future.

Do you have other recommendations do you have for testing SAS BI Dashboard and SAS Web Report Studio reports? Please share so we can all produce accurate & functional results!

Post a Comment

Use OLAP data sources from SAS Stored Processes

Angela Hall
Angela Hall|June 21, 2012

So are you now dreaming big? Coming up with other ideas for how SAS Stored Processes can be leveraged in your installation of SAS?

As I mentioned in last week's post, you could use OLAP cubes as a data source for your SAS Stored Process. Here is how I would suggest getting started.

Step 1. Open and slice the OLAP Cube in SAS Enterprise Guide

This allows you to navigate through the cube and generate the tedious MDX code for that specific view and limit all of your typing.

Step 2. Select the menu item Edit View -> Edit in MDX Viewer


Now you can view all of the MDX code associated with this view. Copy this entire code for the next step.

Step 3. Place the MDX code in a PROC SQL statement

proc sql;
connect to olap (host="machine" port=5451
    user="username"
        pass="pwd"
);

CREATE TABLE stp_datasource as 
select * from connection to olap 
(
/*********************PUT PASTED MDX CODE HERE**********************/

SELECT { 
 [Measures].[UnitsSUM] } PROPERTIES MEMBER_CAPTION, PARENT_UNIQUE_NAME
ON COLUMNS, { 
 [DateDimension].[Fiscal_Month_Num].Members } 
                         PROPERTIES MEMBER_CAPTION, PARENT_UNIQUE_NAME
ON ROWS
FROM [SalesSummary]
WHERE (
         [ProductDimension].[All ProductDimension].[Wholesale].[Carob N Almonds])

/************STOP THE PASTING OF MDX CODE BY THIS POINT****************/
);
quit;

Step 4. Build cascading filters for the where clause

I created a cascading filter, one for TYPE and one for PRODUCT and replaced the where clause with the following code:

WHERE (
        [ProductDimension].[All ProductDimension].[&type].[&product])

Step 5. Register and test the SAS Stored Process

Whalaa! You have successfully queried an OLAP cube for use in your stored process! Now that you have the basic steps, what dreaming are you doing? How will you use OLAP cube data for stored processes?

Post a Comment