Please find attached my presentation. I look forward to your comments, again I couldn’t register for the Live Solution session in the link you sent as it appears to have broken. Can you please re-send the registration link
I did something a bit different and this was to rename all the columns after I had imported the source CSV file. I realised that all the columns starts with the text “Superstore Flat File”, so i had to find a way to rename all the columns in a one-go. I did this by reading about how Matt Allington solved a similar issue at https://exceleratorbi.com.au/renaming-all-the-columns-in-a-table-at-once-using-power-query-m-language/
I created six dimensions and one fact table from the given source file. The DIMs are as follows;
Customers
Segment
Ship Mode
Address
Dates
Products
and had ClientTransactionData as the Fact Table
On the Date table, I used the CALENDARAUTO function to start with and created some relevant columns with the table like YEAR,QUARTER, WEEK DAY, MONTH etc
That’s really strange – I just tried the link myself and it’s working fine for me. I’m looping in the @EnterpriseDNA team to check this out, and I would also ask you to please send a brief message and your screenshot to rebecca.walker@enterprisedna.co -she will make sure you get registered.
Thanks for letting me know. Is anyone else having the same problem?
Very nice job! Kudos for stretching yourself and incorporating the dynamic start and end dates. Certainly every situation is different, but in most of the data I work with the start date of the fact table doesn’t change over time, so you can buy some speed by hardwiring the first of the year corresponding to the earliest fact table entry into the date table and just making the end date dynamic.
The snowflake structure you used for your customer dimension is by no means wrong, but does have some pros and cons that we will discuss in the live session.
Exceptional job on this! Really impressed by the thorough job you’ve done in the data review prior to the modeling, and the careful thought given to how to resolve the problems identified.
Also, great job in following the wide range of best data modeling practices.
Nice work! Again, I love the way people are challenging themselves relative to their own skill sets, in your case incorporating the custom M code for renaming columns.
My one strong suggestion here would be to use the EDNA Extended Date Table, rather than the CALENDARAUTO generated date table. When we get to time intelligence in a few weeks, I think you will find that the former offers some incredible power (particularly relative to the offset fields) that will greatly simplify your DAX.
Were you able to get your registration straightened out? If not, let me know and I will make sure we get you into the session.
Thanks for participating! Nice job on your measures.
In terms of your data model, what you’ve done isn’t technically wrong but I think it’s far from optimal. In de-normalizing your dimension tables to that extreme, I think you’ve made your model far more complex than it needs to be, and also created some problems in terms of syncing slicers, while gaining only marginal file size efficiency. We will talk a lot about this sort of trade-off at the live session tomorrow.
A HUGE thanks to all who participated this week. Closing in on 700 forum views for this week’s problem, double-digit submissions and some great discussions on the forum thread.
Thanks also for those who joined us today for the live solution event. @sam.mckay and I really enjoyed it, and hopefully you found it beneficial. For those who weren’t able to attend, we’ve got the recording of today’s event, a copy of the Powerpoint slides, the original problem with data file, and both @sam.mckay 's and my PBIX solution files up on the portal now at this location under Power BI Accelerator:
We covered a lot of ground today. Some of the topics we addressed were:
Why do we do data modeling at all?
The Four Horseman of the Data Modeling Apocalypse (and how to defeat them)
Identifying fact and dimension tables
What are star schemas and snowflake schemas, and the pros and cons of each?
Why do star schemas work so well in Power BI?
The Art and Science components of data modeling
And a detailed walkthrough of the specific Week #2 solution.
We’ll be back next Wednesday 7/28 with the Week #3 problem, which will be focused on exploring how CALCULATE can be used to alter filter context to produce powerful analyses.
Thanks very much for the feedback. I really enjoyed it as well, and there were some great questions from the participants. @sam.mckay and I are still experimenting with the best platform for these events, but if folks feel most comfortable asking questions via chat, we may switch back to Re-Stream which allows Sam to flash the questions asked at the bottom of the screen.
It was great last night. Thanks for providing insights within the problem.
I would agree if the question could be shown at the bottom of the screen so if someone was just coming into the session. They would know what was being asked.
The solution session was very informative. I especially appreciated the review of the variants (alternatives) proposed by the participants. Thank you @BrianJ and @sam.mckay for your time and for sharing your knowledge and thank you to everyone who participated.
Saw this on Twitter this morning with the tagline “Every Customer…#PowerBI” and it made me laugh. Highly relevant to our solution discussion last Wednesday…
. Stick with that and you can’t go wrong.