Event banner
Event details
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?