Introduction

This tutorial will guide you through the quick process of connecting and importing an Excel spreadsheet file into Sisense Prism.

Before you start

Download US Laptop Sales.xls

Jump To:

  1. Step 1: Select Connection Origin
  2. Step 2: Select & Connect to an Excel File
  3. Step 3: Excel connection settings
  4. Step 4: Finish Wizard
  5. Step 5 (optional): Edit Dimensions


Step 1: Select Connection Origin

The first step is to Create new ElastiCube


Step 2: Select & Connect to an Excel File

To create an Excel data source, we must first select an Excel file.
To do that, go to Add Data in the toolbar and then click on the Excel File item.

This in turn will open up a standard windows open dialog
in which we will select the desired Excel file.

For our tutorial, select the US Laptop Sales.xls file
which you downloaded at the beginning of the tutorial

Note:

click here if you haven't downloaded the US Laptop Sales.xls file.

Step 3: Excel Connection Settings

In this step we will select the actual data ranges from within the
spreadsheet. We can choose between a particular sheet, a named
range
or a static range.

In our example, we will keep all default settings (particular sheet) and
continue by pressing the OK button


Note:

The preview section at the bottom of the dialog gives you an intro tothe data you are about to import. Each column has a special icon near it that shows how Prism will treat the imported column. In our example, we have three types of columns: text, date-time,and numeric.


Tip:

In order for Prism to recognize values properly, make sure you select the appropriate culture for the given Excel file. For example, if the file was generated in a non-English language, then you should switch to that language by selecting it from the Culture drop-down list.

Step 4: Finish Wizard

The last step is to build and sync the newly generated ElastiCube


Note:

by default, when importing Excel spreadsheets, Prism treats textual columns as regular dimensions, numeric columns as measures, and time columns as date-time dimensions.


Tip:

Later on, after the wizard is done and the data source is generated, you can still change the data source configuration (visible columns, aggregation types, default captions etc.) by right clicking on the Data Source Browser, and selecting the Data Source Settings menu item.

 

Step 5: (optional): Dimensions Properties

The Dimensions Properties dialog involves the selection of fields
(columns) and setting them as dimensions , measures , and time
dimensions.

Fields:

Fields that contain qualitative data such as Dates, Customer Names, Gender, Products, Regions etc.

Time Fields:

Date & Time stamp fields that are transformed into time hierarchies.

Set a field as time dimension if you wish to analyze by time as years, quarters, months, days etc., and perform calculations that involve time hierarchies (Growth, Difference, Rolling Sums etc.)

In addition, once you set a field as time dimension, you will get additional time aggregation dimensions that allow you to analyze data by Weekdays and Month Names, a powerful addition that gives you the ability to combine time dimensions on the same widget.


Numeric Fields (Measures):

Fields that contain numerical values that should be aggregated such as sales, cost, profit, # of products sold, ship duration, scientific measurements, statistical measurements etc. Set a field as measure if you wish to aggregate it over other dimensions.