Incremental Querying of Relational Data

Posted by: trail-hacker on 3 August 2017, 3:55 pm EST

  • Posted 3 August 2017, 3:55 pm EST

    Is it possible to add a flag or some other approach that would allow me to offer an incremental data extraction from a relational query?

    To my knowledge, when the Analysis control is connected to a relational database, it loads the full query results into memory and then manages the data internally. This is fine for small data sets, but when connected to a large query, the control comes to a stand still on the first drag operation.

    This is what I would like to see. Mind you that this is a very simply scenario:

    Assume that I have 3 attributes and 2 measures:



    Attributes:

    ClassCode

    Family

    Article

    Measures:

    Cost

    Price

    If I drag ClassCode to the Column shelf, I would like for the data source to issue the following SQL:

    'select ClassCode from Table groupby ClassCode'

    Then I drag Family to Column it would issue another query:
    'select
    ClassCode, Family from Table groupby ClassCode, Family'


    Then I drag Cost to the Row shelf:
    'select ClassCode, Family, sum(Cost)
    from Table groupby ClassCode, Family'
    I know that when connected to an MDX data source, this happens, but I'm not sure why it doesn't (or couldn't) do this for an OleDb data source.

    Has there been any talk of doing this?

    Thank you in advance!
  • Replied 3 August 2017, 3:55 pm EST

    I agree but I want it taken a step farther. Only get data for child dimensions when the parent is expanded.



  • Replied 3 August 2017, 3:55 pm EST

    I agree. I'm looking for full support of this as well. Other complex scenarios might include attribute filters, measure filters, named sets, kpi's, and paging through data. Expanding and collapsing would be a mixture of filtering as far as I can tell.

    Good note!
  • Replied 3 August 2017, 3:55 pm EST

    Hello,

    Your observations are correct, however it is not possible to change them since this is how DDA is designed.
    In case of Relational Datasource, when the user drags and drops an attribute or measure to the grid, the query is fired and executed to gather the data. Hence the first drag operation takes time.
    On the other hand, while connecting with MDX datasource,
    intelligent MDX queries are fired to retrieve only the
    information that is needed to render the requested view.


    We recommend large data sources to use SSAS to offload the processing.

    Regards,
    Aashish
  • Replied 3 August 2017, 3:55 pm EST

    Aashish,

    Thanks for the response. I can accept that this is as design. Please ensure that my email address is attached to a suggestion for future consideration.
     
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels