Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello All,
I am currently building a dashboard using Azure SQL as the DB and using Direct Query mode for visualization.
The DB stores Web Analytics information.
The count of records for some of the facts have ~10million and upwards, the column list though is not much maximum metrics and dimensions being 20.
Is there a way i can perform the following operations to improve the performance of the dashboard.
1. Define a default Date parameter to restrict Web Analytics information being retrieved when the dashboard loads.
2. Override this default parameter with a user selection ? The Date Filter is chiclet slicer visualization.
Thanks for any help and feedback
Solved! Go to Solution.
Try to filter in SQL
let Source = Sql.Database("ericvm2", "testdb", [Query="select S1_DATE, Users from F_googleSessionInformation where s1_date>'"&Date.ToText(defaultDate,"yyyyMMdd")&"'"]) in Source
Or in Power Query
let Source = Sql.Database("ericvm2", "testdb", [Query="select S1_DATE, Users from F_googleSessionInformation"]), filtered = Table.SelectRows(Source, each([S1_Date] > defaultDate )) in Source
@BharatRedz711 wrote:
Hello All,
I am currently building a dashboard using Azure SQL as the DB and using Direct Query mode for visualization.
The DB stores Web Analytics information.
The count of records for some of the facts have ~10million and upwards, the column list though is not much maximum metrics and dimensions being 20.
Is there a way i can perform the following operations to improve the performance of the dashboard.
1. Define a default Date parameter to restrict Web Analytics information being retrieved when the dashboard loads.
2. Override this default parameter with a user selection ? The Date Filter is chiclet slicer visualization.
Thanks for any help and feedback
1.Yes, you can do this by specifying a specfic query with where clause to retrive data, apply the date after where.
2.No Override, the slicer would send a query which is filtered based on the query of Option 1.
Thanks for reaching out and replying.
I was trying point 1, applying the condition in the Let Clause and i am getting the following error
"this database function does not support the query option", can you point me in the direction of some link material or sample query for this
Query that i updated in Advanced Editor
let
Source = Sql.Databases("server",
[Query="SELECT [S1_DATE]#(lf) ,[S1_MONTH]#(lf) ,[Keyword]#(lf) ,[Users]#(lf) ,[New Users]#(lf) ,[New Sessions Percent]#(lf) ,[Sessions]#(lf) ,[Bounce Rate]#(lf) ,[Session Duration]#(lf) ,[Unique Pageviews]#(lf) ,[Cancelled Sessions]#(lf)
FROM [DWA].[F_googleSessionInformation]"]),
database = Source{[Name="Instance"]}[Data],
#"DWA_F_googleSessionInformation" = Database{[Schema="DWA",Item="F_googleSessionInformation"]}[Data],
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"New User Sessions"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"New Users", "New Users"}}),
#"Renamed Columns3" = Table.SelectRows(Source, each([S1_Date] > " & defaultDate & " ))
in
#"Renamed Columns2"
S1_Date is the date column and defaultDate is my parameter variable
Try to filter in SQL
let Source = Sql.Database("ericvm2", "testdb", [Query="select S1_DATE, Users from F_googleSessionInformation where s1_date>'"&Date.ToText(defaultDate,"yyyyMMdd")&"'"]) in Source
Or in Power Query
let Source = Sql.Database("ericvm2", "testdb", [Query="select S1_DATE, Users from F_googleSessionInformation"]), filtered = Table.SelectRows(Source, each([S1_Date] > defaultDate )) in Source
Thanks i applied the PowerQuery option and the solution works fine
Could you post your power query. I don't mention any thing specific but pure SQL in point 1, say some SQL like
SELECT column1,column2 FROM table WHERE col1=xxx
User | Count |
---|---|
15 | |
2 | |
1 | |
1 | |
1 |
User | Count |
---|---|
17 | |
11 | |
5 | |
4 | |
3 |