1 / 97

Brio Basics+

Brio Basics+. An Overview from an End User Perspective. What is Brio. Brio is a Desktop Application Brio is Microsoft Excel with “SQL” behind it Brio has similar function as Microsoft Access Create Spreadsheets from Results and/or Pivots Break Totals and Grand Totals like QMF

parry
Télécharger la présentation

Brio Basics+

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Brio Basics+ An Overview from an End User Perspective

  2. What is Brio • Brio is a Desktop Application • Brio is Microsoft Excel with “SQL” behind it • Brio has similar function as Microsoft Access • Create Spreadsheets from Results and/or Pivots • Break Totals and Grand Totals like QMF • Create Categories of data columns within the Results • Create Computed Items (If …Then statements) • Outstanding Pivot Function • Drill Down in Brio Charts and Pivots • Copy a Brio Chart into Microsoft PowerPoint • Create BrioQuery Data Model • Import Excel Spreadsheets – Local Result Tables • Import SQL query into Brio • Use QMF Save Data Tables • Using the same QMF id and password

  3. Table of Contents • Getting started • Modifying your query • Joins • Additional Limits • Computed Items • Pivot section • Report section • Saving your outcome • Hints and tips • Union Queries in Brio • Using QMF Tables or Importing Excel Files • Subqueries in Brio • Importing QMF Queries into Brio • Using ‘Local Results’ in Brio • Using the EIS Section

  4. 1. Getting Started • Start BRIO Explorer • Go to Start  All Programs  Brio  Brio Enterprise Explorer • Open the OCE file • Click on “Browse” next to Recent Catalog Extensions • Click on “Open Catalog Extensions” folder • Select BRIO 6.0 SAMPLE 2 – ADVANCED.OCE • User ID/Password Box Appears • Enter the User ID und password of the host system • In production, the user ID and password you entered would have been that of the host system. Brio does not have its own security, but Brio will enforce the security of the host application. • Handbook: Brio provides a comprehensive handbook. • For further reference, use the Help pull down menu or click the “question mark” from the tool bar.

  5. Format Toolbar Menu Bar Standard Toolbar SectionsPane Main Contents Pane Status Bar Tables Pane 1. The Brio Interface • You should now have a screen that looks like this. • Brio is a typical Windows application: • Across the top is the tool bar. • The large landscape in the main portion of the screen is the “canvas” for your work. • The Sections pane (dark grey, upper left) is quick access to the work you are doing. • The Tables pane is a list of tables (only those you have access to!). All of this will be covered in later pages

  6. 1. Drag and Drop • From the Table pane, click/hold PRODUCTS and drag to the main landscape. The PRODUCT table, and its elements, are now displayed within the main landscape. • From the PRODUCTS table, click/hold and drag the PRODUCT_ID to the Request line above the main landscape. You should now have a page that is similar to the screen to the left (sans arrows).

  7. “Toggle” request, sort, limit by clicking here From the tool bar click Process. 1. Running that first query • Request/Sort/Limit • Request is similar to the Select statement in a QMF query - This line will hold the data elements you wish to see in your results. • Sort equates to the Order by statement in a QMF query – It orders the results of your query. • Limit is the Where statement – These are the criteria placed against the data you’re pulling.

  8. Return to query Number of rows 1. Running that first query - Results • Results • After following the previous steps and hitting Process you’ve run a simple query that grabbed all the PRODUCT_ID values within the PRODUCTS table.

  9. 2. Changing your query • After returning to the query landscape: • Drag PRODUCT_FAMILY and PRODUCT_CATEGORY to the Request line. • DragPRODUCT_CATEGORY to the Limit line. • The Limit line will prompt you for action.

  10. Drop down box Enter desired value here From the tool bar click Process. 2. Setting the limits • Click the pull down box for the “type” of limit (the default is =Equal). • As with a standard QMF query, you have multiple choices: • Equal • Less Than/Greater Than • Between • Begins with • Ends with • We will stick with the default of =Equal and set the value to “ABC”. • Click OK.

  11. Click here to return to the query 2. Running the new query • Something go wrong? • The requested data elements were returned; however, no values are present. • That means there are no Product Categories named “ABC”. • Click the Query tab in the Sections pane.

  12. Show Values As with any Windows application, hold the Ctrl key or the shift key to choose multiple (or consecutive) element values. 2. Second look at the Limit box • Once back in the query landscape, double click the button within the Limit line. • Click Show Values. • NOTE! • These are ALL the values within the table. If you choose to do this with a multi-million row table, it will take time! • Select all the “A” values from the drop down box and click OK; then rerun the query (click Process).

  13. More on Limits – Load from TXT File

  14. More on Limits – Load from TXT File

  15. More on Limits – Load from TXT File Now your PRODID limit contains the values from the TXT File

  16. 2. More Results • A new query has been built. • The Sections pane has changed to Results. • And these results are now on your client workstation. • You can disconnect from the system and perform further analysis work on these results.

  17. Auto Join 3. Getting complex • Back to the query landscape right click on the Product table and then click Remove. • From the Tables pane, drag SALES_FACT and drag STORES to the query landscape. • Right click on the line between the two tables, and click Properties.

  18. Types of Joins 3. Joins • Joins • Default join is Simple • Where the tables are joined (relationally) based on like values between the two tables. • Other types of Joins • Outer • Left • Right • Override • You can also right-click the line drawn between the tables and hit Remove. Then by dragging a value from one table to the other you can perform your own join.

  19. 1 2 The Auto-Join Option • Brio automatically joins two fields from two tables if their names and data types are identical. • This auto-join option is not recommended when running UNION queries with CM & PM tables. • To turn it off, go to the DataModel menu (1), select Data Model Options, then the General tab. Click the Auto join tables (2) to disable the Auto-join. Now, you can create any joins manually by dragging the items from one table to the other.

  20. More on Joins – Data Model Options

  21. More on Joins – Data Model Options – Set all joined topics

  22. 3. More on the complex query • Using the simple join (set to Equal): • Drag to the Request line: • from STORES • STORE_NAME • STATE_PROVINCE • STORE_TYPE • from SALES_FACT • PRODUCT_ID • AMOUNT_SALES • UNIT_SALES • Drag to the Limit line: • From SALES_FACT • DAY_ID • Select 366 through 375. • Click OK.

  23. 3. Adding a Sort to the Query • Adding a Sort • Drag the STATE_PROVINCE from the STORES table to the Sortline.It won’t let you do it! This is Brio’s way of ensuring you sort only on what you’ve selected in the query. • Instead, drag STATE_PROVINCE from the Request line.

  24. More on Sorting – Query vs Results Notice the Sort in the Query Notice no Sort in the Results

  25. More on Sorting – Query vs Results

  26. More on Sorting – Query vs Results

  27. More on Sorting – Query vs Results

  28. More on Sorting – Query vs Results

  29. 2) Click Close when done 3) Click Process from the tool bar 1) Click View from the tool bar; then click Custom SQL from the drop down to view the query that has been created. 3. Custom SQL

  30. Results section highlighted Data on your ThinkPad Tables you have access to is now changed to the “columns” created by your query 3. Your Results • Your “local” results • Once you have the data returned from the query, it is “in” your local computer, much like a spread sheet would be. • You can sort data individually, you can change the sequence of the columns, hide and duplicate rows, etc… All of this without re-running the query. • Right click is your friend • To modify an item, left click to select it – the item will turn black. Then right click the item - the right click menu contains all the options available to you. • select the total table, click on the highlighter on the left, up end of the table • e.g. you can activate text wrap (the text will appear on two or more row’s instead of one) Highlighter for the total Result table

  31. Some Examples of Report Options • Right Click is your friend • When modifying your report, many of the options you want to do will be via the ‘right click’ • Place the pointer over the AMOUNT_SALES columns and right click; then click ‘Grand Total’ • A pop-up window will ask what to ‘sum’ on: Click ‘Amount Sales’ • Go to the bottom of the report Right Click Left Click

  32. The Grand Total • As with a spread sheet, or a QMF Query, you can simply add a grand total to the bottom of a report Grand Total “Getting to the Bottom” Right Click and Choose “Grand Total”

  33. Break Totals • Break Totals • In the ‘Every Break In’ choose ‘STATE_PROVINCE’ • Select ‘SUM’ • In the ‘Add Total To’ choose ‘AMOUNT_SALES’ • Click “OK” Every Break In Sum Add Total To Right Click and Choose “Break Total”

  34. 4. More Advanced Queries: Query Properties • Back in the Query section: • As with a QMF query, you can also select only different rows with a Brio query. To do this, • go to the Query menu and choose Query Options. Then you will be presented with a pop-up box as shown in the picture to the left. Check Return Unique Rows. This equates the Select Distinct statement in QMF. Set time limit to 30 minutes

  35. 1) Click Day Id to highlight. Click the little arrow, then choose Var. 3) Click Cancel; then click the Var button again to clear the prompt request. 2) Now click Process. The user will be asked to supply the desired value for Day Id. 4. More Advanced Queries: Variable Limits • When creating queries for team members, the same query may be used; but different people on the team may need to query different values. As in QMF, you can add PROMPTS to a Brio Query.

  36. Click the checkmark to recognize the value(s) 4. More Advanced Queries: “AND/OR” Statements • From the Sales_Fact table, drag Store Id to the Limit line: When prompted, enter the number 4; click the green checkmark, then enter the number 10. Again, click the green checkmark. Ensure both are “highlighted”. • Click OK.

  37. The values you chose Click here to toggle 4. More Advanced Queries: AND/OR Toggle • Holding the pointer over Store Id, the “hover” should show “4,10”. • AND automatically appears between Day Id and Store Id. By clicking the ANDbutton, you will toggle between AND and OR.

  38. The logic is “questionable” 4. More Advanced Queries: Brackets • Brackets in a query are as necessary as brackets in algebra. • As an example, let’s look for activity from a specific Store Idor on a specific Product Id during the chosen Day Id. • Drag Product Id to the Limit line. Choose products 5 through 15. • Toggle the AND between Store Id and Product Id to OR. • As written, the results from this query are unpredictable.

  39. Brackets Brackets Toggle 4. More Advanced Queries: Brackets • Click once on Store Id, hold the shift key, and click on Product Id (this will select both elements). • Hitting the “()” button will place brackets around the selected data elements. • This query will now provide predictable results. • Click Process.

  40. Pop-up from right click; choose ‘Add Computed Item’ 5. Adding Computed Items • Return to the query section • Go to the Limit line and select both Store Id and Product Id, • Right click on the elements and choose Remove. • Rerun the query • The sections pane will change to results. • Computed Items • Select one of the columns. • Right click, then choose and left click Add Computed Item.

  41. Type in the name of the column: • Average Sales Basic Algebra 3) Click on “/” 5) Click OK 4) Click Reference (again) and choose Unit Sales Click OK 2) Click Reference and choose Amount Sales Click OK 5. Computed Item

  42. New Column (You may need to move the screen over) 5. Results from Computed Item • A new column has been placed at the far right of your report. • The results within this column is the Amount Sales / Unit Sales as defined in the previous chart. • You can change the background color of the column heading to differentiate between database and self-created columns. To do this, click on the column title, then right click and choose Background.

  43. Left click here to highlight the entire column Choose Number 5. Formatting Columns • The Computed Item column is simply math between the two columns that were specified when setting the column up. • The results are calculated out to the default number of decimals. • To insert a fixed text in a Computed Item, enter the text in ‘…’ • To change the format, choose the entire column (by left clicking at the top of the column), • then right click anywhere in the column • and choose Number.

  44. Currency and $ with 2 decimals 5. More Formatting Columns • Simply choose how you would like to see the results represented. • Choose Currency. • Then choose the number of decimals from the possibilities offered. • You can also choose the font you wish to use, colors, alignments etc…

  45. Select the Product ID column; then right click and choose Add Grouping Column. 5. Grouping • Not to be confused with the “grouping” that is done in a QMF query. • Grouping in this case will take a series (or string) of values and assign a specific value to them. • For example, you can assign the value of Small, Medium or Large to a subset of Product IDs. • be aware by creating a grouping column, because of new values which are not relate to the groups, go to options and define what will happen with this values, otherwise BRIO set it default to 0 Options: to define what happen withungroup values

  46. Click hereNew Group Choose 1-99 Enter this value when prompted Once you’ve defined the values to be used in the group, hit this arrow BEFORE you hit “OK”. 5. Creating the Group Names • Once you clicked Add Grouping Column you will be presented with a pane that will allow you to assign values to a “group”. • Click New Group. • Enter Small. • Then choose 1-99 for the values. • Then hit the arrow to move the chosen items from the Available Values column to the Items in Group column. • Repeat this process for Medium, setting the values 100-199; and the balance into group name Large. • Before you hit OK the AvailableValues column should be empty.

  47. That first Chart • As you drag and drop each data element into the X and Y Axis, the chart will be built. • Your ‘default’ chart will be a bar chart. • There are different styles of charts available. • We will be using a ‘pie’ chart for this exercise. • THIS CAN BE COPIED TO POWERPOINT Click the dropdown Choose ‘Pie’

  48. Customizing the Chart • There are many things you can do with a chart once created; many of the capabilities are similar to Freelance or PowerPoint. • The first thing is to stay organized. So, under sections, double click the Chart bar; you’ll be prompted to enter a new name. DO NOT just change the name within the chart. There is no ‘link’ from the chart to the section for Chart. • By clicking once on any section of the Pie chart, the section will be highlighted. Right Click for a pop-up menu of choices. Including: • Drill Down; • Changing Color • Pulling out a slice of the Pie • Experiment here, there are lots of ways to customize the chart! Double Click And Change the name Click Once to ‘highlight’; Then right-click for these Options

  49. Changing the Chart • Once you’ve created a chart (in this case, you’ve already created a pie chart) you may change the format by simply clicking the pull down menu and choosing a new chart type. Pull Down Menu

  50. Pull Down Menu Insert Choose NewPivot Data Elements ------- Label and Fact placement 6. A quick view of Pivot • Pivot • Allows additional analytical flexibility in a spreadsheet format. • Choose Insert from the tool bar, then choose NewPivot. • You’ll be presented with a blank landscape with a list of available data elements and locations to drag and drop.

More Related