OpenBI Docs Buy

Your first model

In about ten minutes, on the example project that ships with OpenBI Studio: you load a small file of sales targets, relate it to the products, write two measures, put them on a report page and save it all as a version.

You need OpenBI Studio installed (Install OpenBI Studio). The steps are the same on Windows and on a Mac; where a Mac differs, the step says so.

1. Open the example

Choose File › Open the example project. The studio copies Example Brewery to Documents\OpenBI\Example Brewery as a project of your own and opens it on its report pages. It is a made-up brewery: four products, three plants, fifteen shops in Oslo and three years of monthly sales.

Look around for a minute: the views down the left (Report, Tables, Model, OAX and the others), and the page tabs at the bottom of the Report view.

2. Get data from a file

The brewery has a target for each product. Make that file: open Notepad (on a Mac, TextEdit in plain-text mode), paste these lines and save them as Targets.csv in the project’s folder, Documents\OpenBI\Example Brewery:

Product,Target liters
Pils,1000000
IPA,350000
Cider,280000
Lettøl,180000
  1. On the Home tab, in the Data group, choose Text / CSV. The Get data window opens on its Connect page.
  2. Under File, choose Browse…, pick Targets.csv, then Next.
  3. The Navigator shows the table Targets with its two columns and four rows. Choose Load.
The Get data window's Navigator page: on the left the table Targets, from Targets.csv, ticked; on the right its preview, two columns, Product and Target liters, and four rows: Pils 1000000, IPA 350000, Cider 280000, Lettøl 180000. Buttons Back, Transform data, Load and Cancel.
The Navigator: load the table as it is, or shape it first with Transform data.

The table is now in the model, and its query is saved with the project, in M (the language of Open Query, OpenBI’s query editor, compatible with Power Query’s). Because the file is inside the project’s folder, the query names it by the project (${PROJECT_DIR}/Targets.csv), so it still works when you move or share the project.

3. Relate it to the products

  1. Choose Model on the left. The diagram shows every table; Targets is the new card.
  2. Drag Product in the Targets card onto Product in the Products card. The New relationship form opens on the right, filled in: From (the many side) Targets[Product], To (the one side) Products[Product].
  3. Leave Active ticked and Cross filter at Single, and choose Add.
The New relationship form: From (the many side) Targets[Product], To (the one side) Products[Product], Active ticked, Cross filter Single, Cardinality boxes Many to many and One to one unticked, and the buttons Add and Cancel.
The form the drag opens. Nothing is saved until you choose Add.

Instead of dragging, you can choose Relationship above the diagram and pick the two columns yourself.

4. Write two measures

  1. Choose Tables on the left, then Targets in the list. A new measure goes in the table shown.
  2. On the Home tab, choose New measure. The formula bar opens above the view with Measure = , and the ribbon shows the Measure tools tab.
  3. Replace it with the line below. As you type, the line under the formula bar shows the value over the whole model: = 1810000. Press Enter to save it. 
    Target = SUM ( Targets[Target liters] )
  4. On Measure tools, set Format to #,0.
  5. Choose New measure again and write the second one. Check Home table on Measure tools says Targets, press Enter, and set its Format to 0.0 %. Its value: 99.8 %. 
    Liters vs target = DIVIDE ( [Liters sold], [Target] )

[Liters sold] is one of the example’s own measures, on the Sales table. Typing [ lists the measures and columns you can use. Esc puts the formula bar back to what is saved. Measures are written in OAX, OpenBI’s expression language, which is compatible with DAX (see Measures and OAX).

5. Make a report page

  1. Choose Report on the left, then New page on the Home tab (or + at the end of the page tabs). A blank page, Page 1, opens with Design on.
  2. In the Visuals pane, under Add a visual, choose the first icon, Column chart. A chart appears on the page, selected, and the pane shows its Fields.
  3. In the Data pane, open Products and drag Product onto Axis. Then drag Liters sold (in Sales) and Target (in Targets) onto Values.
  4. Click an empty part of the page, then drag Liters vs target onto it: a measure dropped on the page makes a card.
  5. Drag the visuals to move them, and their handles to size them. To rename the page, double-click its tab.
The Report view in Design mode: on the page Page 1, a column chart 'Liters sold against target' with a blue and an orange column for each product, Pils, IPA, Cider and Lettøl, and a card 'Liters vs target' showing 99.8 %. On the right the Visuals pane with the chart's fields and the Data pane, where the tables the chart uses are ticked.
The new page: Pils and Cider sold a little over their targets, IPA and Lettøl a little under.

There is no Save button for pages: each change is written to the page’s file, reports\page-1.html, as you make it. Ctrl+Z undoes the last one.

6. Save a version

Your changes are on disk, but not yet a version you can go back to. Choose Save version on the Home tab (on a Mac, ⌘+S), or click the changes count at the bottom right.

The Save a version window lists what changed: the model’s files, the new table, the page and Targets.csv. Write what you did under Message, for example “Targets per product: a relationship, two measures and a page”, and choose Save version.

A version is a git commit of the project. Versions on the left shows each one with what it changed, and takes you back to any of them: see Versions.

Where next

Last updated · OpenBI Studio 0.3.75