The integration of BI solutions within business process applications or interfaces has become a modern standard. Over the past two decades, Business Intelligence has dramatically transformed how data could be used to drive business and how business processes can be optimized and automated by data. With ML and augmented analytics movement, BI applications are vital to every organization. Analytics embedding enables capabilities such as interactive dashboards, reporting, predictive analytics, AI processing and more within the touch of existing business applications. This differs from traditional standalone BI applications that put all the capabilities of business intelligence directly within the applications on which users have already relied. Now you may ask, when should I consider embedding to maximize my ROI?

Embedding Use Cases

Bar graph with upward trend     Business Process Applications

In this case, the integration of data & analytics is embedded into applications used by specific personas. For instance, embedding historical client information into a CSR application. One outcome will be improved decision-making based on readily available customer insights and higher levels of user adoption.

Shopping cart    Software / OEM Solutions

Digital transformation is all about software. Data visualization, forecasting and user interactions are must-have features of every application. Save the time you would spend coding. Embedding analytics in software not only saves cost greatly but also prominently enhances functionalities of software application.

Forest scene     Portals / Websites

Integration of data into your website or portal is another popular option. The benefits are obvious – information sharing provides your customers with valuable insights through a unified platform; you are able to go to market much faster since you are reaching customers directly. It helps your customers access the data they need to make decisions better, quicker and within their fingertips.

Embedding flow for embedding for your customers

Prepare for Embedding

Ready to get started? Let’s take a look at things to be considered. At a high level, the following areas to be carefully examined before design begins:

  • What are the embedding integration options? Especially with regards to security, how do you enable other application access to your secured BI assets? What are the options to manage authentication and authorization for thousands of users, both internally and externally?
  • Which functionalities will be open and accessible to BI embedding specifically? Typically not all UI functionalities are supported via embedding. Verify that critical functionalities are supported. Map your requirements to embedding functionalities and features.
  • Cloud vs On-premise hosting. Besides management and cost concerns, your organization may have cloud strategies and road-maps in place already. If that is the case, most likely no exception for BI application including embedding. Plus source data cloud modernization is another big driver to go with cloud. 
  • Cost – yes, no surprise there is cost associated with BI embedding. Each BI vendor may collect fees differently but legitimately you will need to pay BI embedding based on consumption pattern even when a single application user account is leveraged. Do the math so you know how much it will be on the bill. 

 Next let’s examine the tool differences. 

Embedding API by Leading BI Vendors

VendorAPIFunctionalities
IBM CognosSDK – Java, .NetMashup Service (Restful)New JavaScript API for DashboardNew REST API   Full programming SDK is almost identical to UI functionalitiesSDK can execute or modify a reportMashup service is easy to web embedding, limited report output formats are supportedJavaScript API and extension for dashboard, display/editNew REST API for administration 
Power BIREST APIJavaScriptREST: Administration tasks, though clone, delete, update reports are supported tooJavaScript: provides bidirectional communication between reports and your application. Most embedding operations such as dynamic filtering, page navigation, show/hide objects 
TableauREST APIJavaScriptREST: manage and change Tableau Server resources programmaticallyJavaScript: provides bidirectional communication between reports and your application. Most embedding operations such as dynamic filtering, page navigation
AWS QuickSightSDK – Java, .Net, Python, C++, GO, PHP, Ruby, Command lineJavaScriptSDK  to run on server side to generate authorization code attached with dashboard urlJavaScript: parameters (dynamic filters), size, navigation

BI embedding opens another door to continue serving and expanding your business. It empowers business users to access data and execute perceptive analysis within the application they are familiar with. Major BI vendors have provided rich and easy to use API, the development effort is minimum, light and manageable while the return benefits are enormous. Have you decided to implement BI Embedding yet? Please feel free to contact Ironside’s seasoned BI embedding experts to ask any questions you may have. We build unique solutions to fit distinctive requests, so no two projects are the same, but our approach is always the same and we are here to help.

For users of IBM Cognos Analytics a popular request is to highlight alternating rows in a report. This article will demonstrate how to achieve this result using the list container and the property Conditional Styles.

In order to create the report, the following objects will be used:

Object Definition
List containerContains report data
Query CalculationsCreate calculations
Running-count() functionGet sequential numbers
Mod() functionGet remainders of 0 or 1
List columns body style ancestorAncestor object used to highlight the data
Conditional stylesCreate type of style

Finished Product

When the demo is finished your report will look similar to the following:

Let’s get started.  The first thing we’ll do is create a simple list report using the sample GO Sales (query) package included with the application.  However, you can use any data to follow along.  

Create the query calculation

  1. Select the blank template and create a report using the List container. 
  2. From the Sales(query) namespace add the Product number, Product and Revenue data items to the list.
  1. View the report in Page Preview.
  1. From the Toolbox, add a Query Calculation to the end of the list container next to Revenue.  The Data item expression dialog will open. Name the calculation Count
  2. Click the Functions tab at the bottom of the screen.   
  1. Open the Summaries folder and double click the running-count() function to add it to the expression.  This function returns the running count by row for a set of values.
  1. Click the second tab on the dialog box, Data Items , and drag the Product Number to the function.  Add the ending parenthesis.

Running-count(Product number)

  1. Click OK to view the results.

The result of the running-count() function will show sequential numbers on each row.

Create Values to Highlight

The mod() function returns the remainder of two numbers.  In our case it will generate the numbers 0 or 1 after the division takes place.  

We will nest the mod() function around the running-count() function to achieve this result.

  1. Double-click the Count column title to return to the Data item expression.
  2. Modify the expression as follows.

