Dashboard building using SQL Server for Data Upload - Slow Reports

[rmalhan12]

Hi, I am using the SQL Server Management Studio to upload reports to use in Power BI through the SQL Server. One months data is roughly 44 columns by 250,000 rows (file size is 110mb as a Microsoft Excel Comma Separated Values File). In the dashboard I have created I am trying to analyse two years worth of data therefore 24 of the above files data in one source (roughly 6 million rows worth of data by 44 columns). Is this the reason my dashboard is taking a long time to update when I'm trying to analyse or am I missing something? 

[rmalhan12]

When I open up the query this is what it shows - 

// DAX Query

DEFINE

  VAR __DS0Core =

    VALUES('AUMReport'[Include])

  VAR __DS0PrimaryWindowed =

    TOPN(101, __DS0Core, 'AUMReport'[Include], 1)

EVALUATE

  __DS0PrimaryWindowed

ORDER BY

  'AUMReport'[Include]

// Direct Query

SELECT

TOP (101) [t0].[Include]

FROM

(

(

select [$Table].[RowID] as [RowID],

    [$Table].[ReportDate] as [ReportDate],

    [$Table].[Owner1_CampaignType] as [Owner1_CampaignType],

    [$Table].[Owner1_CreationDate] as [Owner1_CreationDate],

    [$Table].[PlanAdviceType] as [PlanAdviceType],

    [$Table].[Owner1_FirstName] as [Owner1_FirstName],

    [$Table].[Owner1_LastName] as [Owner1_LastName],

    [$Table].[Owner1_FullName] as [Owner1_FullName],

    [$Table].[SellingAdviser_FullName] as [SellingAdviser_FullName],

    [$Table].[Owner1_ServicingAdviser_FullName] as [Owner1_ServicingAdviser_FullName],

    [$Table].[IsTopUp] as [IsTopUp],

    [$Table].[ProductName] as [ProductName],

    [$Table].[PlanStatus] as [PlanStatus],

    [$Table].[PlanStatusDate] as [PlanStatusDate],

    [$Table].[PolicyNumber] as [PolicyNumber],

    [$Table].[TotalLumpSum] as [TotalLumpSum],

    [$Table].[TotalRegularPremium] as [TotalRegularPremium],

    [$Table].[CurrentValuation] as [CurrentValuation],

    [$Table].[CurrentValuationDate] as [CurrentValuationDate],

    [$Table].[CurrentValuationType] as [CurrentValuationType],

    [$Table].[Owner1_AddressLine1] as [Owner1_AddressLine1],

    [$Table].[Owner1_AddressLine2] as [Owner1_AddressLine2],

    [$Table].[Owner1_AddressLine3] as [Owner1_AddressLine3],

    [$Table].[Owner1_AddressLine4] as [Owner1_AddressLine4],

    [$Table].[Owner1_County] as [Owner1_County],

    [$Table].[Owner1_PostCode] as [Owner1_PostCode],

    [$Table].[Owner1_DateofBirth] as [Owner1_DateofBirth],

    [$Table].[Owner1_ClientServiceStatus] as [Owner1_ClientServiceStatus],

    [$Table].[Owner1_ClientServiceStatusStartDate] as [Owner1_ClientServiceStatusStartDate],

    [$Table].[Owner1_Id] as [Owner1_Id],

    [$Table].[CommissionTotalReceived] as [CommissionTotalReceived],

    [$Table].[Owner1_HomeTelephoneNumber] as [Owner1_HomeTelephoneNumber],

    [$Table].[Owner1_WorkTelephoneNumber] as [Owner1_WorkTelephoneNumber],

    [$Table].[Owner1_MobileNumber] as [Owner1_MobileNumber],

    [$Table].[Owner1_EmailAddress] as [Owner1_EmailAddress],

    [$Table].[Owner1_CampaignSource] as [Owner1_CampaignSource],

    [$Table].[SellingAdviser_Group_Name] as [SellingAdviser_Group_Name],

    [$Table].[Provider_Name] as [Provider_Name],

    [$Table].[PlanType] as [PlanType],

    [$Table].[Include] as [Include],

    [$Table].[Channel] as [Channel],

    [$Table].[Groupings] as [Groupings],

    [$Table].[ChannelAdjusted] as [ChannelAdjusted],

    [$Table].[ProviderCategory] as [ProviderCategory],

    [$Table].[AgencyStatus] as [AgencyStatus],

    [$Table].[Id] as [Id]

from [dbo].[AUMReport] as [$Table]

)

)

 AS [t0]

GROUP BY [t0].[Include]

ORDER BY [t0].[Include]

ASC

[parry2k]

@rmalhan12 6m rows are a very small dataset, I guess you are importing the data into the power bi instead of using DQ? What does the model look like? Using any DAX measure, if yes, what those are?

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[rmalhan12]

Using the direct query function on the SQL server. All I have done is load a single table and selected a slicer to exclude some numbers from the figures but when I run the performance analyzer I get the following results (I haven't even updated any DAX code) -