and Excel Spreadsheets
In this tutorial, you will get a complete overview of data analysis and dashboard creation with Bime using Excel Spreadsheets. It's a lot easier than you might think.
This tutorial is designed to last 20 minutes.
We used a made-up dataset for this tutorial. If you would like to use the same dataset, you can download it here: MyShoes.xls
First of all, create an Excel connection by choosing 'Excel File' from the datastore drop-down menu. Choose which Excel file you want to use, and choose the sheet.
The connection builder will automatically pick up the headings from your spreadsheet and put them into the schema, either as measures or attributes. You can then create new dimensions or attributes if you wish, and move things around to form hierarchies. By default, Bime will take up the whole sheet, but if you have a title on it or blank space around the active data, you can set the 'start cell' for the data connection with the 'offset' function. The start cell needs to include the header row with the necessary column titles. The Excel File must contain a raw data table and not a formatted report.
* Beware having empty lines or empty columns in your spreadsheets - this can cause Bime to 'stop reading' at the point of the break, so data capture will not be complete.
* Each cell in the specified range is taken up and allocated individually, which means that column headings over two lines are to be avoided.
* Fill in blank cells and missing headers.
* Remove text that is not part of the main data table.
* Remove totals and sub-totals.
* Merged cells are also to be avoided as these can prevent the underlying data from being correctly picked up. A merged-cell header over two (or more) columns will be assumed to relate only to the left hand column. The data in the other column(s) covered, and any other columns containing data but with no header, will be picked up, but will be allocated a random heading that may make it difficult to find the right data set in a complex data source.
Bime is really easy to use: you just have to drag and drop dimensions and measures into the pivot table.
Now choose the data you want to analyze: drag your chosen measure into the measure box and your chosen dimension into the column axis. Your data will automatically be generated in a grid format.
Bime provides different kind of visualizations such as heatmaps, pie charts, treemaps, and so on!
After generating your visualization you can use some post-processing features. For instance, you can try the "TOP" function which retrieves the first x number of results. We can now use post-processing filters (kind of masks) to only display what we are really interested in.
You can also decompose your data for a specific dimension.
Thanks to Bime, you can create your own calculated attributes and measures and edit the display format for each measure which provides you with infinite possibilities.
Create calculated attributes in a few steps and re-use these attributes when ever you need them. Let's have a look at a quick example. Suppose you want to know the number of products which contain a specific text. For instance, products containing the text "sp".
Create calculated measures in a few steps and re-use these measures when ever you need them. Creating a calculated measure is exactly the same as creating a calculated attribute - just click on the arrow next to a measure and repeat the steps above.
Whenever you want to analyze different dimensions in a complex way, the Treemap could become your best friend. You can instantly see the best elements and also use the hierarchy option to sort the results (the first measure determines the box size and the second one the color). That way, it really makes sense.
You've successfully completed this tutorial! You should now have a solid foundation in how to use Excel spreadsheets in Bime. There are still many more features you can experiment with, so why not take a look at our complete online documentation for further hints and tips!