Add Support for SQL Server Analysis Services (SSAS) Connection with MDX Query Execution, Dimension Management, Pivot Tables, and Domain User Login about superset HOT 3 OPEN

Hey there, @ishirvani! I'm here to help you with any bugs, questions, or contributions you have. Let's tackle this together! 😊

I found a similar unsolved discussion regarding the integration of SQL Server Analysis Services (SSAS) with Apache Superset. You can check it out here: Add Support for SQL Server Analysis Services (SSAS) Connection with MDX Query Execution, Dimension Management, Pivot Tables, and Domain User Login ^[1].

To enhance Apache Superset to support SSAS with features like MDX query execution, dimension and database management, pivot table functionalities, and domain user login, you would need to create a new DB engine spec for SSAS. Here is a high-level approach:

1. Create a new DB engine spec for SSAS:

   • Implement a new module in the superset/db_engine_specs directory, for example, superset/db_engine_specs/ssas.py.
   • Extend the BaseEngineSpec class to handle SSAS-specific functionalities.

2. MDX Query Execution:

   • Override the execute method to support MDX queries.
   • Ensure that the connection string and query execution logic are compatible with SSAS.

3. Dimension and Database Management:

   • Implement methods to fetch metadata about dimensions and databases.
   • Use SSAS-specific APIs or queries to retrieve this information.

4. Pivot Table Functionalities:

   • Extend the visualization capabilities to support pivot tables.
   • This might involve creating custom visualization plugins or extending existing ones to handle the multidimensional data returned by SSAS.

5. Domain User Login:

   • Integrate with the authentication system to support domain user login.
   • This might involve configuring Kerberos or another authentication mechanism supported by SSAS.

Here is a simplified example of what the new DB engine spec might look like:

# superset/db_engine_specs/ssas.py

from superset.db_engine_specs.base import BaseEngineSpec

class SSASEngineSpec(BaseEngineSpec):
    engine = 'ssas'
    engine_name = 'SQL Server Analysis Services'

    @classmethod
    def execute(cls, cursor, query, **kwargs):
        # Implement MDX query execution logic
        cursor.execute(query)
        return cursor.fetchall()

    @classmethod
    def get_dimensions(cls, cursor):
        # Implement logic to fetch dimensions
        cursor.execute("SELECT * FROM $system.DISCOVER_DIMENSIONS")
        return cursor.fetchall()

    @classmethod
    def get_databases(cls, cursor):
        # Implement logic to fetch databases
        cursor.execute("SELECT * FROM $system.DISCOVER_CATALOGS")
        return cursor.fetchall()

    # Add more methods as needed to support other functionalities

6. Register the new DB engine spec:
   • Add the new engine spec to the list of supported engines in Superset.

# superset/config.py

from superset.db_engine_specs.ssas import SSASEngineSpec

DATABASES = {
    'ssas': SSASEngineSpec,
    # other database specs
}

For domain user login, you can configure LDAP authentication in the superset_config.py file:

# When using LDAP Auth, setup the LDAP server
AUTH_LDAP_SERVER = "ldap://ldapserver.new"

This is a simplified example, and the actual implementation would require more detailed handling of SSAS-specific features and integration points ^[2][3][4].

To continue talking to Dosu, mention @dosu.

from superset.

To correctly configure the SSAS connection in Apache Superset for domain user authentication using Kerberos or LDAP, and ensure the connection is fully registered and usable, follow these steps:

1. Install the Required Database Driver

Ensure you have the necessary driver for SSAS. Since SSAS is not explicitly listed, you may need to find a compatible SQLAlchemy dialect and Python driver for SSAS.

2. Configure Kerberos Authentication

In the Secure Extra field of the database connection configuration, add the following JSON configuration for Kerberos authentication:

{
    "auth_method": "kerberos",
    "auth_params": {
        "service_name": "superset",
        "config": "/path/to/krb5.config"
    }
}

Ensure you have the trino[kerberos] or trino[all] package installed:

pip install trino[kerberos]

3. Configure LDAP Authentication

If you prefer LDAP, you will need to configure it similarly in the Secure Extra field. However, specific details for LDAP configuration are not provided in the given context.

4. Register the Connection

After configuring the authentication, register the connection through the Superset web UI:

