Analysis building - more details

Now we have introduced the rudimentary basics of creating an analysis, let's look at the options when building effective analyses in more detail.

First, we need to create a new analysis to work on, in the same way we did in the preceding section:

  1. Click on New.
  2. Click on Analysis.
  3. Pick the Subject Areas (choose Sample Sales Lite again).

    The page you are presented with has the following sections (marked here with green squares):

    Analysis building - more details

  • Menu Bar: This is where we can navigate to other content, or create new content.
  • Subject Areas: This is where we can explore and choose from the attributes and Measures that we previously set up in the .rpd file. Normally, you would use a single Subject Area, but you can add other Subject Areas by clicking on the little box icon. Subject Areas will consist of Measures that are on one or more Fact tables, and attributes, that come from one or more dimensions. The Subject Area was defined in the .rpd file.
  • Columns: Once we have chosen objects from the Subject Areas, then these will appear in this pane and form the basis of our analysis. Columns can be dragged into this panel, or if you double-click they will appear. Dragging gives you greater control on where they end up.
  • Filters: This is where we can add and amend any Filters which are applied to the result set produced by our Selected Columns. Filters are created either by clicking the Filter icon on a selected column, or by clicking on the Filter icon on the Filters header bar. Filters can be defined using the Filter dialog box, or they can be handcrafted SQL.

    Filters can be saved for use in other analyses. This means that several analyses can use the same shared Filter which saves time if you need to make a change to the filtering.

  • Catalog: Here we can access any previously created and already saved items; for example, Filters or calculations that we may want to use in our current analysis.

Let's explore the options available on each column:

  1. Select the column Brand from the Products folder in the Subject Areas panel by double-clicking it. It will appear in the Columns panel.
  2. Now click on the little cog wheel and you will see the menu shown below:

    Analysis building - more details

    • Sort: Does what it says on the tin! Note that you can add sorts to an analysis that already contains a sort column. You can remove individual sorts or remove them all in one go.
  3. Click on Sort Descending.
    • Edit formula: Selecting this will open a dialog box with a large number of options, too many to cover in this book, but let's look at the most commonly used features.
  4. Click on Edit formula:

    You are presented with the following dialog box that has the available columns on the left panel, the main formula box on the right, and some quick link buttons at the bottom:

    Analysis building - more details

    Note

    Note the Bins tab at the top. Bins help us to group items together, and are a quick way of creating a case statement.

    For now, we will enter a formula directly into the box:

  5. Enter "Products"."Brand" into the preceding formula box.
  6. Click OK.
  7. Click on the Results tab to see the brands in capitals.

    Note

    When you have some free time, take a look at all the available functions by clicking on the f(...) button.

    • Column Properties: It opens another dialog which provides many options on how the column is formatted. You may notice that the title for the Brand column is now, Brand, so let's change the title to something more friendly:
  8. Click on Column Properties.
  9. Click on the tab heading Column Format.
  10. Check the box Custom Headings - This unlocks the preceding box.
  11. Replace the words with Brand:

    Analysis building - more details

    Note that Suppress is selected, which means that if two rows have the same value, they are merged into one box.

  12. Click OK.

    To explore the other Column Properties options, we will add some more columns into our analysis:

  13. Double-click on the Calendar Date column twice to add it to our analysis.
  14. Double-click on the Revenue column.
  15. Check the results by clicking on the Results tab.
  16. Click on the Criteria tab.

    Column Style

  17. Click on the Column Properties link on the first Calendar Date field.
  18. Change the Horizontal Alignment to Right.
  19. Font Color to blue.
  20. Font Style to Italic.
  21. Cell Background Color to grey:

    Analysis building - more details

    Data Format

    Now we will change the way the information in the Date column is presented:

  22. Click on the Data Format tab.
  23. Tick the Override Default Data Format box (which makes the option available to edit).
  24. Select a format:

    Analysis building - more details

  25. Click OK.
  26. On the second Calendar Date column, set the Date Format option as Custom and enter dddd into the Custom Date Format box:

    Analysis building - more details

  27. Click OK.

    Note

    After each setting change, you can check the results in the Results tab.

    Conditional Format

    Next, we will explore how to change the format of the data presented, based upon a condition. For example, make Revenue numbers red if they are below 1,000 and green if they are above 5,000:

  28. Select Column Properties for the Revenue column.
  29. Select the Conditional Format tab.
  30. Click on Add Condition, and select Revenue.
  31. Set the parameter, less than 1000:

    Analysis building - more details

  32. Click OK.
  33. Now set the format we would like. Choose yellow bold font on a red background.
  34. Click OK.
  35. Add another condition.
  36. Select Revenue.
  37. Greater than 5000.
  38. Set the format to a green background.
  39. Click OK.

    Let's see all those formatting changes now:

  40. Click on the Results tab:

    Analysis building - more details

    Note

    Conditional Formats can also present images instead of, or as well as, the data in the column. See the Select Image button.

    Interaction

    The next column property to explore is the Interaction tab. This enables users to interact with the data they see, perhaps to see more details or view a web page. We will use an example link to a page on a Dashboard:

  41. Click on Column Properties of the Revenue column.
  42. Select the Interaction tab.
  43. Change the value Primary Interaction to Action Links.
  44. Click on the plus sign.
  45. Enter a name in the Action Name box.
  46. Click on the little plus sign (the left-hand running man):

    Analysis building - more details

  47. Select the first option, Navigate to BI Content.
  48. Select the Sales Summary page on the Quick Start Dashboard (located in folder Shared FoldersSample LiteDashboardsQuick StartSales Summary).
  49. Click OK, Click OK, Click OK.

    At this stage, we need to save our work, so let's save it as Analysis Two in the Book folder:

  50. Click Save As.
  51. Navigate to the Book folder.
  52. Enter the Name Analysis Two.
  53. Click OK.

    Now you can test the link by running the report (click on the Results tab) and click on one of the Revenue numbers. It will now load the Sales Summary page.

    Reopen our Analysis Two by clicking on the breadcrumb link in the bottom left-hand corner.

    Note

    Other ways to find your Analysis Two include using the Open link on the menu bar; it will show your recent edits. You can also click on the Catalog and navigate to the Book folder. Another method is to click on the Home link and you will see Analysis Two in the recent edit section.

    Continuing with the Column options available...

    Filters are used to pre-select data. You can use them to limit your results sets, and you can use them to allow users to limit results on a Dashboard. Let's see them in action:

  54. Click the Filter option on the Brand column.

    This opens the Filter dialog box.

    You can now click on the Value box and it will display available values:

  55. Select BIZTECH.
  56. Click OK:

    Analysis building - more details

    When you run the results now, only Biztech brands will be listed.

    Filters can also be applied to columns that are not displayed in your analysis. In the bottom panel, you can see a Filter button (looks like a funnel).

  57. Click on the Filter button.
  58. Click on More Columns....

    It will open the Select Column dialog:

  59. Select the column you would to Filter. We will choose Per Name Year from the Time folder.
  60. Change the Operator to is greater than.
  61. Enter 2008 in the Value box.
  62. Click OK.

    To create a SQL Filter, you tick the box Convert to SQL and press OK. This will display a SQL statement that represents the Filter you have created.

    We will add some more columns to our analysis now. Find the column in the left-hand panel and then double-click it or drag to the right-hand panel:

  63. Add the column Per Name Year between the Brand and Calendar Date columns.
  64. Add the column # Of Orders from the Calculated Facts folder.

    We now have an analysis ready to go:

    Analysis building - more details

  65. Save your analysis again.

    After you have saved your work, let's see what it looks like:

  66. Click on Results.