mod(running-count([Product number),2)

  1. Click OK when done to view the report.  Note the position of the parentheses and the divisor is 2.  Even numbers will have a remainder of 0 and odd 1.

Note:  the mod()function is available for selection, however, we are typing the expression to ensure the correct syntax is applied.

The results of the calculation show 0 and 1 on alternating rows. 

 Apply Conditional Formatting

We will now highlight the rows in the list object using the List Columns Body Style ancestor. A row with the value of 1 will be colored.

  1. Click any column in the List container and at the top of the Properties pane or on the flyout menu select the ancestor object and click List Columns Body Style.  When selected the area will highlight in the list.

2.  From the Properties pane double click the Conditional Styles property.  The Conditional Styles dialog box will appear.  Click the plus sign (+) and select New Conditional style > Count. Click OK.

  1. Click the plus sign (+) to add a threshold value of 0 and click OK.  In the Style column click the first edit button (pencil) and select a Background color. Click OK.  Note the arrow next to the 0 is pointing upward indicating the color will generate for values greater than 0. Click OK to exit all dialog boxes.
  1. View the report.

5.  Since the Count column is not needed in the report remove it using the Cut button (scissors). Do not delete the column as it is used in the calculation used to render the conditional formatting and must remain in the underlying query.

As you can see, alternating row colors in a list report is simple in IBM Cognos Analytics!

Happy reporting! 

For the original version of this blog post using IBM Cognos Workspace Advanced, click here.

Determinants can play a crucial role in the overall performance and consistency of your Framework Manager model but remain one of the most confusing aspects of the application to most developers. This article will attempt to end the confusion.

Determinants are used so that a table of one grain (level of detail) behaves as if it were another actually stored at another grain. They are primarily used for dimension tables where fact tables join to dimension tables at more than one level in the dimension. (There are other cases where you could use them, but they are less common and fairly specific situations.)

The Situation

Let’s use the example of a date dimension table with day level grain. If all the fact tables join at the day level, the most detailed level, then you do not need determinants.  But as many of us know from experience, this is not always the case. Fact table are often aggregated or stored at different levels of granularity from a number of reasons.

The Problem

The trouble arises when you wish to join to the dimension table at a level that is not the lowest level. Consider a monthly forecast fact table which is at the month level of detail (1 row per month). A join to the month_id (e.g. 2009-12) would return 28 to 31 records (depending on the month) from the date dimension, and throw off the calculations. Determinants solve this problem.

The SQL

Often when modeling, it’s useful to think about the SQL code you would like to generate. Without determinants, the incorrect SQL code would look something like this.

SELECT
F.FORCAST_VALUE,
D.MONTH_ID,
D.MONTH_NAME
FROM SALES_FORECAST F INNER JOIN DATE_DIM D ON
F.MONTH_ID = D.MONTH_ID

This code will retrieve up to 31 records for each of the sales forecast records. Applying mathematical functions, for example Sum and Count, would produce an incorrect result. What you would like to generate is something along the following lines, which creates a single row per month, AND THEN join to the fact table.

SELECT
F.FORCAST_VALUE,
D1.MONTH_ID,
D1.MONTH_NAME
FROM SALES_FORECAST F INNER JOIN
( SELECT DISTINCT
D.MONTH_ID,
D.MONTH_NAME
FROM DATE_DIM D ) AS D1
ON F.MONTH_ID = D1.MONTH_ID

As shown  above, the trick is to understand which columns in the dimension table are related to the month_id, and therefore are unique along with the key value.  This is exactly what determinants do for you.

Unraveling the Mystery in Framework Manager

Following Cognos best practices, determinants should be specified at the layer in the model in which the joins are specified.

Here we see a date dimension with 4 levels in the dimension, Year, Quarter, Month and day level.

This means we can have up to 4 determinants defined in the query subject depending on the granularity of the fact tables present in your model.  The first three levels, Year, Quarter, Month, should be set to “group-by” as they do not define a unique row within the table and Framework Manager needs to be made aware that the values will need to be “Grouped” to this level. In other words, the SQL needs to “group by” a column or columns in order to uniquely identify a row for that level of detail (such as Month or Year).  The Day level (often called the leaf level) should be set to “Uniquely Identified”, as it does uniquely identify any row within the dimensional table. While there can be several levels of “group by” determinants, there is typically only one uniquely identified determinant, identified by the unique key of the table. The “uniquely identified” determinant by definition contains all the non-key columns as attributes, and is automatically set at table import time, if it can be determined.

The Key section identifies the column or columns which uniquely identify a level.  Ideally, this is one column, but in some cases may actually need to include more than one column.  For example, if your Year and Month values (1-12) are in separate columns.  In short, the key is whatever columns are necessary to uniquely identify that level.

Using our aforementioned table, the setup would look like this:

The Attributes section identifies all the other columns which are distinct at that level.  For example, at a month_id  (e.g. 2009-12) level , columns such as month name, month starting date, number of days in a month are all distinct at that level. And obviously items from a lower level, such as date or day-of-week, are not included at that level.

Technically, the order of the determinants does not imply levels in the dimension. However, columns used in a query are matched from the top down which can be very important to understanding the SQL that will be generated for your report. If your report uses Year, Quarter and Month, the query will group by the columns making up the Year-key, Quarter-key and Month-key. But if the report uses just Year and Month (and not the Quarter) then the group by will omit the Quarter-key.

How Many Levels Are Needed?

Do we need all 4 levels of determinants? Keep in mind that determinants are used to join to dimensions at levels higher than the leaf level of the dimension. In this case, we’re joining at the month level (via month_id). Unless there are additional joins at the year or quarter level, we do not strictly need to specify those determinants. Remember that year and quarter are uniquely defined by the month_id as well, and so should be included as attributes related to the month, as shown.

The Result

Following these simple steps the following SQL will be generated for your report. The highlighted section is generated by the determinant settings. Notice how it groups by the Month_ID, and uses the min function to guarantee uniqueness at that level.  (No, it doesn’t trust you enough to simply do a SELECT DISTINCT.)  The second level of group by is the normal report aggregation by report row.  So the result is that the join is done correctly, which each monthly fact record joined to 1 dimensional record at the appropriate level, to produce the correct values in the report.

This blog is an updated version of a previously released post, which can be found here.

Editor’s Note:  This blog was originally posted on July 25, 2011, we recently reviewed and updated on October 5, 2020. 

Over past major releases, Cognos has provided a couple predefined JavaScript library to interactive with report objects especially prompt objects. We find that traditional web standard JavaScript is still very useful due to the effort of migrating to IBM specific JavaScript library and flexibility to develop code at report level. Going forward we would encourage you to consider using the new custom controls provided by IBM Cognos Analtyics family product for new JavaScript application development. The new custom controls support the full interactive mode, provide much better end user experiences and support HTML5 standard.  Some commonly seeded functionalities such as dynamic column sorting, filters are now built-in via full interactive viewer, and JavaScript is no longer needed.  Read more here.
Read more

Editor’s Note:  This blog was originally posted on September 27, 2011, we recently reviewed and updated on October 5, 2020. 

Over past major releases, Cognos has provided a couple predefined JavaScript library to interactive with report objects especially prompt objects. We find that traditional web standard JavaScript is still very useful due to the effort of migrating to IBM specific JavaScript library and flexibility to develop code at report level. Going forward we would encourage you to consider using the new custom controls provided by IBM Cognos Analtyics family product for new JavaScript application development. The new custom controls support the full interactive mode, provide much better end user experiences and support HTML5 standard. Some commonly seeded functionalities such as dynamic column sorting, filters are now built-in via full interactive viewer, and JavaScript is no longer needed. Read more here.

Note: 

  • All information and examples are specifically designed for IBM Cognos Analytics Reporting tool based reports. It is assumed that the reader is familiar with Cognos Report Studio. 

  • For more information about JavaScript and the HTML language basics please go to http://www.w3schools.com and other available web based tutorials. 

  • Code development environment: IBM Cognos Analytics 11.1.x

5 – Reaching a Prompt Element

Obviously, in order to use JavaScript to modify or manipulate an element on a web page, you first need to reach or find the element you want to edit. As I mentioned in section 3 in the previous article, Cognos prompt pages and report pages are dynamically generated web pages. Prompts placed in Cognos reports are designed to take user inputs and submit requests to a web server. Like many other web pages a <Form> element is embedded on the page in order to accept user’s input. As you can imagine, all prompts elements are tied to this intake form. This allows us to easily access any prompt element once we find the form.

Here is the typical call you can use in JavaScript to find the “invisible” form in a Cognos report:

var fW = getFormWarpRequest(); 

Depending on the type of the prompt you are dealing with, you can get the prompt object using the following name convention:

Text Edit Box

fw._textEditBox<prompt name>

List Box

fw._oLstChoices<prompt name>

Drop Down List

fw._oLstChoices<prompt name>

Radio Button Group

fw._oLstChoices<prompt name>

Check Box Group

fw._oLstChoices<prompt name>

Date Edit Box

fw.txtDate<prompt name>

Let’s apply this logic to the sample report developed in the Part 1 of the previous article.

Steps:

  • Open the previously saved sample report “mylist” in Reporting Tool. If you haven’t created it yet or lost it check IBM Cognos and JavaScript- Part I, Section 3 to create it. 
  • Go to Prompt Pages -> Prompt Page 1. 
  • Click on drop down prompt P1. On the properties panel under Miscellaneous->Name, give this prompt object a name such as “mydropdown”.
  • Double click on the HTML item, added in the previous steps to create the report, next to report title. Change the JavaScript code to: 

<script LANGUAGE=”JavaScript”> 

<!– 

// call Cognos built-in function to get the form element 

var fw = getFormWarpRequest(); 

//get the drop down element 

var dropdown=fw._oLstChoicesmydropdown; 

//next will loop through all the options of the drodown lis 

for (var i=0; i <dropdown.options.length; i++) 

if (i >1) //skip the first two default options Cognos generated 

alert (dropdown.options.text) ; //call alert function to display the text value of the option 

//–> 

</script> 

  • Move the HTML Item next to drop down prompt. Your report should look like below: 
  • Save the report. 
  • Run it from Reporting tool. You will see popup windows shown 5 times. Each one displays the text value of each option of the dropdown box. 

6 – Reaching a Report Data Element

An IBM Cognos report page is really just a web page that dynamically displays report content based on data query results. The most frequently used data containers are lists and crosstabs. How do I access a list/crosstab then? Is that even possible? Fortunately the answer is yes. In section 3 of the last article, we examined the report page structure. A list or crosstab on report page is just a table element, styled and formatted according to Cognos global style sheets.

Steps:

  • Open the previous saved sample report “mylist”  
  • Go to Report Pages -> Report Page 1  
  • Double click on the HTML Item, replace the JavaScript code with the following: 

<script language=”JavaScript” type=”text/javascript”> 

//<!– 

var tbls = document.getElementsByTagName(“TABLE”)    // first get all the table objects on the page 

var tblCnt = tbls.length;        // determine the number of tables 

for(var t=0; t<tblCnt; t++) //loop through each table object 

var tbl = tbls.item(t);            // define the current table 

if (tbl.className == “ls”) //this is our list table 

var cell= tbl.rows(0).cells(0); // reach the first cell of the first row of the list table 

alert( cell.firstChild.innerHTML); //display the first cell content/text 

//–> 

</script> 

  • Move the HTML Item after the list. Your report should look like below:
    • Save the report. 
    • Run it from Reporting tool. You will see a popup window that displays the first column header text “Product line”: 

    In the next couple steps, we are going to really start to enhance the abilities of the tool and modify an element’s attributes on a click event.

    A HTML element may have many attributes:

    • Attributes provide additional information about an element.  
    • Attributes are always specified in the start tag.   
    • Attributes come in name/value pairs like: name=”myname”, value=”code1″   
    • Attribute values should always be enclosed in quotes. Double style quotes are the most common, but single style quotes are also allowed. 

    Below is a list of some attributes that are standard for most HTML elements:

    Event attributes vary for each element, here are some common ones:

    7 – Edit a Prompt Element

    In section 5, we learned how to reach a prompt element using JavaScript. Once you get an object you want to control, it simply becomes a task of modifying the attributes that you wish to alter.

    Let’s take a look at how JavaScript can remove the default style of the dropdown list prompt and add in a custom background color. When the user makes a selection from drop down list JavaScript will check which option the user selected. If Camping Equipment is selected a pop up alert will tell the user which years of data will be included in report.

    Steps:

    • Open the previous saved sample report “mylist”  
    • Go to Prompt Pages -> Prompt Page 1   
    • Double click on HTML Item and update the JavaScript to following: 

    <script LANGUAGE=”JavaScript”> 

    <!– 

    //call cognos built-in function to get the form 

    var fw = getFormWarpRequest(); 

    // reach the dropdown list 

    var dropdown=fw._oLstChoicesmydropdown; 

    //clear the class attribute of the dropdown list 

    dropdown.className=”; 

    dropdown.style.backgroundColor=”#CEECF5″; //set back ground color 

    dropdown.onchange=check; //onchange event call check function 

    function check( ) 

    var idx=dropdown.options.selectedIndex; //check which option is selected 

    if ( dropdown.options.value==”991″ ) //if camping equipment option is selected 

    alert (“Camping Data only includes Year 2009 to Current data”) ; 

    //–> 

    </script> 

    • Save the report. 
    • Run it from Reporting tool. You will notice that the background color of the drop down box is set to light blue, and a message will be popped up only when Camping Equipment selection is selected: 

    8 – Editing a IBM Cognos Report Data Element

    Report data elements such as lists, crosstabs, and charts are different elements on a report page (a web page). Usually they are not tied to a web form as they are results based on user requests. That being said, it may require additional user interface items to be added to the report so that user requests can be processed. In the next demo we will use JavaScript to dynamically show or hide list object data based on a user selection. Custom buttons are added to facilitate the show/hide requests.

    Steps:

    • Open previous saved sample report “mylist”  
    • Go to Report Pages -> Report Page 1.  
    • Drag a chart object from the Insertable Objects pane.  
    • Set the chart to use the same data query that the list uses.   
    • Drag the Revenue data item as Default Measure of the chart.  
    • Drag Product line data item into the Series area.  
    • Save and run the report. You should have both the list and chart shown on the report.
    • Drag a new HTML Item before the list table, and type in the following : 

       <input type=”button” value=”Show Data Table” onclick=”showhide()” id=”showbutton” style=”display: “> 

    <input type=”button” value=”Hide Data Table” onclick=”showhide()” id=”hidebutton” style=”display: none”> 

    • Double click on the other HTML Item added in the previous step and update the JavaScript to the following: 

    <script language=”JavaScript” type=”text/javascript”> 

    //<!– 

    var showbt=document.getElementById(“showbutton”); // get show button element 

    var hidebt=document.getElementById(“hidebutton”); //get hide button element 

    var tbls = document.getElementsByTagName(“TABLE”)    // get all the tables on the page 

    var mylist; 

    var tblCnt = tbls.length;        // determine the number of tables 

    for(var t=0; t<tblCnt; t++) 

    {                // check each table 

    var tbl = tbls.item(t);            // define the current table 

    if (tbl.className == “ls”) // this is the list table 

    mylist=tbls; 

    mylist.style.display=”none”; //set it to be hidden 

    //dynamically show or hide list table, chart 

    function showhide() 

    if ( mylist.style.display==”none”) //if list is currently hidden 

    mylist.style.display=””; //set list to be shown 

    showbt.style.display=”none”; // hide “Show” button 

    hidebt.style.display=””; //show “Hide” button 

    Else //else if list is currently shown 

    mylist.style.display=”none”; //set list to be hidden 

    showbt.style.display=””; // make “Show” button displayed 

    hidebt.style.display=”none”; // make “Hide” button to be hidden 

    //–> 

    </script> 

    Your report should resemble the image below:

    • Save and run the report again. By default the list data table is hidden.
       
    • Click on the Show Data Table button, the list table will immediately be displayed. One of the benefits of using JavaScript here is that the report doesn’t have to re-run to dynamically perform the show or hide object action. Both list and chart are generated at report initial run. JavaScript simply shows or hides the list and chart at runtime. 

    Summary

    Congratulations! Over the past few hours you have learned some fundamental JavaScript coding practices in Reporting tool. As you can see, it is not nearly as difficult as you may have feared. The business value of using JavaScript can plainly be seen in the above samples and is really limited to the user’s imagination. With a few lines of JavaScript code in Report, you can enhance the report’s user interface, usability, performance, dynamic interaction capabilities and many other options.

    While JavaScript is an extremely useful tool, it is not recommended that IBM Cognos developers use JavaScript too heavily. We recommend that you think carefully before you actually start to develop and embed JavaScript in your reports. The primary concern is around the maintenance, upgrade and future sustainability. IBM Cognos web page structures and APIs have been known to change over the course of product releases, and you may have to upgrade JavaScript code after Cognos upgrades. For advice and best practices please feel free to contact us.

    Gathering data is an essential step before performing analysis in Power BI Desktop. The tool allows users to connect with many different data sources, such as traditional or cloud databases, text files, big data, and live streams. Among these sources are SQL Server Analysis Services (SSAS) Tabular models, which are widely leveraged for enterprise solutions. Why? Let’s dive into Tabular Modeling to learn more.

    Read more

    In the first installment of this series, we discussed the origins of Mode 2 Analytics, and in the second installment we focused on how to enable this capability in your organization using Cognos. Now that we’ve learned all about how Mode 2 works, let’s walk through a sample use case that highlights the Bimodal Analytics Lifecycle as well as the technical capabilities of Cognos Analytics and how they fit together.

    In this example, you are the manager of a Healthcare Call Center system that is comprised of seven (7) regional centers across the country. Each call center handles contact (phone calls and online chats) from the customers that are located within the states that make up those regions.

    Read more

    What is the most common, most vital need of any business? Speed.

    Speed to adapt, to respond, to evolve. It is important, not just at the big picture level, but on a daily basis as well. If you’re constantly waiting for information, then you’re spending less time analyzing data and making decisions that help the organization. Speed has been vitally important in the acceptance of Mode 2 style content such as Dashboards and Stories. Response time is key when viewing daily high-level metrics or when creating that “single use” asset to analyze a potential issue.

    Read more

    In previous releases of Cognos Analytics, we have seen a trend of integrating many of the features of metadata modeling in Framework Manager into the Cognos Analytics interface. This trend is continuing with new or improved modeling capabilities being incorporated into Cognos Analytics 11.1 Data Modules.

    Read more

    With the maturing and ever increasing acceptance of the cloud across multiple industries and the data gravity gradually moving to the cloud, i.e. more data being generated in the cloud, we are seeing some interesting cloud-based data and analytics platforms offering unique capabilities. Some of these platforms could be disruptive to the established market leaders with their innovative thinking and ground up design that is “born in the cloud and for the cloud.”

    Read more