Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Solved! Go to Solution.
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
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
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?
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 ? 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
Hi
Ok
1) Create a query of the EXCEL spreadsheet with just one column the KEY column only
call this query KEYLISTQUERY and remove duplicates.
2) Then create another query. in the adavance query editor paste this.
let #"Changed Type" = Table.TransformColumnTypes(#"KEYLISTQUERY",{{"KEY", type text}}), Custom1 = "'" & Text.Combine(#"Changed Type"[KEY],"','") & "'" in Custom1
The output of this is something like this
'key1','key2','key3' - (This video here helped me create comma separated list.)
We are going to use this text in the dynamic where clause later. This assumes you have a text column number columns and dates will be different, of course.
3. Right mouse on the this new query and select create a function called KEYListfunction
with no parameters. to run this function press the invoke button after clicking on the function. This function output the query 'key1','key2','key3'.
4. Finally create a another query . This now has the dynamic where clause. Which will push down the query to the server. This is what we want to do the join to the XLS in the hana server not in power bi pc client.
let xx = KEYListfunction(), sel = "select * from ""ZXXX.XXX::mytable"" where ""KEYCOL"" IN (" & xx & ") ", Source = SapHana.Database("myhanaserver:12345", [Query=sel]) in Source
Finally is you get a weird Firewall error see this post here
Have tested with a XLS key list of 12000 against a HANA TABLE 9 million row. Once you have the data its a lot quicker as you don't download the 9 million!
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
I know this is a very old post but hoping someone has an answer. Has anyone been able to get this to work once published to the Power BI service and refreshing through a gateway? I seem to get the privacy errors no matter what I set the levels to in the gateway data sources.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
116 | |
98 | |
87 | |
35 | |
35 |
User | Count |
---|---|
152 | |
98 | |
81 | |
61 | |
55 |