Views

You can now see a title and a table with our columns in. In the bottom-left panel (called Views) we can see the title and table object. Each analysis can have multiple views, including multiples of the same type.

As you build different views of your data, they are listed in the Views panel. You can add the same type more than once, for example, two Pivot Tables could be created, one with Years in columns, the other with Quarters.

Note

After you create a view, you can drag it onto the right panel, wherever you like. You can move views around and you can format the view borders, alignment, background, and so on.

Views each have their own container which have layout and style properties. Looking at Analysis One, we can see that the default horizontal alignment for a table container is to the center.

Let's examine some of the most commonly-used views.

Tables

By default, you get one table.

The default properties for a table are that the column headings are fixed, and there is a scroll bar on the right side for viewing results further down the dataset. If we had lots of columns in our analysis, then only the first few would be visible, but there will be another scroll bar at the bottom to view more columns. There are alternative settings that we can set.

We will now edit the properties of the table:

  1. Click on the Edit Table Properties icon (small pencil at the top-right of the view):

    Tables

    This will open the Edit Table View page:

    Tables

    On this page, we can change what columns are presented, in what order, and where in the view. Note the Table Prompts, Sections, and Excluded boxes. In the preceding image, we are only showing the Layout and Selection steps. You can show or hide parts of the editor using the icons on the menu bar:

    Tables

  2. Click on the Properties icon.
  3. Change the Data Viewing option to Content Paging.
  4. Enter 5 into the Rows per Page box.
  5. Check the box for Row Styling (the default is green and white rows).
  6. Change the Column headings to As separate rows:

    Tables

  7. Click OK.

    We will now move some columns around to see the effect:

  8. Move (by dragging) the Per Name Year column to the Table Prompts box.
  9. Move the Day column to the Excluded box.

    Now add a total row at the bottom of the table:

  10. Click on the sum sign next to Columns and Measures:

    Tables

  11. Click OK.
  12. Click Save.

    Let's see the results:

  13. Click on the Results tab:

    Tables

