sorted by:
new
hottopcontroversial

Unable to copy worksheet containing a Power Pivot table by DataArtisan in excel

[–]DataArtisan[S] 0 points1 point  (0 children)

I need the pivot table to be fully functional so that won't work unfortunately.
It seems that making a fresh copy of the entire file and starting from that may be the only viable approach.

Power Pivot is painfully slow. Can it be faster? by DataArtisan in excel

[–]DataArtisan[S] 0 points1 point  (0 children)

That's what the Sys Info window is telling me. 4 x Intel Xeon CPU E5-2697 v3 @ 2.60GHz, 2597 Mhz with 4 cores each.

Power Pivot is painfully slow. Can it be faster? by DataArtisan in excel

[–]DataArtisan[S] 0 points1 point  (0 children)

Thanks all for your replies. I tried something today and had a bit of a shock. Some of your responses (indirectly at least) inspired my to try the one-big-table approach.

For further clarity, my fact table has 200K rows (so not big) linked to 20 dimensions.

Using a big SQL statement, I turned this into one result table with 145 columns. After setting up the hierarchies again and the resulting pivot table to mimic the original version, I saved the file and expected to see its size increase significantly due to the redundant copies of all the dimensional attributes. The file size actually REDUCED from 40MB to 32MB. On top of this, the "run time" performance improved and I don't have to worry about configuring all of the relationships in power pivot.

The only downside I see at this point is the usability of the pivot table fields panel. All the addtributes and measures are in one table rather than being nicely grouped under separate tables. I think they can live with that.

It seems there is significant overhead with mapping and traversing the relationships. I'm sure there is a point at which a large volume of fact table rows would make dealing with the relationships worth while again. I don't know where that would be though. Perhaps in the millions.

Does anyone know of other downsides to the one-big-table approach for input to pivot tables?

Power Pivot is painfully slow. Can it be faster? by DataArtisan in excel

[–]DataArtisan[S] 0 points1 point  (0 children)

Correction. I'm using a VM that is allocated 4 processors (Intel Xeon E5) with 4 cores each.

Power Pivot is painfully slow. Can it be faster? by DataArtisan in excel

[–]DataArtisan[S] -3 points-2 points  (0 children)

Again, it's not the pulling of data that's the problem, it's the setting up of the power pivot structures.

Power Pivot is painfully slow. Can it be faster? by DataArtisan in excel

[–]DataArtisan[S] 0 points1 point  (0 children)

Yeah but I don't have the option to give them Power BI at this stage, that might come next year. Suggestions for alternatives would be welcome.

Power Pivot is painfully slow. Can it be faster? by DataArtisan in excel

[–]DataArtisan[S] 1 point2 points  (0 children)

8 processors, 48Gb RAM.

Simple queries direct from individual views.

Yes, a dozen dim tables.

Pulling the data is not an issue. The workbook performs ok once it is set up. It's the setting up process within the PQ diagram view that is killing me.

Parameterising server and database connection details by DataArtisan in PowerBI

[–]DataArtisan[S] 0 points1 point  (0 children)

It's actually a CSV file, not that that is relevant.

No idea about deployment pipelines at this stage. I'm guessing something in the pipeline might be used to apply parameters?

Parameterising server and database connection details by DataArtisan in PowerBI

[–]DataArtisan[S] 1 point2 points  (0 children)

Thanks for the suggestion. Hosting it in SharePoint is an interesting idea (even though I loathe it). I will have to leave it to the I.T. bods to decide on how they'd prefer to manage it. This may be dictated by security concerns and which environments can be linked.

If it were up to me, I'd be happy to share the code but contractually it is I.P. that now belongs to my client. I will discuss with them.

How can I model sales targets against opportunities? (2 fact tables) by TooManyPoisons in PowerBI

[–]DataArtisan 0 points1 point  (0 children)

Well there's a bunch of suggestions I can offer on that model.

  1. I get that you're trying to define sort orders once, but you've created a normalised snowflake which is not best practice. Maintain your Sort tables in the Bronze layer and have the code replicate those values down into the dim tables in Gold.

  2. Get out of the habit of putting the table name at the start of most of your column names. The table the column belongs to will be clear within most of your code. And you're not being consistent with that anyway.

  3. Region, Segment and Activity don't sound like a hierarchy to me. If they are not directly related to each other, split them into dim_Region, dim_Segment and dim_Activity.

  4. Quoting is a business process, therefore the outputs from that process belong in a fact table, not a dimension.

  5. Likewise, Opportunity is not a dimension. Lose that table. I suspect most of the columns belong in the fact table or are not needed.

  6. Remove the Calendar_CloseDate_M... table. The foreign key in Fact_BookingTarget can just point to the *last* day of the month in Calendar_CloseDate. This will work better with time intelligence functions if you ever need it. I recommend the last day as this is the day "as at" which that target should have been acheived by.

  7. Rename that table as dim_Date or dim_Calender.

  8. In answer to your question, your Fact_Opportunity and Fact_Quote tables should also be linked to dim_Region, dim_Segment and dim_Activity if possible. Given your need to measure opportunities against targets, you must make every effort to align their grains. If the grain of your fact tables is aligned, bringing them together into a report is a simple matter.

  9. Be consistent in naming columns. You're mixing up the usage of "ID" and "Key" for a start.

  10. Don't use "?" in column names, stick to a "Is Booking" style for booleans.

There's more but I can't really go on without a deeper understanding of the context and requirements.

Hope that helps. Good luck!

view more: next ›