Excel on Kyvos

Applies to: Kyvos Enterprise  Kyvos Cloud (SaaS on AWS) Kyvos AWS Marketplace

Kyvos Azure Marketplace   Kyvos GCP Marketplace Kyvos Single Node Installation (Kyvos SNI)

Microsoft Excel has become one of the most popular data analysis tools due to its availability on most office machines. Kyvos can integrate with Excel for your reporting needs, using the MSAS connector.

To retrieve the Kyvos semantic model data in MS Excel, perform the following steps.

1. Go to Data > Get Data > Select From Database > From Analysis Services on your Excel worksheet.

2. On the displayed Data Connection Wizard, provide the connection information.

   Open

   

   • URL/Server: This is the server URL. Enter as http://<ServerName>:<port>/<kyvos>/xmlaKyvos
     Here,
     <ServerName> is the IP address or DSN name of your Kyvos server.
     <Port> is the port number on which Kyvos listens.
     <kyvos> is the web app name.

   • User/Password: Use your Kyvos username and password for authentication.

   • Windows authentication: The connection URL for windows auth must be like
     http://<ServerName>:<port>/<kyvos>/xmlaKyvosSSO
     For Windows authentication, you must configure the details mentioned at SSO Configuration for Windows Authentication

3. Click Next.

4. Choose the semantic model from the drop-down list and select Connect to a specific semantic model or table option. This loads a list of the available semantic models.

   Open

   

5. Select the semantic model that you want to browse and click Next.

6. On the Save Data Connection File and Finish screen, check the information and click Finish.

   Open

   

7. From the Import Data dialog box, select how you want to view data in your workbook.

   Open

   

8. Click OK.

The Kyvos semantic model is loaded in your worksheet. All your business logic is abstracted and presented in the form of measures and filters. You can now start browsing the semantic model. This example shows profit against parts.

Open

Migration of Excel from SSAS to Kyvos

Important Points to know

• To migrate Excel from SSAS to Kyvos, you must have Kyvos server version R1027.

• The semantic model’s name, folder, structure, and column properties (such as key or display field and so on) in Kyvos must exactly match the SSAS semantic model for seamless migration. If the field differs between Kyvos and SSAS semantic models, migrating an Excel worksheet from SSAS to Kyvos will not yield expected results.

• When migrating from SSAS to Kyvos, the slicer will not display due to default behavior.

• Best practices while migrating Excel Sheet from SSAS to Kyvos:

  • Recommended Best Practice: As a best practice, we recommend that users save the sheet once after migration and then proceed with any additional modifications or operations on the migrated sheet.

  • Grand Totals Behavior: In SSAS, certain measures may not display Grand Totals by default. However, after migration to Kyvos, Grand Totals might appear automatically due to differences in aggregation handling between the two platforms.

  • Multiple Key Fields in Dimensions: SSAS allows the use of multiple key fields for certain dimensions. Kyvos, however, does not support multiple key fields for a dimension level. This difference may lead to discrepancies in data representation after migration.

  • Member Properties Configuration: If member properties are applied to fields in SSAS but are not configured or supported in Kyvos, the migrated sheets may not behave exactly as they did in the source environment.

  • Sorting Behavior in Time Hierarchies: In SSAS, time hierarchies are often sorted alphabetically by default. After migration to Kyvos, they are typically sorted chronologically, which can result in a different order of members in the hierarchy:

  • Sorting Differences Due to Special Characters: When converting a sheet to a Formula Report, differences in sorting may occur if special or accented characters are present. This happens because SSAS and Kyvos use different collation and sorting rules for accented characters, which can lead to variations in the displayed member order.

To migrate an Excel Sheet from SSAS to Kyvos, perform the following steps.

1. Open the Excel sheet that needs to be migrated.

2. To create Kyvos connection, see the information mentioned above in this section.

3. Navigate to Data in the top ribbon > Properties > Definition.

   Open

   

4. Click Browse and select the Kyvos connection created for migration.

   Open

   

5. Click Open to choose the selected Kyvos connection.

6. You will be redirected back to the Properties section, where the Connection String will reflect the selected Kyvos connection or target folder.

   Open

   

7. Review the connection details to ensure the correct Kyvos environment and semantic model are selected.

8. Click Ok to confirm the selection. A dialogue will appear; select Yes to proceed with migration.

   Open

   

9. Once the process completes, the Excel sheet will be successfully migrated to the selected Kyvos connection.

   Open