Try changing the Year Prompt and you will see the results changing.

Graphs

Click on the small Graph button on the top bar.

This displays all the possible ways of viewing the data we can retrieve in our analysis. In the following image we show the different types of Bar Graphs that are available:

Graphs

Like tables, Graphs have Prompts, Sections, and Exclude boxes:

  1. Select the Vertical Bar Graph.
  2. Take some time to explore Bar Graphs. Experiment with dragging the columns to the various boxes.
  3. Change the Graph type to Line.
  4. Move the Year column to the Sections box.
  5. Move the Date column to the Horizontal Axis.
  6. Click on the Properties box (the small xyz icon) and change the scale to 900 width:

    Graphs

  7. Click on the Titles and Labels tab.
  8. Change the Horizontal Axis Labels (click on the icon next to the option).
  9. Set the Axis Labels option to Hide.
  10. Click OK, Click OK.
  11. Click Done.

    Your analysis now has three views on the page, a title, a table, and a Line Graph.

  12. Save your analysis.

Pivot Tables

Pivot Tables are used to move values into columns, just like in Excel.

Here we show a pivot table with Brand in rows, and Year in columns. Pivot Tables also have Prompts, Sections, and Excluded boxes:

Pivot Tables

The preceding is created in a new analysis:

  1. Click New.
  2. Choose Analysis.
  3. Select the Sample Sales Lite Subject Area.
  4. Add the columns, Brand, Per Name Year, and Revenue.
  5. Click on the Results tab.
  6. Click on the Graph icon and select Pivot Table.
  7. Move the Year column to the Columns section:

    Pivot Tables

  8. Click Done.
  9. Now remove the table from the page by clicking on the little x in the top right corner.
  10. Save your analysis as Analysis Three.

Note

Removing a view from your page does not delete it; it can be used later but is not shown on this page.

Narratives

Narrative views are very useful for when you want to present your data in a precise manner, usually (but not always) using HTML and CSS. We can mix words, images, and column data to produce impressive output.

A simple narrative example:

  1. Open Analysis Three and go to the Results tab.
  2. Click on the Add View icon and select Narrative - you will find it in the Other Views list.
  3. In the Prefix box enter <font size=3>[u]Summary[/u]</font>[br/][br/].
  4. In the Narrative box enter [b]@1[/b] Sales in [b]@2[/b] were $@3[br/][br/].
  5. Enter some text into the Postfix box.
  6. Click on Done.

    Note

    The @ sign indicates a column in your analysis. @1 is the first column @2 second and so on.

    Narratives

As you can see, Narrative views can provide flexibility in how one presents data.

Note

Narratives can be used to create statement-like output. We often use them to create forms that have boxes for signature blocks.

Performance Tiles

