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

Accepted Solutions
Highlighted
Microsoft
Microsoft

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

 

View solution in original post

6 REPLIES 6
Highlighted
Microsoft
Microsoft

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.

 

Highlighted
Regular 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

Highlighted
Microsoft
Microsoft

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
Highlighted
Regular 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

Highlighted
Microsoft
Microsoft

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

 

View solution in original post

Highlighted
Regular Visitor

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

Thanks i applied the PowerQuery option and the solution works fine 

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021