5. This section focuses on SpagoBI user interface, highlighting basic navigation features. Login. To ensure data protection, users can access SpagoBI through a. SpagoBI 5 User Manual, SpagoBI SUITE – Basic reporting & Data Exploration Tools Based on the SpagoBI 5 serie, this first volume provides an introduction to . A new User Manual on SpagoBI 5 is available now on SpagoWorld Shop! on the environment and main analytical tools of SpagoBI suite.
|Language:||English, German, Dutch|
|ePub File Size:||16.61 MB|
|PDF File Size:||11.29 MB|
|Distribution:||Free* [*Sign up for free]|
SpagoBI 5 User Manual - Download as PDF File .pdf), Text File .txt) or read online. Free e-book. Users expect market-ready offerings (i.e.: code complemented by: Users want a full business proposal, not just bare code. . SpagoBI 5 User Manual. Video-. SpagoBI 5 User Manual - SpagoBi最新版本用户手册.
The way we analyze the measure sales amount is called a dimension. Therefore the sales date is one dimension of looking at the sales data; the store where the sales occurred is another dimension of looking at the sales data. We can therefore look at the sales data by date, by store e.
We would like to demonstrate this in SpagoBI using simple data with one dimension and several measures. Below is the problem description: Shemma Global is a Business intelligence company that specializes in data mining and analysis.
They would like to view the memory usage of one of their servers by event time. Olap 1: Server uptime data. This kind of table is called a fact table. Normally dimension data like date, sales region e.
A foreign key is then included in the fact table to link the two. Consider a telecommunication company as another example. We would make the fact table the central table in our schema surrounded by dimension tables as shown below. Table 1: The fact table Activations.
Consumed Airtime. The above schema is called a star schema. If we were to build an OLAP document for this assignment, we would build a single fact table Table 1 and link it to the dimension tables using foreign keys.
For the purpose of demonstrating the server problem, we will keep the measure and dimension in a single fact table. In the telecommunication example above, we can create a cube which can answer questions like how many subscribers were activated on a certain year, certain quarter or certain month, or how much airtime was consumed by customers from Nairobi region or how many subscribers are in pre-paid. The cube will be created using SpagoBI studio which will automatically generate for us the xml schema file.
This will be a simple cube based on data from the diagram Olap 1 which shows the average amount of used memory by day. The average used memory here is the measure or fact while the day is the dimension. So our cube only has one measure, the average amount of memory used on any given day. Here are the steps. This will start SpagoBI studio. Click on Finish. This will remove any previously defined jar file. This will add the mysql jdbc driver 34 S p a g o B I 4.
Without this jdbc driver, SpagoBI studio will not be able to communicate with the mysql database and therefore we will not be able to create any report. For database enter BIDB.
Remember this is the name of the mysql database that comes with the CD included in this book. Enter username and password for your database. Click on Save password. Click on Test Connection then Finish. Right click on Business Models and select New Model. For the Model name use UptimeModel and the file name use Uptime.
Make sure business Models folder is selected. Select the BIDB connection we had created previously. Right Click on the business model and click on show properties view. Change the name to Server Uptime. Change the type to cube. Change Swap usage to be a measure. Do the same for processes, available memory and used memory.
Notice how the icon changes. Now that we have measures, let us add a dimension. Remember a dimension helps us look at the data in various ways. For example, we would like to look at the data by week day.
SpagoBI 5 User Manual
To create a Dimension, we will add a new business class. Right click on business model, select Edit then Add business class. For the attributes, select weekday and id. We will use the column id to link with the cube table. For name, enter Analysis Period and click on Finish. Change the type of the Analysis Period to dimension. Drag Weekday to Hierarchy. Lastly let us link the cube and the dimension. Then click on Add relationship and Finish. Now it is time to create the Mondrian Template which is an xml file that contains all the things we have been doing.
It is this xml file that will be uploaded to the SpagoBI server. Under Resources select Data source. Driver and then click on the Test button. You should get a success. Now that we have our Data source created and tested, lets go back to SpagoBI studio and create a link to SpagoBI server so that we can upload the Mondrian Template we created on the Studio to the server.
Under resources, right click on server and select New Server. The password is same as username by default and click on active. Click on Test. It should be a success. For the name, enter ServerUptime. You should get the screen below if all went well.
Now under document browser, select biadmin folder and click on the server uptime document. Then select Open Olap Navigator. The small left most button. Click on Measures and make sure all measures are selected. Click on OK. Then Ok again. Now let us use the OLAP cube to answer some questions. How much memory in KB was used on Sunday? When is memory mostly used? Can we show this in a graph? And there you have it, your very first OLAP document. Prepare the database. The data for this assignment is in the table storesales.
The sales department would like to view the total sales for any store by year, quarter, month and day. Our measure is sales and we have a time dimension here with multiple hierarchies: year, quarter, month and day.
If you got this book free and would like to have the companion videos and database then contact us at info ogutu. Assignment Questions and Answers: 1. Compare sales between quarter 3 of and quarter 3 of for all stores. Include a column with Total Sales. References and further reading: 1. Describe what Location Intelligence is. Describe the importance of Location Intelligence to your business. How is location Intelligence useful to an organization. Creating a location intelligence document, Step by Step.
Location Intelligence is the integration of Business Intelligence and Geographical data. This gives a visual representation of the data on a map. This is a document that shows the number of livestock that are at risk due to famine to be used by a disaster preparedness organization.
For the policy makers, it would be easier to understand as they will be interested in regions with red color. You can see that we have different tones of red color on the map. The regions which have a higher tone like Upper Eastern have more livestock at risk compared to other regions. In this chapter, we will learn how to create such documents. The document shown above is called a Geo document in SpagoBI. It is created using an SVG map and an xml template called a geo template.
Apart from the map and the template, you will also need a source of data. So we need to understand how to create these 3 components; an SVG map, a geo template and a data source. Assume you are a consultant for the Kenya Red Cross and you are tracking an upcoming famine in the month of August.
You have data from various provinces and regions in Kenya and you would like to put them in a map so that you can project it in the big screen on the conference room.
The map will be updated automatically as data comes from the provinces. To solve this problem we will need several things to be done first: i We will need the data. We will be creating the map of Kenya with its various regions.
We can do this with a free tool called Inkscape or you can use Corel Draw if you have a license for it. Instead of starting from scratch, follow these steps to create a map based on an already existing map. If you are good with inkscape or Corel draw, you can skip this and draw your own SVG map.
If you chose not to create the the map, jump to step iii otherwise follow the following steps to create the map.
If you open your file, it should look like this. We will use this image as the base of our SVG map since we do not want to waste time drawing the map of Kenya with all its borders. Download and install Inkscape. Open Inkscape.
Select embed. You can see that the regions are already separated by lines so we will just use a fill tool with white color to segment and name each region we need. The map will change as show below. We need to change it back to white so click on fill and change it to fill: ffffff i. Why are we doing this? We do this because once we are done with naming all regions, they should all be white and will only take the color based on data from the database. Click on Set to make the changes permanent.
Finish the other areas. Your map should look like this. Now we need to select all regions and group them. How do you do this? Once all the regions are selected, to group them, go to object and select group.
When you look at the xml, it should be as shown. Note that all regions are under the county group. So how will SpagoBI know how to color the various regions based on the data from the database? It will compare the names of the regions against a column called county in the database.
This column will have names similar to the various regions in the SVG map. So always ensure your group name is same as the column name where the region names are stored.
See below for a sample table we will be using for this assignment. Now your map is ready! Under Template, use Kenya.
For format, select SVG. Click on Save. Let us create the location intelligence document template. Geo Template. Most documents in SpagoBI needs a template that defines the structure and source of data and the location intelligence document that we are going to create is no different. The template we are going to create is called a geo template and has the structure outlined below. Remember we had grouped all the regions into a group called county in our SVG map? Here is a sample of the data we are using.
Considering our template above, we only have one hierarchy called Kenya and one level called county. From our select statement in 3 above, you can see that this is the column where we have region ids and should have the same on the SVG map e. Note that the path id on the svg map match the contents of the column county in the table. In our case the group is called county. Follow these steps to create the dataset. Creating the dataset. Under Resources, select dataset. Under dataset list click add.
Click on the Type tab. For the DataSet Type, use query. Click on the preview tab and select the preview button. Save your dataset. Uploading the Geo Template.
Copyright Engineering Group, SpagoBI Labs
You can find the template in the folder Location intelligence in the CD that came with this book. We need to create a Geo document using this template. Proceed as follows. Login to SpagoBI as user biadmin with password biadmin. Under analytical model, select document development. Click on custom documents. Click on add. For the label use Kenya Livestock. For the name use Kenya Livestock. Under type select Location Intelligence. For the datasource, select BIDB. We are done with creating the document; now let us look at it.
Click on your document to open it. Describe the importance of Dashboards to your business. Use High Charts. Building Dashboards using High charts. It might be that the car is running out of fuel or that the engine oil is getting low. It might be also that the battery is not charging or you are driving with handbrake on! An automobile dashboard need to be easy to understand and should not take time to read; remember you are driving and cannot stare at the dashboard for long!
A good dashboard should be easy to understand and should portray relevant information only. The business community copied this dashboard idea from the automobile industry. Business dashboards show at a glance the state of the business at any given time. As an example, a chart might show a comparison between sales between current quarter and the last quarter. If all sales for previous quarters are greater than current quarter, then something is definitely wrong.
Since a dashboard should be easy to read, normally only summaries are shown in dashboards. Dashboards also show trends and comparisons. We will create our first dashboard to compare sales between current year against sales for previous year.
The dashboard will be built using the highcharts library. First, we will write the Sql that will help us get the sales comparisons between current year and previous year for the Nairobi store. Here is the result of the query: 83 S p a g o B I 4. It is called dash1. In case you got a free softcopy of the book then write to the author at xogutu gmail. Now that we have the query, we will create the xml template that will be used by highcharts.
There are two methods we can use to achieve this: We can use the SpagoBI studio which is a graphical tool or we can use a manual method and specify the entries in an xml file. We will start with a manual method so that we understand the contents of the xml file.
We have included the xml below, it can be found under the dashboard folder in the CD that came with this book. Now login to SpagoBI as the biadmin user and follow these steps to create the highchart document.
The first thing we need to do is create a dataset where our highcharts document will get its data. Click on the add button. You should have the following once you are done. Click on the Type TAB. Under DataSet Type, select Query. Remember we had created this data source previously.
Under Query, Paste the query in the file dash. You should have this once you are done. Click on preview button. You should have the output shown below. Save the data set. Click on Insert. Add create the document as shown below. The file can be found on the CD that came with this book under dashboard folder. You should have your first chart! Chart Size. You can also specify the size of the chart in this element.
In Spago BI, this can come from a column in your data set query. Normally they come from database tables or views through the SpagoBI data set. Alias element. When false it appears on the left. As an example, if we say opposite is false as shown below, 93 S p a g o B I 4. This element is used to set the labels of the items on the Y axis. The above block of code produces the legend below. It can be line or column.
The contents of the series come from a table column through the SpagoBI dataset. Instead of editing xml files manually, we can use the SpagoBI studio to generate for us the xml file to be used as a template for the highchart document. Follow these steps to create a similar document as the one above but using SpagoBI studio.
Start SpagoBI studio. For project name enter Highcharts. Enter the chart title, subtitle and size Width and Height as shown below. Remember this is the column in the database where the name of the months are stored e. So the X axis will consist of month names from January to December. Under Series list, create the two series as shown. Chose a color of your choice. This will be the color of the line chart. A document will be created, which can be executed directly using the document browser.
Figure 56 - Create a new document 29 Copyright?
Moreover, SpagoBI supports data mash-up, to integrate enterprise data and externally sourced data. Cockpit documents can be created both by technical users and end users.
Cockpits are part of SpagoBI ad-hoc reporting system. Figure 57 Cockpit Cockpit engine designer is able from: Technical menu directly into the Document Browser for the technical users? Figure 58 Cockpit By clicking on the add widget button a configurable window opens. Figure 59 Cockpit — New Widget 31 Copyright?
As a first step, choose the dataset. The wizard window shows all datasets available according to the loggedin user role. Figure 60 Cockpit — Dataset selection As a next step, choose the widget you want to use. The wizard window offers a tool bar with all available widgets that you can configure.
Widgets include: Add a new widget? Choose the dataset? Choose how to display your data? Place your object within the available space During the second step, multiple datasets can be selected. The dataset already used will be orange-bordered, while the other one s will be blue-bordered.
Associations should be set within the designer when widgets are built on different data sets. To set associations, click the dedicated button. Figure 63 Cockpit —Association editor The editor shows one column for each data set. To create associations, select the columns to be correlated and click the [icona] button to make them effective. Users can define their own query graphically, execute it, check the results, export them, save the query for future use and generate a reporting template.
The QbE interface includes a Schema area showing the data model, as well as an Editor area in which the user can build the query see Figure Figure 68 - QbE interface The data model represents a business model with different levels of complexity, namely a high-level representation of the data included in the data base.
Each single entity is composed of a title, some attributes and relationships with other entities: Figure 69 - Data model By exploring the content of an entity i. Attribute made of numeric data and additional data e. It refers to fields that can be associated to a category e. It refers to relationships or connections between two entities e.
It includes the list of columns to be returned by the query. To add a new attribute in this section, just drag and drop a field into the item tree or click it. Figure 70 - Select fields For each dropped item, it is possible to: Define an alias? Apply a mathematical function, in case of aggregation? Set a sort criteria?
Indicate the column s to be included in the result? Indicate whether a column shall be visible in the result? Delete a column? Add a filter criteria? Add a filter on groups. In this section, a filter criteria may be added. Similarly, to add an element in this section, drag and drop it and then click on the data model. For each dropped element, it is possible to: Specify a filter operator Figure 71 - Filters?
Define the right operand using a fix value or choosing one of the options included in the lookup see Figure 72? Define the right operand using a model attribute?
Specify whether the filter value shall be requested during the query execution it is for Prompt. Therefore the Filters section enables the definition of filters with multiple relationships using the AND and OR operators. Moreover SpagoBI offers a tool for building complex expressions: Figure 73 - Multiple filters 38 Copyright? Figure 74 - Expression Wizard The expression wizard is built through the Exp.
Items section that includes the Operators list AND and OR filter relationships and brackets defining the execution order , and the list of available filters in the Operands section. To test the expression, use the Log box as shown in Figure Once you close the window and execute the query, the content of the expression will be considered, if included.
It works like a normal filter, with some additional columns for grouping functions. This way users can choose the value of the operand at query execution. Once the query has been defined, it can be executed using the Preview button. Figure 75 - QbE preview Similarly to the Worksheet, in order to get back and continue building and designing the query, click the Designer button at the top right of the page.
Figure 76 - QbE designer The QbE designer offers a section dedicated to the query catalogue see Figure 77 on the left. If the QbE analytical document has one or more analytical drivers parameters , they will be listed here.
Right click on the menu of the chosen entity and select Add calculated field: Figure 78 - Entity contextual menu The new window offers an editor in which you can define the calculated field. Figure 79 - Calculated field To build a calculated field, you shall define: Type string, number, date? Nature measure or attribute? This new item can be used as a model attribute. The following table includes some functions that can be used to build an advanced calculated field.
It returns the length of a bit string It converts a string to uppercase It converts a string to lowercase It returns the substring of str string with the given length to the idx position. It joins two strings It returns the length of a string It returns the position in which the S character is located, starting the search at a specific idx point of the string Notes?
FROM] str It eliminates spaces in a string It returns the current date It returns the current time It returns current date and time It returns time, minutes and seconds It returns year, month, day case?
In order to analyze data, it is worth grouping these values into categories. For this reason, the QbE engine can define and manage intervals in queries. To create a new band, click Add interval in the contextual menu: As shown in Figure 82, it is possible to select numeric fields and data functions to define intervals.
The procedure to define a band follows. Figure 82 - Bands Click the Next button at the bottom right corner of the guided procedure and a new window will appear to create new instances of the bands. Click Add Band to add a new instance, set values and related labels.
Click Add Default to include a default interval: Click Finish. The band will appear as a node in the Schema panel. If you wish to modify the interval, click Edit Range. Figure 83 - Interval definition 44 Copyright? Store - Region entity? Customer - Region entity? Sales Fact - Store entity? Sales Fact - Customer entity. Ambiguity arises when attributes coming from the various tables are dragged and dropped into the query that is build in the QbE, as in Figure Figure 85 - Ambiguity Click Relationship Wizard in the Query editor and a pop-up window will appear, where users can define the path: At this point, modify the relationship so as to eliminate ambiguity: Follow the same procedure for fact tables.
Figure 86 - Generated query 46 Copyright?
SpagoBI 5 User Manual
On the other hand, if the query aims to show the region of the Store, it is necessary to modify the wizard as follows: The generated query follows: To describe this feature, it is worth using an example. Figure 87 - Alias The two relationships concern the start date and end date of the promotion. As shown in the Figure 87, this information can be retrieved from the QbE graphical interface.
The Promotion entity includes two relationships see points 1 and 2 in the figure , whose tooltip returns information on how the relationship is structured see point 3. If you wish to see the list of promotions with a specific start date and end date, it is necessary to drag and drop the name of the promotion Promotion entity and the The Date field Time by Day entity two times by changing the alias, as well as the name of the column to be visualized in the results of the query see points 4 and 5.
By executing the query, users can investigate the system behaviour: If you wish to use both relationships end date and start date , select both. The tooltip shows the complete path using an intuitive tree layout.
Once the relationships are selected in both entities, click Apply. SpagoBI will show the following window: List of Fields associated to the entities selected in section 1?
List of fields involved in the query. To distinguish the fields during the execution of the query, it is necessary to identify all the fields involved in the query included in section 3 with the aliases of the entities that contain them included in section 1.
The result follows: Figure 88 - Alias association To check whether the association was correctly set, you can refer to the relationship specified in the tooltip: Figure 90 shows the desired result.
Figure 90 - Query result 50 Copyright? Figura 91 - GIS This feature is based on a user-friendly interface.
Questions tagged [spagobi]
Click the icon to view the legend Figure 92 - GIS legend? Click the icon to select the layers to be visualized or the list of map providers? Click the icon to select the layers to be visualized or the list of map providers Figure 93 - GIS layers 51 Copyright? Choose whether you want your business data to be represented on a map with zones or points. Figure 94 - Data representation levels Figure 95 - Map Point view Finally the following feature allows users to easily identify the data to be visualized on the map by selecting the indicator or a filter that can make visible only a selected part of the analysis, filtering on some characteristics as shown here.
Figure 97 - Save new map 52 Copyright? This engine can be used irrespectively of the so-called geographic context - it allows to display the distribution of indicators on any structure that can be represented on a map e. Generally, the interface is composed of the following three elements: Hierarchy navigator?
Level navigator, allowing users to integrate additional levels in the map e. For example, as shown in Figure , you can add the charts associated to each region. Figure - Charts level Users can navigate the analytical document, specifically: Zoom-in and zoom-out using the Navigation window?
Access the legend? View the measure catalogue and choose the measures to be visualized? View the list of available levels and select the desired ones? Associate an area or a point on the map to an analytical document - alternatively you can make information appear in a box. This option can be configured by administrators. Figure - GEO options 54 Copyright?
After having defined the analytical axis with specific hierarchies and measures, users can analyze data on different levels of detail and from different perspectives through traditional drill-down, drill-across, slice-and-dice, drill-through processes.
Users can filter on characteristics by clicking the filter icon A wizard window appears and customization can be performed on data, without inserting any other row in the cube. Figura — Filters Users can modify the cube in different ways, by simply drag and drop attributes and measures from the top bar into the side bar, and filtering them there.The document shown above is called a Geo document in SpagoBI.
Trademarks: All other trademarks are the property of their respective owners. Thanks to this high level of flexibility. Under project name, enter Dashboard and click on use default location. Inside there, you will find the file engine-config.
Figure As shown in the Figure 87, this information can be retrieved from the QbE graphical interface. Specify the fields to be used as category and measure Selected fields submenu? We need to create a connection to the database from the SpagoBI Studio.