Forum Discussion

ozmike's avatar
ozmike
Icon for Resolver I rankResolver I
9 years ago
Solved

Filtering/Joining/Merging A Large DB table against an XLS file

Hi

 

 I have a table with 9 million rows in a readonly database. I get supplied a list of record say 12000 keys in an XLS which I want to filter against. I can't use direct query as I'm joining XLS to DB? So of when I filter down (join /merge) to the 12000 using power query it brings back all 9 million rows when applying changes. 

 

So any ideas with the desktop on how to speed things up..I'm using the desktop power bi and HANA database, I only need 12000 records out of the dataset.

Need Help
  • ozmike's avatar
    ozmike
    9 years ago

    Hi here is a turn key solution ( excuse the pun)  example joining the backend to an XLS, This will avoid the default action of pulling the whole table down. It uses SAP hana but i'm sure it could be used for other dbs. The key column in this example is a text field so needs single quotes here and there. Assume 'query' is an item that you can connect to normally in power bi.  Enjoy

     

    let
    Source2 = Excel.Workbook(File.Contents("\\yourpath\KeyList.xlsx"), null, true),
    Sheet1_Sheet = Source2{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers1" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    Source1 = #"Promoted Headers1",
    KeyList =    Text.Combine(Source1[KEYfield],"','")  ,  // column to single line of text 
    sel2 = "select * from ""ZXXX1.ZXXX_REP.bp::Query"" where ""KeyField"" IN  ('" & KeyList  & "')  ",
    Source = SapHana.Database("server:portnumber", [Query=sel2])
in
   Source

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi ozmike,

     

    You can refer to below steps:

     

    1. Write a function to parameterize connect to hana database.

    2. Get data from excel file, then analysis key list and format them to string.

    3. Write t-sql query with above keys string.

    4. Invoke the custom function with t-sql query as parameter.

     

    Sample function:

     

    let 
    loadData=(ServerName as text,source as text, table as text, Query as text) =>
let
    Source = SapHana.Database(ServerName ,Query ),
    Contents = Source{[Name="Contents"]}[Data]{[Name=source]}[Data]{[Name=table]}[Data],
    #"Added Items" = Cube.Transform(Contents, {{Cube.AddAndExpandDimensionColumn, "STLNR", {"STLNR"}, {"Bill of material"}}})
in
    #"Added Items"
in
    loadData

     

    Use: last parameter is the t-sql query.

     

    let
    Source = loadData("xxxxxxx", "xxxxx", "xxxxx", T-sqlquery)
in
    Source

     

     

     

    Notice:

    Before these steps you should input the sap hana certificate to power bi.

     

    Regards,

    Xiaoxin Sheng

    • ozmike's avatar
      ozmike
      Icon for Resolver I rankResolver I
      9 years ago

      Hi Thanks

       

      Its a little over my head ..you might have to break your original into 20 steps!

       

      1. Write a function to parameterize connect to hana database.

      Where do write a function ? I assume in query editor.?

       

      loadData=(ServerName as text,source as text, table as text, Query as text) =>

       

      Servername = "myhanaserver:12345"

      Source= "foldername" ( folder icons in the connect to data source)

      table="cubename" ( cube icon - in data source)

      query ? - you mean tsql parameter

      also what is  "Bill of material" and  "STLNR" in my world

          #"Added Items" = Cube.Transform(Contents, {{Cube.AddAndExpandDimensionColumn, "STLNR", {"STLNR"}, {"Bill of material"}}})

      "Bill of material" = cubename, anything else I need to change or is his a column name? 

      "STLNR" - ?

       

      2. Get data from excel file, then analysis key list and format them to string.

      Open a data source to an excel file and format key to text?

       

      3. Write t-sql query with above keys string.

      I want to join the list of keys? to the cube to filter rows? what t-sql ? coudl you give an example ;) I know SQL.

       

      4. Invoke the custom function with t-sql query as parameter.

      You mean set in the advanced editor the query's definition as calling the function.

       

      5. before these steps you should input the sap hana certificate to power bi.

      I have a server name , user and password - whats a hana certificate?

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi ozmike,

         

        >>Where do write a function ? I assume in query editor.?

        Yes, at query editor, you can right click to creata a blank query and write these formula.

         

        >>Open a data source to an excel file and format key to text?

        get data from excel, deal with excel file and output the key list.

         

        >>I want to join the list of keys? to the cube to filter rows? what t-sql ? Smiley Wink I know SQL.

        You can use above key string as the parameter of the sql query.

        E.g. "Select * from xxxx where key in ("&keylist&")"

         

        >>You mean set in the advanced editor the query's definition as calling the function.

        Invoke the method which I mentioned, open the advanced editor to input the parameters.

         

        Notice: query is the sql query which added the key list.

         

        >>I have a server name , user and password - what a hana certificate?

        It means you need to sign in your database, after this operation the certificate will be stored in power bi.

         

        You can find it at datasource -> global permissions.

         

        Regards,

        Xiaoxin Sheng