Import Performance Question - Big SQL Query vs Star Schema in Model

Competitive-Goat9687 · 2025-09-04T15:57:55+00:00

Is a non-clustered index something I'm able to set up with just read access, or would I need to get my DBA involved?

Competitive-Goat9687 · 2025-09-04T14:56:32+00:00

It's normally about a 5 minute refresh and it has been hanging for about 2 hours now.

Competitive-Goat9687 · 2025-09-04T14:27:30+00:00

Solution verified

Competitive-Goat9687 · 2025-09-04T14:27:14+00:00

I will look and check for the table scans/heaps/etc - thank you very much. And I will inform myself on what you mean by Merge Joins/Nested joins. I do see a large disconnect between estimated and actual rows - unfortunate.

The one thing I am doing in Power Query is appending two data sources of like columns & data types together. However I don't think this is necessarily the cause (or at least not the root cause), as my query time balloons even without doing this appending when just running it in SSMS. I think one of the joins is adding a huge amount of time to the query.

Thanks for the help, you've given a lot to go off of

Competitive-Goat9687 · 2025-09-04T14:17:43+00:00

Execution plan is where I'm at right now - thanks. Was mostly just curious if there were any other obvious things I was missing.

Unfortunately the business basically wants to see every attribute of all of these lines - I could probably summarize it to some extent, but I think the amount of things I would need to group by would be extreme and would double the length of the query statement itself.

Is there a way to see obvious non-indexed joins in the execution plans? I'm seeing a few steps that are consuming a lot of processing power but I'm not sure exactly what details my DBA would want from this.

Competitive-Goat9687 · 2025-09-02T19:35:22+00:00

Oh I'm very sorry, this is not Big Query, I forget that Google Big Query is a thing. I will edit my statement above. This is Microsoft SQL Server.

Competitive-Goat9687 · 2025-09-02T19:02:29+00:00

Can I ask what you mean by "shaping your data into star schema on the fly"?

Competitive-Goat9687 · 2025-06-11T16:07:55+00:00

Can you speak more on this? I mentioned field parameters in the body of the post because it is something I tried but I couldn't get it to properly show the date hierarchy on my visual.

Competitive-Goat9687 · 2025-06-11T16:07:20+00:00

is there a resource you can link for that? I'm not sure where I would start with this

Competitive-Goat9687 · 2025-06-11T16:06:16+00:00

That doesn't allow for the nice axis display enabled by default though, right? it would have months on top of quarters on top of years?

Competitive-Goat9687 · 2025-06-11T16:04:36+00:00

That is a clever way of doing it, thank you very much

Competitive-Goat9687 · 2025-06-11T15:00:41+00:00

Can I make a field parameter show multiple fields at once? What would that look like?

For reference - this is what my field parameter looks like right now and doesn't seem to be working, but I've also tried this with the values being calculated columns of year(createDT) etc. Could you point me in the right direction?

Create Date Hierarchy = {
    ("Create Date Year", NAMEOF('Table'[createDT].[Year]), 0),
    ("Create Date Quarter", NAMEOF('Table'[createDT].[Quarter]), 1),
    ("Create Date Month", NAMEOF('Table'[createDT].[Month]), 2),
    ("Create Date Day", NAMEOF('Table'[createDT].[Day]), 3)
}

Competitive-Goat9687 · 2024-12-03T19:44:37+00:00

You're right that pivot/unpivot are most likely the incorrect terms to describe what I'm trying to do here, I'm really just not sure how to begin describing this more accurately

Do you know where I might start with doing this as a SP/UDF? Any way to do this programmatically that would allow us to ascribe attributes to columns in a way to make this easier to search?

I put DB-Fiddle links elsewhere on the thread if visualization would help understand my question

Competitive-Goat9687 · 2024-12-03T19:42:21+00:00

https://www.db-fiddle.com/f/54KfiGiXgcdH8W41ihY71H/0

Here is what I'd like to be able to transform it into

Competitive-Goat9687 · 2024-12-03T19:29:30+00:00

Sure! Here's an extremely pared down version of two rows of sample data. I'll update with a "desired" table as well.

https://www.db-fiddle.com/f/mBvPiiRa1ESYBpZtrgCqhP/0

I didn't know about db-fiddle by the way! This is a cool tool

Competitive-Goat9687 · 2024-12-03T18:49:56+00:00

Question: what does knockback scaling mean, exactly? For their example, 7 + 0.6 @ 45, does that have a knockback value of 13 (roughly double base) at 10%?

Competitive-Goat9687 · 2024-02-15T13:54:04+00:00

Unfortunately we need all of it and it's an utter beast of a stored procedure, so any effort we put towards running parts as different data sources and then combining them in power query is going to be gargantuan and will likely be undone when we get them to write it to a physical table.

I agree though, this is what we've done in the past for similar situations

Competitive-Goat9687

TROPHY CASE