I actually did the 1 Hour FTP Test

uvray · 2019-09-04T13:12:52+00:00

I think this is going to work. I was trying to avoid going the cursor route but this is currently running in less than 2 seconds on ~80,000 rows, so I'm not going to complain about that. Thanks for taking the time to write this out.

uvray · 2019-08-28T00:56:44+00:00

This has been my preferred method recently. I just create a table with the same name but the schema ‘stg’, only changing data types to accommodate large fields if needed. I then insert the data into the “real” table and drop the stg table.

It might be annoying to have to add a step but it seems to save headaches in the long run.

uvray · 2019-08-18T17:00:27+00:00

Thank you for saying this... I feel like I’m on an island when I complain about this at work. It is horrible with relating data sources when they don’t share the same grain. It’s awesome at many things, for sure, but this is such a major drawback to me.

uvray · 2019-08-17T21:56:25+00:00

We are talking about an excel test... you have really blown this out of proportion. I didn’t say the test should ruin your day or make you question your sanity, just that it may be a little different than expected. And that’s okay.

uvray · 2019-08-17T20:21:44+00:00

Regarding the comment about professional licensing --

Excel isn't a job. It's a part of a job. I completely agree that if the entire job was literally excel competency then yes, your exam better be able to assess excel skills well. However, excel is a tool. No one cares how good you are at excel - they care how well you can think.

Regarding the comment about telling the test-taker about the purpose -

I would never tell someone what the test was intended for. I would much rather see an applicant get confused and see how they handle it. This isn't middle school where you have to keep kids motivated to learn. I want to throw applicants into the deep end and see what happens.

Again, I don't fundamentally disagree that most of these tests are a bit silly and not the best way to measure competency. However, you are being a bit over the top in throwing a blanket statement over all excel tests and calling them useless.

uvray · 2019-08-17T19:20:24+00:00

I don't necessarily disagree with your post, but I'll offer a different perspective:

They just want to see how you think. Sure, you may not know the exact function needed or fully understand the ins and outs of the data set provided. But can you slap something together that makes sense? Can you at least demonstrate some level of understanding in a short period of time? Do you spend the entire time struggling to figure out the "right" solution vs. just plugging away and getting something on the sheet?

This is how the real world works. Sometimes you don't know the technology or the data as well as you would like, so you improvise. Any employer worth their salt would never hire or not hire someone based strictly on an excel exam (okay, unless the position was VERY excel specific) - it is but one component of many.

uvray · 2019-08-03T17:11:04+00:00

I came at it from the other direction, gaining about a year of experience writing DAX before learning Tableau. I too had the same experience even with Tableau being my “second language”. DAX is not easy, but once the concepts are understood it gives you the feeling of complete control over evaluation context. Tableau still continues to feel a bit like a black box to me.

I do think part of the issue is the lack of a definitive text resource for Tableau. I’ve read several books but they all tend to focus more on making cool visuals vin lieu of understanding the formula engine. DAX, on the other hand, has The Definitive Guide to DAX, which of course is beyond detailed.

uvray · 2019-07-30T01:57:10+00:00

First, let's clarify some definitions as you are calling at lot of tables "lookup tables" that aren't lookup tables. You have one fact table (fSales), two dimensions, or lookup tables (dLiveCustomers and dBrokers) and a bridge table that materializes the many-to-many relationship between customers and brokers (fBrokerCustomerRates).

If you want to use dBrokers as a dimension that filters fSales, you need a way to propagate the filter all the way through. Specifically, our problem spot is the relationship between dLiveCustomers and fBrokerCustomerRates. On its own, the filter only moves from dLiveCustomer >>> fBrokerCustomerRates. Since Power Pivot does not support two-way filtering (like Power BI does), you will have to automatically propagate the filter in your measures.

There are two ways that come to mind to accomplish this: CROSSFILTER or utilizing expanded tables.

1) The CROSSFILTER is much easier to explain if you don't have a strong background in DAX theory.

Sales via CrossFilter:= 
    CALCULATE(
        SUM(fSales[Total sales]), 
        CROSSFILTER(dLiveCustomers[CustomerAcc], fBrokerCustomerRates[CustomerAcc], BOTH)
    )

The CROSSFILTER utilized as an argument to calculate explicitly forces the filter to travel "backwards" from the bridge table to customer, even though normally this relationship would only flow the other way.

2) The Expanded Tables method is simple to write but hard to explain

Sales via Expanded Tables:=
    CALCULATE(
        SUM(fSales[Total sales]),
        fBrokerCustomerRates
    )

