Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BharatRedz711
Regular Visitor

Azure SQL Direct Query Method: Default Filter to restrict Retrieved Data

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 ACCEPTED SOLUTION

@BharatRedz711

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

 

View solution in original post

6 REPLIES 6
Eric_Zhang
Employee
Employee


@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

@BharatRedz711

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 

@BharatRedz711

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.