Event banner
Event details
Thanks so much for your post Matthew! Hopefully timing works out and yes we are still on for tomorrow.
Another question for Marco. How much does Premium Capacity 'hide' badly performing reports versus 'shared' capacity?
Good day!
We would like to reduce the refresh time for one of our datasets. We're observing a 90 minute refresh duration using import mode for a dataset containing a fact table with ~250M rows. We're using premium capacity workspace (P1 SKU) with large dataset storage enabled. The data is being sourced from azsql (gen 5, 8 vcores) table with clustered column store index on fact table. Azsql CPU/data IO seems to stay under 20% utilization during the refresh. We've confirmed that a native query is being executed to refresh the fact table. We haven't pursued incremental refresh as the source table is trunc/load on daily frequency.
The fact table itself has fairly simple data types.
CREATE TABLE [dbo].[Fact_Table](
[c1] [int] NOT NULL,
[c2] [int] NOT NULL,
[c3] [date] NOT NULL,
[c4] [date] NOT NULL,
[c5] [varchar](1) NULL,
[c6] [int] NULL,
[c7] [date] NOT NULL,
[c8] [float] NULL,
[c9] [float] NULL,
[c10] [float] NULL,
[c11] [int] NULL,
[c12] [int] NULL,
[c13] [int] NULL,
[c14] [int] NULL,
[c15] [int] NULL,
[c16] [int] NULL,
[c17] [float] NULL,
[c18] [int] NULL,
[c19] [int] NULL,
[c20] [int] NULL,
[c21] [int] NULL,
[c22] [int] NULL,
[c23] [int] NULL,
[c24] [int] NULL,
[c25] [float] NULL
)Do you consider this scenario to be appropriate for import or have we reached a threshold where we should be considering DQ? We experimented some with DQ but encountered sluggish report performance and DAX limitations.
Any advice or troubleshooting suggestions would be greatly appreciated!
Thanks,
MK
Not performance related, but I have a requirement to apply dynamic column grouping to aggregated data and I'm wondering if there's a solution using DAX.
The underlying dataset is a table that contains STORE NUMBER, REGION, VOLUME, SALES, FORECAST.
STORE NUMBER REGION VOLUME SALES FORECAST 1 A HIGH 100 120 2 A LOW 20 20 3 B LOW 30 40 4 C HIGH 120 180 5 C HIGH 130 140 The initial presentation of the data will be as follows. The SALES and FORECAST data will be summed across the entire dataset with a count of total stores.
STORE COUNT SALES FORECAST 5 400 500 The users would then like to be apply dynamic grouping of the aggregated dataset without filtering out any of the underlying dataset. In this example, they'd want the options of grouping by data by (1) REGION, (2) VOLUME, or (3) REGION and VOLUME.
1. Group by REGION
STORE COUNT REGION SALES FORECAST 2 A 120 140 1 B 30 40 2 C 250 320 2. Group by VOLUME
STORE COUNT VOLUME SALES FORECAST 3 HIGH 350 440 2 LOW 50 60 3. Group by both REGION and VOLUME
STORE COUNT REGION VOLUME SALES FORECAST 1 A HIGH 100 120 1 A LOW 20 20 1 B LOW 30 40 2 C HIGH 250 320 Two potential solutions that have been presented for this are (1) bookmarks as buttons and (2) utilizing personalized visuals. Regarding #1, in the actual use-case we're dealing with 8 dynamic categorical columns where any combination could be selected, so creating that many different bookmarks doesn't seem feasible. Regarding #2, personalized visuals using a perspective will create the desired result, but we're concerned about the user experience of configuring the visual (and would also like to lock down the visualization type).
Is there a more graceful way to solve this problem using DAX and a slicer to dynamically choose the columns used to group the data without filtering data?
