Connect to Excel

In this section, we'll show you how to create an MDX dataset in Kyligence Cloud and then use Excel PivotTable to analyze the data.

Prerequisites

1. A computer running Windows operating system.
2. Excel 2013 or later versions.

Create Kyligence MDX dataset

1. In the left navigation, click BI Tools. Click the icon to open the Kyligence MDX trial page.

   

2. On the trial page, click Dataset -> + Create Dataset.

   

3. Enter a name for your MDX dataset and click Next.

   

4. Drag the model from the left-hand model list to the right-hand canvas, and then click Next.

   

5. On the Define Semantics page, you can unify the semantic layer through adding hierarchies and calculated measures, editing dimensions and measures, etc. Or you like to go with the default setting, click Next.

   

6. On the Translation page, you can leave the default and click Next, and then click OK on the Dimension Usage page.

7. Now you can see a new MDX dataset on the Dataset page.

   

Connect to Excel with Kyligence MDX

To connect Excel with Kyligence MDX, you may follow the steps below:

1. On the left navigation pane of Kyligence Cloud, click BI Tools. Click the Excel icon to get the OLAP database server address in the pop up dialog box.

   

2. Open Excel. click Data -> Get Data -> From Database -> From Analysis Services.

   

3. In the pop-up Data Connection Wizard window, enter your server address in the Server name box. Enter your Kyligence account information to sign in, then click Next.

   

4. We have successfully established connection to Kyligence MDX. Select the dataset you want to analyze and click Next.

   

5. Click Save Password in File and Always attempt to use this file to refresh data, and then click Finish.

   [!Note]

   Repeated prompts for credentials and data refresh failures may occur if you do not choose to select the two check boxes.

6. We can now analyze Kyligence MDX dataset with Excel PivotTable. For example, in the screenshot below, we add "YEAR" and "CATEGORY" to Rows, and "Revenue" to Values to check the revenue of certain products of the year.

   

   • For more information on integration with Excel, see Integrate with Excel.
   • This article serves as a tutorial for first-time users. In a real production environment, we recommend installing MDX Gateway for integration with Excel.