Here you'll see the features of our Excel add-in. With the add-in, you can make operations within Excel.
On this guide, we'll show you how to download and install the add-in, extract the data and execute basic operations (insert, update, delete).
Everything on this guide is made with Windows 10 and Excel 365 (version 1812, build 11126.20266).
Close your Excel before installing the add-in. Maintaining Excel opened will result in errors in the installation.
Step 1: Download the Excel add-in.
Step 2: Execute the installation wizard. You'll be presented to the following screen:
The SlicingDice add-in for Excel is provided by CData Software, a leading provider of data access and connectivity solutions.
Step 2.1: Click on
Next >. Now you'll be presented to the following screen:
Step 2.2: Read the license agreement and them, if you agree, click on
I Agree button to continue your installation process.
Step 2.3: You'll then be presented to a product registration screen, like the following:
Fulfill the text fields and then click on
Next > button.
You need to fulfill the text fields
Step 2.4: You'll be presented to an install location screen, like the following:
Choose the destination folder and then click on the
Next > Button.
Step 2.5: It will be showed to you a component checklist, like the following image:
Keep all components checked and click on the
Next > button.
Step 2.6: The following screen will then be showed to you:
Here, you can name your Start Menu folder. When you're satisfied, click on the
Next > button.
Step 2.7: You'll be directed to an install information screen, like the following image:
Click on the
Install button. The installation will now proceed:
When the installation is finished, you'll receive a success screen:
Also, the help manual will be opened in your default browser.
If you want to access the help manual, you can do this by going to the Start Menu, clicking on the CData Excel Add-In folder and then clicking on Help.
You can also access the online help manual here
Below, we'll show you some operations that you can execute using this Add-In.
For the purpose of this tutorial, we'll be using the Alcohol Consumption dataset.
On this section, our example dataset will be inserted on SlicingDice. To make this insertion you will need to add a data source and create a loading job:
On the control panel, click on Data Loading & Sync -> Data Sources. Create a CSV (HTTP) source with the following URL:
Click on Loading Job and create a loading job accordingly. In this example, the whole dataset will be inserted at the
defaultdimension and our mandatory entity-id will be the "country" column. Table/View name is "drinks".
Check the Loading Job section if you need any help.
Step 1: Open some spreadsheet
After you start Excel, you'll need to open something. This is needed for you to access the plugin.
In this example, we'll click on the "Blank Workbook" item, like the image below.
Step 2: Open the CData Add-In
Notice that you have a new tab called "CDATA".
Click on the "CDATA" tab and go to the next step.
Step 3: Create a connection
On the "CDATA" tab, in the "Get External Data" category, click on "From SlicingDice" option.
It'll show a screen like the following:
For this tutorial, we'll fulfill only two spaces:
Connection Name, which will store the data of connection;
API Keys, which in this case will be the Master Key from your database. You need to have a database already created
Supported API Keys
Although in this tutorial we used only the database Master Key, the following keys can be useful as well:
- Custom Key (read-only);
- Custom Key (read-write).
You can also insert several API keys to work with multiple databases. You can do it by separating each API Key with comma (,).
In this example, take a look at how your connection will look like:
Click on "Test Connection" button. If everything is alright, you'll receive the following message:
Close the message and click on "OK" at the Connection Wizard.
Step 4: Importing your data
After the conclusion of Step 3, you'll see a Data Selection screen like the following image:
On this image, you'll see more information about what connection you're using, the selected dimension and the columns of the dimensions.
You can also see the SQL query that will be used, the limit of rows, the name of the sheet that will be created, if the data will auto refresh and from what row the data will start.
For this example, we'll only modify the "Max Rows" to 200 Rows, to receive all the rows on the database.
Click on "Ok" and wait for the data download.
In this example, our data will look like the following image:
score column is an automatic column that is created in every SlicingDice SELECT query. The
score column shows how many times each query condition matched each result, adding incrementally for each matched condition. It's useful when you have several "OR" conditions in the query.
In this case, the "score" column isn't useful, so it can be deleted.
We'll add a new row, containing the following information:
You can add this information directly from Excel.
In this example, we'll add this information to Excel row 191, as the following image shows.
Notice that the words and numbers at row 191 are in red. This is because they are not in SlicingDice yet.
To upload the row to SlicingDice, you'll need to select the row (already selected in the previous image) and click on "Insert Rows", on "Actions" category.
If everything goes right, the color of the font will become black.
Error while inserting
Sometimes the add-in will issue an error saying that you need to refresh the workbook. If this error happens, deselect the entire row and select manually the cells of the last column.
Now we'll modify the data of "afghanistan". The original dataset has every column filled with zero.
We'll add some different values, as shown below.
See that all modified values are in red? This is because SlicingDice doesn't have this value yet.
Select the row and click on "Update Rows", at "Actions" category.
You'll see the font color automatically go black.
Now we'll delete the data that contains "somaliland" as entity-id.
Select the rows that contain the data, like the image below.
In the "Actions" category, click on "Delete Rows". Confirm your action, and you'll see the selected row be erased from your table.
The add-in can also revert rows. If you modified some rows and want to rollback your changes, select the row(s) and click on "Revert Rows", on the "Actions" category.
This action will only work with the data colored in red.
Once you upload the data to SlicingDice, you can not retrieve the former values