Forum Discussion

pbi_user_rdg's avatar
pbi_user_rdg
Regular Visitor
4 years ago
Solved

SAP ODBC.DataSource SupportsDerivedTable

I need to configure ODBC connection to store data in DirectQuery mode in PBI. I have two problems:

 

1. Per MS documentation, SupportsDerivedTable option can be used in ODBC.DataSource in M-Query to make the query a DirectQuery mode.

Here's my M-Query:

let
Source= Odbc.DataSource("dsn=localhost SQL Server", [SupportsDerivedTable = true])
in
Source

 

I get this error:

Expression.Error: The provided options are not valid.
Details:
[List]

 

2. Where do I enter my SQL statement in ODBC.DataSource in M-Query? The current statement (without SupportsDerivedTable) gives a list of SQL objects. 

Thank you!

Data Protection
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi pbi_user_rdg ,

     

    According to the official document—— The supported options records fields fall into two categories—

    1. Are public and always available. The record can contain the following fields:

    HierarchicalNavigation 

    CreateNavigationProperties 

    ConnectionTimeout 

    CommandTimeout 

    SqlCompatibleWindowsAuth 

    So for example:

     

    Source = Odbc.DataSource("dsn=Test", [ConnectionTimeout=#duration(0,0,10,0)])

     

     

    2. Are only available in an extensibility context.

     

    Since SupportsDerivedTable option is not public ,it is used in Overriding SqlCapabilities, you could not directly use it.

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi pbi_user_rdg ,

     

    According to the official document—— The supported options records fields fall into two categories—

    1. Are public and always available. The record can contain the following fields:

    HierarchicalNavigation 

    CreateNavigationProperties 

    ConnectionTimeout 

    CommandTimeout 

    SqlCompatibleWindowsAuth 

    So for example:

     

    Source = Odbc.DataSource("dsn=Test", [ConnectionTimeout=#duration(0,0,10,0)])

     

     

    2. Are only available in an extensibility context.

     

    Since SupportsDerivedTable option is not public ,it is used in Overriding SqlCapabilities, you could not directly use it.

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • pbi_user_rdg's avatar
    pbi_user_rdg
    Regular Visitor
    4 years ago

    Thank you Anonymous! 
    How can one use the options available only as extensibility context?
    Here's my M-query that does not store the table in DirectQuery mode:

     

    let
    Source =
    Odbc.DataSource(
    "dsn=localhost SQL Server",
    [HierarchicalNavigation=true, SqlCapabilities=[SupportsDerivedTable = true]
    ]
    ),
    #"DW_Database" =
    Source{
    [Name="dbname",Kind="Database"]
    }[Data],
    dbo_Schema =
    #"DW_Database"{
    [Name="dbo",Kind="Schema"]
    }[Data],
    DimDate_Table =
    dbo_Schema{[Name="DimDate",Kind="Table"]}[Data]
    in
    DimDate_Table