Uploaded image for project: 'Solution Center'
  1. Solution Center
  2. SOL-272

Microsoft Analysis Services (SSAS) Connectivity

    XMLWordPrintable

    Details

    • Type: How To
    • Status: Obsolete
    • Affects Version/s: EXASolution 5.0.10, EXASOL 6.0.0, Exasol 6.1.0
    • Fix Version/s: None
    • Component/s: ADO.NET
    • Labels:
      None
    • Environment:
    • Solution:
      Hide

      Note: This solution is no longer maintained. Please find the latest information in our documentation portal:

      https://docs.exasol.com/connect_exasol/bi_tools/ms_sql_server_analysis.htm

      -----------------------------------------------------------------------------------------------------------------------------------

      Add Data Source

      0. Install the EXASolution Data Provider (part of the EXASOL ADO.NET-Package) on both your design environment and your deployment environment. Restart Analysis Services after installation.

      You must install the ADO.NET driver as Administrator (open a Windows-Terminal and execute msiexec /i EXASolution_ADO.NET-<Version>.msi )

        If you want to use the Data Provider with Visual Studio 2017 you need Version 6.1.x and you must additionally install an Extension for Visual Studio:  In your Windows Program Menu go to the EXASOL folder and click on "Install SSAS 2017 Extension for Visual Studio 2017"

      1. Choose "New Data Source" in your Multidimensional Project

       

      2. Select the "EXASolution Data Provider" in the drop down list

       

      3. In the Connection Manager, define the Server (Connection String) of your EXASolution database and provide the user credentials,
      Use the "Test Connection"-Button to check if your connection works.

       

      4. For Impersonation Information choose "Inherit"

       

      Add Data Source View

      1. Choose "New Data Source View" in your Multidimensional Project

       

      2. Select the EXASolution Data Source

       

      3. Select all tables and views you need for your Multidimensional Project

       

      4. Now you can start to build your cubes on top of the relational tables in EXASolution

       

      Best practices

      ROLAP mode for fact tables, MOLAP mode for dimensions

      Configure your fact partitions as pure ROLAP to get a performant solution with a low latency:

      For best performance keep the default MOLAP setting for your dimensions. With these settings the deployment of the cube is very fast:

       

      Set the date format in EXASolution

      If you use date attributes in your hierarchies, please ensure that SSAS has the same date format setting as EXASolution.
      To set the date format in EXASolution use the ADO.Net Connection String parameter "onConnect" to set the date format according
      to the SSAS setting, e.g.:

      alter session set NLS_DATE_FORMAT='MM/DD/YYYY'
      
      Show
      Note: This solution is no longer maintained. Please find the latest information in our documentation portal: https://docs.exasol.com/connect_exasol/bi_tools/ms_sql_server_analysis.htm ----------------------------------------------------------------------------------------------------------------------------------- Add Data Source 0. Install the EXASolution Data Provider (part of the EXASOL ADO.NET-Package) on both your design environment and your deployment environment. Restart Analysis Services after installation. You must install the ADO.NET driver as Administrator (open a Windows-Terminal and execute msiexec /i EXASolution_ADO.NET-<Version>.msi )   If you want to use the Data Provider with Visual Studio 2017 you need Version 6.1.x and you must additionally install an Extension for Visual Studio:  In your Windows Program Menu go to the EXASOL folder and click on "Install SSAS 2017 Extension for Visual Studio 2017" 1. Choose "New Data Source" in your Multidimensional Project   2. Select the "EXASolution Data Provider" in the drop down list   3. In the Connection Manager, define the Server (Connection String) of your EXASolution database and provide the user credentials, Use the "Test Connection"-Button to check if your connection works.   4. For Impersonation Information choose "Inherit"   Add Data Source View 1. Choose "New Data Source View" in your Multidimensional Project   2. Select the EXASolution Data Source   3. Select all tables and views you need for your Multidimensional Project   4. Now you can start to build your cubes on top of the relational tables in EXASolution   Best practices ROLAP mode for fact tables, MOLAP mode for dimensions Configure your fact partitions as pure ROLAP to get a performant solution with a low latency: For best performance keep the default MOLAP setting for your dimensions. With these settings the deployment of the cube is very fast:   Set the date format in EXASolution If you use date attributes in your hierarchies, please ensure that SSAS has the same date format setting as EXASolution. To set the date format in EXASolution use the ADO.Net Connection String parameter "onConnect" to set the date format according to the SSAS setting, e.g.: alter session set NLS_DATE_FORMAT= 'MM/DD/YYYY'
    • Category 1:
      Clients, Interfaces & Drivers
    • Category 2:
      3rd Party Tools

      Attachments

        Issue Links

        1. Data_Source_1.PNG
          107 kB
        2. Data_Source_2.PNG
          45 kB
        3. Data_Source_3.PNG
          21 kB
        4. Data_Source_4.PNG
          19 kB
        5. DSV_1.PNG
          92 kB
        6. DSV_2.PNG
          18 kB
        7. DSV_3.PNG
          30 kB
        8. DSV_4.PNG
          45 kB
        9. Fast_Deployment.PNG
          26 kB
        10. Storage_Mode_ROLAP.PNG
          15 kB

          Activity

            People

            • Assignee:
              CaptainEXA Captain EXASOL
              Reporter:
              CaptainEXA Captain EXASOL
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: