Quick Start Guide: How to Build a Cube in SaasabiPro
There are two approaches to building multidimensional cubes in SaasabiPro. You can build them programmatically via our open API toolkit or you can use our Microsoft Excel-based Management Console. If you would like to use our APIs, contact us at support@saasabi.com to request documentation and set up a training session for your team. This Quick Start Guide will walk you through the process of building cubes using the SaasabiPro Management Console.
The SaasabiPro Console manages every process involved in defining the cube structure, creating the cube and then populating it. We take a data-driven approach to cube creation and management. The console enables you to pull in data from nearly any source (database or API); select which data to extract; create extraction scripts; and create data integration and data manipulation routines including application-specific functions. The Management Console also provides you with options for defining and managing the frequency of cube-update scripts. All cube modification and management tasks are run in the background without disrupting continuing operations; the cube is refreshed once all changes and loads are successfully completed.
Step 1: Access the Saasabi Management Console
Open Microsoft Excel and select the Saasabi tab in the ribbon menu.
Step 2: Connect to Saasabi
Using the “Connect to Saasabi” icon in the Saasabi menu, open the log-in pop-up window.
Step 3: Access the Cloud
Click on “Saasabi on Microsoft Azure”.
Step 4: Sign in to Saasabi
Enter the email address that you have associated with your Saasabi account. If you don’t have a Saasabi account, please send an email to support@saasabi.com to request one.
Step 5: Authenticate Your Identity
Authenticate your identity using the credentials selected by your Saasabi administrator. The example below uses your Microsoft ID as the authentication method for Saasabi.
Step 6: Grant Access
After clicking the “Sign in” button, you will be shown another pop-up window asking you to grant permission for Saasabi to access your information. Click the “Yes” button to continue.
Step 7: Add New Model
To build a new model, click on the “Add New Model” button in the Instance Management section of the Saasabi add-in.
Step 8: Add a Datasource
An “Editing Model: New Model” pop-up window will appear giving you options to create, remove or modify datasources. Click on the “Create” button under the “Datasources” heading.
Step 9: Select a Datasource
A pop-up box will appear which enables you to select a data source type and enter your credentials. Select your data connector from the list of options on the left, and the required fields for the connector will appear on the right (for example, to connect to a SQL Server you will need to enter the server name, database, user name and password). Provide the required information, and click the “OK” button. This will connect Saasabi to the data source, and the corresponding data set will appear under the “Datasources and Tables” section.
Step 10: Add New Table to Model
To add a table to the model, select one from the datasource list and click the “Include” button. This will add that table and bring up a table configuration window.
Modify the table name, if desired, and set the primary key from the drop-down menu. A primary key is a special relational database table column (or combination of columns) designated to uniquely identify all table records. Its primary function is to provide a unique value for each row of data. A primary key can be either an existing table column or a column that is specifically generated to a defined sequence.
Point consolidation enables you to make data refreshes more efficient by adding only new data (incremental refresh) rather than importing the full data set (total refresh) every time. In order to utilise point consolidation, you must indicate what criteria will be used to identify new data moving forward. Select the appropriate data column and comparison type (e.g. time, date, number, etc.) to use for point consolidation.
The last section in the table configuration window enables you to control how you would like to add the table to your model. You will not have to make any selections in this section if this is the first table you are adding to the model, and this table will serve as the primary table for the model. If you are including an additional table in the model, you will use this section to join or merge tables.
To join or merge tables, select the appropriate tab. If you have selected “Join Table and Include in Model”, use the drop-down menus to indicate which column you would like to join with the columns/tables that are already in the model.
If you have selected “Merge Table Data into Existing Table”, select the table you would like to merge with from the “Table to Merge with” drop-down menu in the “Column Mappings” section. SaasabiPro will display the column mappings, which have either been set automatically or are waiting to be set.
Within the “Column Mappings” section, choose a column from the table you are adding to the model. Then in the column to the right, select a column from the model to merge the table with. In the third and final column, choose the type of treatment for the data you’ll be merging (“Map to Existing”, “Ignore” or “Add as New”).
The “Map to Existing” option will merge the new table’s data into the specified table and column. The “Ignore” option won’t import the data in the column identified but will leave them out. The “Add as New” option will add a new entry into the specified column of that table.
Once all of your table configuration settings have been chosen, click “OK” to save the changes to the model.
Step 11: Data Transformation
Transform the data in your model by adding filters, removing unwanted columns and creating measures.
To add and remove columns use the “Include Columns” and “Exclude Column” buttons. To remove a column, select a column by clicking in a field within that column (but not the header field), then click the “Exclude Column” button. To include columns, click the “Include Columns” button, and a pop-up window will appear showing the names of the columns that have previously been removed. Simply drag and drop the column name back into the table to add it back to the model.
To add a filter, click on the “Apply Filter” button, and a "Filter Editor" window will appear. The "Filter Editor" window will list all of the columns within the table. You can add a filter to a particular column by clicking on the blue link in the window. Select the column you want to filter, then click on the green link. This will bring up a list of filter options (equals, does not equal, greater than, etc.). Select one, and then enter a value (either a name or numeric value) in the last field. If you would like to add another filter to the table, click on the plus mark next to the red “And” link at the top of the window. You can add as many filters as you would like. Once you are finished, click the “OK” button and your changes to the model will be saved.
To adjust the column settings of a table, click on the “Column Settings” button. A “Configure your Column” window will appear.
Rename the column by entering a new name in the “Column Name” field. Please note that this new column name will not write back to the data source. It renames the column within your Saasabi model. This will be the name that is seen by Saasabi users.
You can define how Saasabi will treat the column (date, time or categorisation). In the example below, the column has been treated as a categorisation and has a time period type of month/year.
You can also deploy complex calculations within the "Configure your Column" window for a greater degree of flexibility and customisation. Select the calculation language you’d like to use and enter the appropriate string of code. The calculation options are quite flexible and enable you to make web calls out to retrieve information such as real-time stock prices, currency conversion rates, etc. Once you have finalised the column settings, click the “OK” button and SaasabiPro will save these settings to the model.
The last step in building a model is creating measures. To do this, click the "Create Measure" button near the top of the "Editing Model" window. If you want to edit an existing measure, click the "Modify Measure" button. To delete a measure, simply click the "Remove Measure" button.
When you click on the "Create Measure" button, a “Configure your Measure” window will appear (this window looks similar to the "Column Settings" window shown above). First enter a name for the measure in the “Measure Name” field. Then set the “Summary Type” from the drop-down menu to either Sum, Average, Count, Minimum, Maximum, Countdistinct or Calculated. In the example below, we are creating a measure on the “Number of Visits” column. We make it a whole number by setting the “Format Type” to “Whole Number”. You can also set the precision of the number (in decimal places).
You can add more complex calculations by using the “Calculated” summary type. For example, you might want to create a measure that joins two columns together, such as a month column and a year column. You can write a string of code in C# that will merge the data in the month column with the data in the year column. The “Calculated” option provides a high degree of flexibility. It gives you the ability to make web calls out to retrieve real-time information such as a stock prices or currency conversion rates.
Once you have created your measure click the “OK” button, and the changes to your model will be saved.
Step 12: Add Additional Datasources
To connect to another datasource you will need to repeat Steps 8 and 9. In the Saasabi ribbon, select the instance and model to which you would like to add an additional datasource. Then click the “Edit Current Model” button.
When the “Datasources” window appears, click on the “Create” button. A pop-up box will appear that allows you to select a data source type and enter the relevant credentials as per Step 9.
Step 13: Edit Model Settings
To define the model settings, such as the name and data refresh period, click on the “Edit Current Model” button, and in the bottom left-hand corner of this window click on the “Modify Settings” button. When you click on this button a green side panel will appear on the right-hand side. Enter a name for the model in the first field and a description of the model (this is recommended but not a mandatory step). If you would like the data in your model to auto update, then tick the "Auto Update?" check box and select a frequency option from the drop-down menus such as ‘DailyAt’ and ‘09:00’. This set of selections will refresh the data daily at 9am. Once you have finished defining your model settings, click the “Save Settings” button.
If you have any questions about building cubes using SaasabiPro, email our support team at support@saasabi.com.