cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BharatRedz711 Frequent Visitor
Frequent 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

Accepted Solutions
Moderator Eric_Zhang
Moderator

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

@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

 

6 REPLIES 6
Moderator Eric_Zhang
Moderator

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


@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.

 

BharatRedz711 Frequent Visitor
Frequent Visitor

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

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

Moderator Eric_Zhang
Moderator

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

@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
BharatRedz711 Frequent Visitor
Frequent Visitor

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

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

Moderator Eric_Zhang
Moderator

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

@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

 

BharatRedz711 Frequent Visitor
Frequent Visitor

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

Thanks i applied the PowerQuery option and the solution works fine