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

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

BharatRedz711 — Mon, 06 Mar 2017 10:52:59 GMT

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

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

Eric_Zhang — Tue, 07 Mar 2017 02:57:53 GMT

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

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

BharatRedz711 — Tue, 07 Mar 2017 08:53:04 GMT

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

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

Eric_Zhang — Tue, 07 Mar 2017 08:57:53 GMT

@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

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

BharatRedz711 — Tue, 07 Mar 2017 08:58:35 GMT

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

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

Eric_Zhang — Tue, 07 Mar 2017 09:20:11 GMT

@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

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

BharatRedz711 — Tue, 07 Mar 2017 09:36:40 GMT

Thanks i applied the PowerQuery option and the solution works fine