Performance Tiles present a single number in a box. This can be a very effective way to get an import measure presented to users. Any measure that is within an analysis can be used in a tile. We will create a new example analysis to explore performance tiles and also introduce the Filter By function:

  1. Create a new analysis using the Sample Sales Lite Subject Area.
  2. Add the Revenue column four times.
  3. Add a Filter for Per Year Name = 2010 (note we do not need the Year column in the analysis.
  4. Edit the formula for the second Revenue column.
  5. Click on the Filter... button at the bottom of the box.
  6. Find Brand in the left pane and double-click it.
  7. Select BizTech in the criteria.
  8. Deselect the Filter by Brand Key option.

    You should now have a screen like shown in the following screenshot:

    Performance Tiles

  9. Click OK.

    The formula is set so that it will sum the Revenue column where the "Brand" = Biztech. Your formula will now look as follows:

            FILTER("Base Facts"."Revenue" USING ("Products"."Brand"            ='BizTech'))
  10. Change the column name to Biztech Revenue.
  11. Edit the third Revenue column - this time we will sum the Target Revenue. Set the formula to:
            FILTER("Base Facts"."Target Revenue" USING ("Products"."Brand"        = 'BizTech'))
  12. Change the column name to BizTech Target.
  13. Edit the fourth Revenue column. Create a variance percentage column which will show the percentage actual Revenue is under, or over, Target Revenue. Set the formula to:
    (FILTER("Base Facts"."Revenue" USING ("Products"."Brand" =
'BizTech')) - FILTER("Base Facts"."Target Revenue" USING
("Products"."Brand" = 'BizTech'))/ FILTER("Base Facts"."Target
Revenue" USING ("Products"."Brand" = 'BizTech')))*100.0
  14. Change the column name to Variance.

    Now change the Data Format of the Variance.

  15. Edit Column Properties for the Variance column.
  16. Select the Data Format tab.
  17. Tick the Override Default Data Format.
  18. Change the option Treat Number As to Percentage, and set decimal places to one.

    Before we move on, we will add a conditional format on the variance column. We will create a simple red format when the Revenue is less than the Target Revenue. First, values less than zero, that is, poor performance against the target.

  19. Click on the Column Properties for the Variance column.
  20. Select the Conditional Format tab.
  21. Add a condition.
  22. Select the Variance column from the list.
  23. Set the Operator to is less than and put 0 (zero) into the Value box.
  24. Click OK.
  25. Set the format of the font to red and bold.
  26. Set the background color to #ff8080 (you enter this directly into the box).
  27. Click OK.

    Now values more than 10 percent, that is, good performance against target.

  28. Add condition.
  29. Select the Variance column from the list.
  30. Set the Operator to is greater than and put 10 into the Value box.
  31. Click OK.
  32. Set the background color to #669966 (you enter this directly into the box).
  33. Save the analysis as Analysis Four.

    That's the data created, we will now add a Performance Tile:

  34. View the results.
  35. Add a view, selecting Performance Tile.
  36. Set the Measure to the Variance column.
  37. Untick the Use Measure Description option and edit the Description box. Set it to Revenue vs Target:

    Performance Tiles

  38. Click Done.
  39. Save the analysis.

    We now have a Performance Tile which can be added to the page.

    Experiment now with different Years to see the results:

  40. Change the Filter for Year to 2008.
  41. View the results.

    You should now see a green box instead of a white one:

    Performance Tiles

Recap

So far in this chapter, we have covered creating a simple analysis, adding a Graph, and saving as Analysis One. We then created a Dashboard and put our Analysis One onto a page. Next, we looked at column sorting, formulae, and column properties, which included the Data Formats, Conditional Formatting, and Interaction features. We also reviewed the most common views that an analysis can present; tables, Graphs, Pivot Tables, narratives, and Performance Tiles.

There are 46 different view types in OBIEE 12c, so you should take some time to experiment with some of those we haven't covered in detail. Check out the Heatmap view and Gauge views for yourself.

In the next part of this chapter, we will look at how you present your analysis on Dashboard pages, and how users can interact with your Dashboards.

Prompts

In most of our example analyses, we have been setting Filters for specific values, for example, we added the Filter Per Name Year is greater than 2008 into Analysis Two.

Instead of predetermining what Year the user would like to see, we can give the user the option of choosing the Year when the analysis is run. This turns a static analysis into an interactive analysis.

There are two ways to make an interactive analysis, either ask users within the analysis, by using the Prompts tab, or by placing the analysis onto a Dashboard which has a Dashboard Prompt object on it.

In analysis prompting

We will now examine the Prompts held within an analysis:

  1. Create a new analysis, using Samples Sales Lite.
  2. Add the columns, Per Name Year, Per Name Quarter, and Revenue.
  3. Click on the Prompts tab.
  4. Click on the green plus sign to add a new Prompt.
  5. Select Column Prompt  Per Name Year.
  6. Change the Label to Choose Year.
  7. Change the User Input to List Box.
  8. Change the Default Selection to Specific Values and add 2009.
  9. Click OK:

    In analysis prompting

  10. Save your work as Analysis Five in the Book folder.
  11. Click on the Home tab.

    Analysis Five is listed in the recent section.

  12. Click on the Open link under the title of Analysis Five:

    In analysis prompting

    The analysis will now open with the Prompts page. You select the Years you are interested in seeing and when you press OK, the results will appear.

    Note

    Use Ctrl + click to select multiple values.

    We can also see similar behavior when we place the analysis onto a Dashboard page:

  13. Edit Dashboard One (click on the Edit link on the home page).
  14. Click on the Add Dashboard Page icon and select Add Dashboard Page.
  15. Enter Page Two in the Page Name box.
  16. Click OK.
  17. Drag Analysis Five from the Catalog onto the page.
  18. Click on the Save icon.
  19. Click on the Run icon.
  20. Select the Year from the Prompt box and click OK.

Now we will look at the other method, using a Dashboard Prompt object on the Dashboard.

There are two tasks when creating an interactive Dashboard. First, you make your analysis responsive by adding Filters, and then you create a corresponding Prompt on the Dashboard Prompt object. The good news is that you can set a column to any of the available Prompt types, including a simple Is Prompted option.

A column in an analysis will only respond to a Dashboard Prompt if the column has a Filter.

We will create the analysis first, which will be similar to Analysis Five:

  1. Create a new analysis, using Samples Sales Lite.
  2. Add the columns, Per Name Year, Per Name Quarter and Revenue.
  3. Add a Filter to the Per Name Year column.
  4. Set the Operator to is prompted:

    In analysis prompting

  5. Save your analysis as Analysis Six.

    Now we create a Dashboard Prompt:

  6. Click on New.
  7. Choose Dashboard Prompt.
  8. Pick the Sample Sale Lite Subject Area.
  9. Click on the green plus icon to add a Column Prompt.
  10. Select the Per name Year column.
  11. Experiment with the available options. Choose a default specific value, and Require User input:

    In analysis prompting

  12. Click OK.
  13. Save the Dashboard Prompt in the Book folder as Prompt One.

    Now we have created the two ingredients, let's put them together on a Dashboard page.

  14. On the Home page, click on the Edit link for Dashboard One.
  15. Add a new page, Page Three.
  16. Drag the Prompt One onto the page, and then the Analysis Six below it:

    In analysis prompting

  17. Save the page, then run it:

    In analysis prompting

Experiment by selecting different Prompt values and pressing Apply. See how the results change depending upon what values you choose.

Note

Spend some time looking at all the different options available in a Dashboard Prompt both in the column, and in the Prompt properties.

Remember, only columns that have a Filter on will respond to a Dashboard Prompt.

Result layout

All of the analyses we have built so far have used the default way of presenting the results. The default place to see results is on the Compound Layout screen. This is a results page that has our views on. We can choose which views to place on the compound layout, and we can also create more than one compound layout screen.

To demonstrate this feature, we will create a new analysis, and on this analysis, we will create three separate layouts. We will then place these on a Dashboard page:

  1. Create a new analysis based upon Sample Sales Lite.
  2. Add the columns Per Name Year, Brand, Product, Revenue, Target Revenue.
  3. Save it as Analysis Seven in the Book folder.
  4. View the results.

    By default, you will see a title and a table view. We will now create a few more views so that we can place them on different layouts.

  5. Create a new Table view using the Add View icon on the views menu bar:

    Result layout

  6. Remove the Brand and Products columns to the excluded section.
  7. Click Done.
  8. Rename the view using the rename icon on the Views menu bar:

    Result layout

  9. Rename the Table to Year Totals Table.
  10. Add a Graph view. Choose Line Graph and place Brand in the Prompts section, Year in the Group By section and Measure Labels in the Vary Color by section.
  11. Click Done.
  12. Rename the view as Revenue Lines graph.

We now have four views; one title, two tables and one Graph view.

Now we will create a new layout:

  1. Click on the create Compound Layout icon:

    Result layout

    When you create a new layout, the system will automatically create a new title view:

  2. Rename the layout as Year Layout.
  3. Drag the Year Total table over below the title.
  4. Add another Compound Layout.
  5. Rename it Graph Revenue Layout.
  6. Add the Graph view, Revenue Lines graph.
  7. Save it as Analysis Seven.

    The analysis is now ready to place on a Dashboard:

  8. Open Dashboard One in Edit mode.
  9. Add a new page, calling it Compound Example One.
  10. Drag Analysis Seven onto the new page.
  11. Now click on the analysis properties (xyz icon).
  12. Hover over Show View and the list of available views will appear.
  13. Select Year Layout:

    Result layout

  14. Save your page and run it.

What you are now looking at is the Year Totals Table and a title which are both on the Compound Layout - Year Totals Layout.

So far, we have viewed analysis results on the Dashboard page. Another option when viewing Analysis results from a Dashboard is to launch the analysis from the page and view in another window.

Let's see this in action using Analysis Seven, and also introduce the new 12c feature of sub pages:

  1. Open the Compound Example page of the Dashboard in edit mode.
  2. Add a sub page (available from the add page icon) and call it Example Sub Page.
  3. Drag Analysis Seven onto the sub page.
  4. Change the view shown to Graph Revenue Layout.
  5. From the analysis properties icon, hover over Display Results.
  6. Click on Link - In A Separate Window:

    Result layout

  7. Save the page and run it.

All we see now is a link. To view the results, click on the link and a window will pop open showing the Graphs.

Note

If you rename the object on the Dashboard, there is an option to make the object name your link.

One feature to explore while we are here, is the Report Links option on the properties menu.

Report links are presented at the bottom of the view and can be customized for each view. The users' favorite link is the Export one!:

Result layout

Add the links as shown above, then re-run the page and you will see the links at the bottom.

When we run a Dashboard page, we sometimes want to switch views quickly but stay on the page. The good news is that there is a feature for that in the analysis views:

  1. Open Analysis Seven in edit mode.
  2. View the results.
  3. Add a view called View Selector (from the other views link).
  4. Enter a Caption (Select View).
  5. Select some views from the list in the left-hand box and move them to the right side (the first one will be the default view):

    Result layout

  6. Save the analysis.
  7. Open Dashboard One in Edit mode and add another page.
  8. Call it Selector Example.

    Note

    View Selectors can include individual views (for example, tables) or complete compound layouts.

  9. Drag Analysis Seven onto the page.
  10. Change the view to View Selector 1.
  11. Save and run the page:

    Result layout

Now you can switch views quickly.

Column hiding and showing

When we place a view on a Dashboard, we set the default columns that are displayed. We can allow users to show columns that are excluded from the view, or exclude columns from the view.

To enable column hiding, set the Interaction Properties (available on the Analysis Properties):

Column hiding and showing

Conditional display

One of the best aspects of OBIEE is the ability to direct the user to the most useful information. Sections on a Dashboard page can be hidden or displayed conditionally. The condition can be a specific analysis or can be a saved condition object.

We will put together an example using the condition object. The steps will be the following:

  • Create an analysis
  • Create a condition object
  • Create a Dashboard Prompt - we include this to help demonstrate the concept
  • Create a Dashboard page
  • Add a section and set it to conditionally display

Let's get on with it:

  1. Create an analysis, based upon Sample Sales Lite.
  2. Add the columns Per Name Month and Revenue.
  3. Add a Filter to the Year column, set it to 2011 / 12.
  4. Add a Filter to the Revenue column, set it to less than 100000.
  5. Save the analysis as Analysis Eight:

    Conditional display

    Note

    If you run the analysis, you will see there are no results.

    Now create the condition:

  6. From the New menu, select Condition.
  7. Browse to and select the Analysis Eight.
  8. Save it as Condition One.

    Next, we create the Dashboard Prompt:

  9. From the New menu, select Dashboard Prompt.
  10. Choose the Sample Sale Lite Subject Area.
  11. Add a Column Prompt.
  12. Select Per Name Month.
  13. Set the options that all are not ticked.
  14. Set the Default Value to a specific month.
  15. Edit the Page Properties (use the pencil icon).
  16. Untick the Show Apply Button option.
  17. Untick the Show Reset Button option.
  18. Save the Prompt as Prompt two.

    Putting it all together:

  19. Create a new page on Dashboard One.
  20. Call it Condition Example.
  21. Add a Section (drag the Section object from the top-left panel).
  22. Click on the properties icon and select Condition... from the drop-down menu:

    Conditional display

  23. Click on the select Condition icon and find the Condition One object in the Book folder:

    Conditional display

    We will add some content into the section. A simple message will be sufficient in this example. You can add anything into a conditionally displaying section.

  24. Drag text from the top left panel.
  25. Enter some text:

    Conditional display

    Note

    You can enter normal text, HTML, and CSS in a text box.

  26. Click OK.
  27. Save the Dashboard.
  28. Run it.

You will now see a blank page!

The condition returned false, because there are no records for 2011 / 12.

To prove it will show when Revenue is low, let's add another section:

  1. Go back to Edit mode.
  2. Drag another section onto the page.
  3. Drag the Dashboard Prompt, Prompt Two into the second section.
  4. Drag Analysis Eight into the second section.
  5. Save the page.
  6. Run the page:

    Conditional display

    Default: you will see no results.

  7. Change the Prompt Value to 2008 /10.

Our sales message appears!

Recap

We just built a Dashboard page that can conditionally display a section, depending upon the result of a condition, which is the checking of results of an analysis. We choose a simple example but this could be useful to highlight something to users that needs to be addressed, for example, sales orders not yet processed.

Conditionally displaying results means your users are informed when they need to be, and not when they don't. This changes the way people interact with their data. Do not give them all the data to determine what is good or bad, but present the messages that you need them to act on.

OBIEE is not just for presenting historical Graphs. OBIEE is great for day to day operational use, reporting to users any type of information that they need to do their job. We like to use the phrase, "It is better to see the bunny in the headlights, than the roadkill in the mirror"!

Master Detail linking

When presenting more than one analysis on a Dashboard page, you can link them together using the Master Detail feature. One analysis will be the master, and the other, the detailed analysis.

We will put together an example. The steps will be:

  • Create the Master Analysis
  • Set the communication channel identifier
  • Create child views
  • Set the Filter for the channel
  • Place both on a Dashboard page

Let's get on with it:

  1. Create an Analysis, based upon Sample Sales Lite.
  2. Add the columns Per Name Year, Per Name Month and Revenue.
  3. Edit the Per Name YearColumn Properties.
  4. Change the Primary Interaction on the Year column value to Send Master-Detail Events.
  5. In the box that appears, put salesyear.
  6. Save the analysis as Analysis Nine:

    Master Detail linking

    Now we create the child views:

  7. View the results page.
  8. Add a Graph View.
  9. Place the Year column into the Prompts section.
  10. Edit the View Properties.
  11. Check the box for Listen to Master-Detail Events.
  12. Enter salesyear into the Channel box.
  13. Save the analysis:

    Master Detail linking

    The analysis is now ready to use. We will demonstrate it on a Dashboard page:

  14. Add a page to Dashboard One, call it Master Detail Example.
  15. Add Analysis Nine.
  16. Set the View to Table 1.
  17. Add another column to the page (drag from the left panel).
  18. Change the properties of the second column, removing the page break.

    The above setting will place the second column to the right of the first one:

  19. Drag Analysis Nine onto the page again, into the second column.
  20. Change the view displayed to the Graph.
  21. Save the page and run it.

Now, when you click on a Year in the table, the Graph will automatically update. Note that we could have also created a different analysis to listen to the Master Detail channel.

Saved Dashboards

When a Dashboard is loaded, the query runs with the default values if there are defaults set. This may not suit the user, so they set the Prompt to the value of their choice, for example, their office or region. The next time the page is run, the user does not want to have to repeat the process again, so they can save the settings applied. This is simply done by using the Dashboard properties wheel, and selecting the Save Current Customization option:

Saved Dashboards

Select the Use as default option and your Filters will be applied automatically when you run the page. You can save many customizations, and switch between them using the Apply Saved Customization option in the menu.

Calculated data

So far, we have mainly used simple presentation of columns that are available in the Subject Area. We introduced the formula editing feature when we used the UPPER() function, and there are large number of functions available which can create a calculated result in a column. There is also a method available to calculate row totals.

Let's look at some typical calculations:

  • SUM: You can sum any numerical column, from any of the folders, although you would normally operate on measure columns.

A typical SUM calculation would look as follows:

SUM("Base Facts"."Revenue" )

You can also set a level to aggregate by, for example a Year or a brand:

SUM("Base Facts"."Revenue" by "Time"."Per Name Year")

A typical use for the above calculation would be to create a Percentage column:

("Base Facts"."Revenue" / SUM("Base Facts"."Revenue" by "Time"."Per Name Year"))*100.0

Now we will create an analysis to demonstrate the function:

  1. Create a new analysis using the Samples Sales Subject Area.
  2. Add Per Name Year and Brand.
  3. Add the Revenue five times.
  4. Edit the formula on the second Revenue column, and change it to SUM("Base Facts"."Revenue" by "Time"."Per Name Year").
  5. Change the heading to Revenue By Year.
  6. Edit the formula on the third Revenue, and change it to SUM("Base Facts"."Revenue").
  7. Edit the formula on the fourth Revenue, and change it to ( "Base Facts"."Revenue" / SUM("Base Facts"."Revenue" by "Time"."Per Name Year")) *100.0.
  8. Change the heading to % of Year.
  9. Change the Data Format to Percentage.
  10. Edit the formula on the fifth Revenue, and change it to ( "Base Facts"."Revenue" / SUM("Base Facts"."Revenue" by "Time"."Per Name Year")) *100.0.
  11. Change the heading to % of Year.
  12. Change the Data Format to Percentage.
  13. View the results.
  14. Edit the Table View.
  15. Set the Totals for the Year column and for the Columns (both After).
  16. Save it as Analysis Ten:

    Calculated data

You can see that the Revenue by Year total is repeated for each row of the Year, but the Sum of Revenue is repeated for every row.

You can also use the Aggregate Function to get a measure column total, for example "Base Facts"."Revenue"/Aggregate("Base Facts"."Revenue" BY)*100.0 would also provide the overall percentage.

  • TimestampDiff: A function that work with dates and times, it calculates the difference between two dates, and presents the result in Years, Months, Weeks, Days, Hours, Minutes or Seconds.

The format is as follows:

TimestampDiff(interval, date 1, date 2), where interval is one of the following:

  • SQL_TSI_SECOND,
  • SQL_TSI_MINUTE,
  • SQL_TSI_HOUR,
  • SQL_TSI_DAY,
  • SQL_TSI_WEEK,
  • SQL_TSI_MONTH,
  • SQL_TSI_QUARTER,
  • SQL_TSI_YEAR.

We often use the function to Filter data for relative dates, for example, display the Total of Sales for the last two months.

  • TIMESTAMPADD: TIMESTAMPADD is similar to the TimeStampDiff function, but it returns a date. You add or subtract an interval (For example, Week) to a date. The same list of intervals is used. The format is as follows:
        TIMESTAMPADD(interval, number of intervals, date 1)

If the number of intervals is negative, then you are subtracting from date 1. The example is as follows:

TIMESTAMPADD(SQL_TSI_DAY, -1, date '2010-12-30')

This will return the day before 30th December 2010.

Let's create an analysis using the timeStampDiff function:

  1. Create a new analysis using the Samples Sales Subject Area.
  2. Add Calendar Date twice and Revenue.
  3. Edit the formula on the second date column, and change it to TIMESTAMPDIFF(SQL_TSI_WEEK, "Time"."Calendar Date", date '2010-12-30')
  4. Change the Heading to Week.
  5. Add a filer on the Week column, set it to less than 6.
  6. View the results.
  7. Add a Line chart.
  8. Set it to be a curved line (use the icon on the toolbar).
  9. Set the horizontal as the Week column.
  10. Save it as Analysis Eleven:

    Calculated data

This example shows the trend of sales over 6 rolling weeks from December 30, 2010.

At this point, it is also worth mentioning the special functions of CURRENT_DATE(), CURRENT_TIME(), CURRENT_TIME(), and NOW(). Current date will return a date that relates to the server, without any time components. Current time will only return the time on the server, without any date element. Current_Timepstamp will return both the date and time. Now is exactly the same as current time-stamp.

These functions can be useful in creating a reference point for your other functions. For example, you may want to see the sales for the last three days, in which case you would use the function TIMESTAMPDIFF(SQL_TSI_DAY, "Time"."Calendar Date", CURRENT_DATE) and set a Filter on this column for less than four.

  • RCOUNT: This useful function provides the user with a row count column in the results. Use RCOUNT(1) to have a simple row count for each row of the results. You can also use a group by clause in the functions, for example, RCOUNT(1 BY Per name Year) will reset the counter to one each time the Year changes.

This function can be useful to just display a small sample of the results, by having a Filter on the RCOUNT(1):

  • TopN: This is a combination of a Filter and a function at the same time. It determines the order of a measure, by its value from highest to lowest, and then Filters your recordset for the top N. For example, you can see a list of the top 10 months by Revenue using: TOPN("Base Facts"."Revenue",10). If you just want to see how a number ranks overall, then set the Filter number to be larger than the expected number of total result rows. BottomN is similar to TopN but ranks from smallest to largest.
  • NTile: NTile(number,scale) arranges numbers on a scale are presents where the row lies on the scale. For example, if you had a list of month numbers and used a scale of 6, that is, NTILE(Month Number, 6), then January and February will be ntile 1, March and April will be 2, May and June will be 3, and so on.
  • Calculated rows: So far, we have looked at some example functions that operate on a column value, in a single row. With OBIEE, we can also sum rows together, and not just using table properties. We can create Groups which bring data together.

Let's demonstrate this using Analysis Ten as a starting point:

  1. Edit Analysis Ten.
  2. Save As Analysis Twelve.
  3. In the Criteria view, click on the Selection step icon (looks like a couple of feet!).
  4. Click on New Step in the Brand section.
  5. Select New Group from the sub menu:

    Calculated data

  6. Give the gsroup a name, Biz and Home.
  7. Select the Brands, BizTech and HomeView.
  8. Click OK.
  9. Click Save.
  10. View the Results tab.

You will now see a new row added to the table view, called Biz and Home, which shows the totals for those two brands only.

Note

The above example's use of functions demonstrates the principles. There are dozens of functions available to use, so please spend some time experimenting with them.

Saved columns

One of the cool new features in OBIEE is the ability to create a column and save it for use elsewhere. This feature enables a column to be created once and used in multiple analyses. Better still, any updates to the column can be made in one place and all analyses will reflect the changes.

Let take a quick look at Saved columns:

  1. Create a new analysis.
  2. Add the Office column.
  3. Edit the formula, set it to be LEFT("Offices"."Office", 1).
  4. Click on the Bins tab.
  5. Click on Add Bin.
  6. Set the Operator to Less than.
  7. Enter H in the Value box.
  8. Click OK.
  9. Enter and Name, use A-G.
  10. Add another bin for less than R.
  11. Add another Bin for Equal to or less than Z.
  12. Click OK.
  13. Now save the column by clicking on the Column Properties Wheel and select the bottom option, Save Column As...
  14. Choose a name for the saved object, use Office_Group.
  15. Change the location to the Book folder. You will get a warning message about the location, you can safely ignore the message.

So, we now have a column created, and you can edit it as a stand-alone object. Navigate in the catalog to the Book folder and you will see the column you just created. You can right-click on it and you get two edit options, the formula or the Properties.

Now we will use the column:

  1. Create a new analysis.
  2. In the Catalog panel, locate the Book folder and click on the Office_Group object.
  3. Click on the right-facing arrow to add the column to our analysis.
  4. Add the Revenue column.
  5. Save as Analysis Thirteen.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.224.59.145