• Go to Settings -> Data: Database Connections.
• Click the +DATABASE button.
• Fill in the connection details, including the connection string and the Secure Extra field.

5. Display the Entire SSAS Data

Once the connection is successfully tested and saved, Superset should be able to display all databases and dimensions available in SSAS to the authenticated user.

Example Configuration for Kerberos in the Secure Extra Field

{
    "auth_method": "kerberos",
    "auth_params": {
        "service_name": "superset",
        "config": "/path/to/krb5.config"
    }
}

Ensure the connection string and other parameters are correctly set according to the SSAS driver documentation ^[1].

Custom DB Engine Spec Implementation

Your custom DB engine spec for SSAS looks good. Ensure you have implemented the necessary methods and attributes to support metadata fetching and query execution. Here are some key methods and attributes you might need:

Methods:

• fetch_metadata: Fetch metadata from the database.
• execute: Execute SQL queries against the database.
• get_dimensions: Fetch dimensions from SSAS.
• get_databases: Fetch databases from SSAS.

Attributes:

• engine: Specifies the engine type.
• engine_name: Specifies the engine name.

Example Custom DB Engine Spec

from superset.db_engine_specs.base import BaseEngineSpec

class SSASEngineSpec(BaseEngineSpec):
    engine = 'ssas'
    engine_name = 'SQL Server Analysis Services'

    @classmethod
    def execute(cls, cursor, query, **kwargs):
        cursor.execute(query)
        return cursor.fetchall()

    @classmethod
    def get_dimensions(cls, cursor):
        cursor.execute("SELECT * FROM $system.DISCOVER_DIMENSIONS")
        return cursor.fetchall()

    @classmethod
    def get_databases(cls, cursor):
        cursor.execute("SELECT * FROM $system.DISCOVER_CATALOGS")
        return cursor.fetchall()

Register the New Engine Spec

Add the new engine spec to the list of supported engines in Superset:

# superset/config.py

from superset.db_engine_specs.ssas import SSASEngineSpec

DATABASES = {
    'ssas': SSASEngineSpec,
    # other database specs
}

By following these steps, you should be able to configure the SSAS connection in Superset for domain user authentication, register the connection, and display the entire SSAS data to the user upon successful authentication ^[1][2].

To continue talking to Dosu, mention @dosu.

from superset.

Hello,

I am working on adding support for SQL Server Analysis Services (SSAS) to Apache Superset, with the goal of connecting via domain user authentication and ensuring that once connected, the user can view the full set of data, including databases and dimensions.

Here’s what I’ve done so far:

Creating a new DB engine spec: I’ve implemented an initial version of a custom engine spec for SSAS that includes MDX query execution and fetching metadata like dimensions and databases. Here’s the implementation I’ve started with:

python
Copy code
from superset.db_engine_specs.base import BaseEngineSpec

class SSASEngineSpec(BaseEngineSpec):
engine = 'ssas'
engine_name = 'SQL Server Analysis Services'

@classmethod
def execute(cls, cursor, query, **kwargs):
    cursor.execute(query)
    return cursor.fetchall()

@classmethod
def get_dimensions(cls, cursor):
    cursor.execute("SELECT * FROM $system.DISCOVER_DIMENSIONS")
    return cursor.fetchall()

@classmethod
def get_databases(cls, cursor):
    cursor.execute("SELECT * FROM $system.DISCOVER_CATALOGS")
    return cursor.fetchall()

Integrating domain user authentication: My goal is to connect to SSAS using domain user credentials (via Kerberos or LDAP). I want to ensure that once the domain user is authenticated, they can view the entire dataset (databases and dimensions) available in SSAS.

Testing and displaying data: After the successful connection, the user should be able to explore the SSAS databases and dimensions through Superset's interface, including using MDX queries.

I would like some guidance on:

How to correctly configure the SSAS connection in Superset for domain user authentication, ensuring that it works with Kerberos or LDAP.
Best practices or additional steps to fully register and make this SSAS connection selectable and usable for users.
How to ensure that, upon successful authentication, the entire SSAS data (including all databases and dimensions) is properly displayed to the user within Superset.
Any insights or existing implementations around SSAS in Superset, specifically for domain user logins, would be greatly appreciated!

Thank you for your support.

from superset.