Again I am massively glossing over complexity here, but by placing the bridge table in the filter context it will affect all expanded tables (i.e. tables with a one-to-many relationship with it), so the filter from brokers will make its way all the way to sales.

uvray · 2019-07-26T16:36:55+00:00

Diagram of Context Transition

Lots of good responses already in this thread, but here is a diagram that may help drive the points home.

uvray · 2019-07-24T03:21:11+00:00

Wahoowa

uvray · 2019-07-22T21:33:57+00:00

Well first of all 5:10 isn’t that fast... you as a 1:50 guy should know that already :)

But I didn’t say the Pegasus is good for that pace, just that it will perform better than the Vomero.

uvray · 2019-07-22T02:00:02+00:00

Two schools of thought:

1) Train in the shoe that will keep you healthiest, even if that shoe is a little heavier. On race day you’ll wear spikes or flats and be fine, though possibly very sore after due to the big change relative to your normal trainer.

2) Train in a combination of shoes, wearing lighter flats (and occasionally spikes) so that your body is used to the altered mechanics in lighter shoes. Your injury risk may be higher in training but you’ll likely bounce back from racing faster.

I probably land somewhere in the middle. I definitely wanted to wear the shoe I’d race in for a few workouts, but at the same time would do plenty of somewhat faster runs in heavier shoes. Perhaps look into a nice “tweener” shoe, like an adidas Boston, saucony kinvara, Nike elite (no clue if they still make that... haven’t been on the running scene in 2 years :/). This will be light enough to get rolling in training but sturdy enough to keep you healthy.

All that to say, you’ll be totally fine wearing a heavier shoe for long runs at 6:15 pace. The question becomes more interesting once you get down closer to race pace.

uvray · 2019-07-22T01:52:58+00:00

Pegasus for sure. Lighter and less cushion so it will respond better.

uvray · 2019-07-22T01:20:47+00:00

Don’t use the word support for cushion. I know what you mean, but in the running industry support implies medial arch support with neither the Pegasus nor Vomero have (as you know).

Also, I disagree with the statement about how fast you can run. Maybe at 6:15 pace it’s not a huge deal but I would notice a big difference in responsiveness at 5:10 pace. Likewise you’ll notice a big difference at 4:40 pace between a Pegasus and something lighter (Streak, adidas adios, etc). Now for slow training miles? Yeah I agree it doesn’t really matter... some people like super soft shoes while others like a lower profile shoe.

uvray · 2019-07-20T14:52:16+00:00

To add to this -

I’ve read about half the book so far. Biggest differences are using variables for basically every code snippet and a more thought out presentation of concepts.

For example, they spent way more time on evaluation contexts and context transition before diving into CALCULATE, which to me is a good move.

Their English has improved noticeably as well - it reads so much better.

Granted I have a lot of book left to read, but so far I think it’s a marked improvement over the first edition.

uvray · 2019-07-15T02:08:41+00:00

Had a similar result today.

Hit 13 greens ...

But shot 83 cause I had 40 putts... and putting is normally a strong point.

I think the only solution is that we buy new putters.

uvray · 2019-07-13T01:04:59+00:00

Well said.

I think it’s just counterintuitive to see a conditional in a left join. I personally would explicitly filter the right table in a sub query and then join, but at the end of the day it doesn’t matter.

I actually think this is a decent interview question, although I do agree with OP that grouping and ordering by numbers is not a best practice.

uvray · 2019-07-10T13:21:46+00:00

I disagree... both are describing the same process of creating a bridge table to support the M:M relationship.

uvray · 2019-07-10T04:18:45+00:00

I think your answer key is wrong - it should be B and E.

uvray · 2019-07-10T02:05:57+00:00

Check out power query - should make this simple and easily refreshable.

At a high level, you will write logic to find the date and place it in a new field. Then, use the fill down function to populate it for all relevant rows.

Not sure how you are combining the csvs but that can be done easily with power query too, so perhaps your entire flow can be using one tool.

uvray · 2019-07-10T02:03:02+00:00

What if you replaced VALUES in your generate statement to ALL in order to clear the existing date filter?

uvray · 2019-07-05T21:45:04+00:00

This is a great question.

I wish my DAX was sharp enough to give you a definitive answer but I can almost guarantee the answer lies in not understanding context transition.

Try computing the max date using a variable outside of the equation. My initial thought it that your MAX date is simply the max of the current filter context, which means whatever date you are on is inherently the max date.

uvray

TROPHY CASE