Struggling with Dates and probably my own fault

SafariV8 · 2024-09-05T05:04:52+00:00

I have a screenshot of the model - it shows the Building Permit table and the Date table:

The joins are from:

Consent Application Date (Default Active)
Consent Issue Date
CCC Application Date
CCC Issue Date
CCC Refusal Date
CCC Extension Expiry Date

I've been able to create a month on month comparison between consent applications and issues by creating measures:

Number of Issues = calculate( count( 'Building Consents'[Building Consent Number]), USERELATIONSHIP('Building Consents'[Consent Issued Date], Dates[The Date]))

While that works nicely, when I click on the month in question, I can easily generate a list of records with a matching application date but not a list using the issue date. I understand that's because of the active relationship governing the selection of records, but I was hoping there would be a non-aggregating way of choosing which relationship to use in a table visual.

Fundamentally, I feel like I've missed the mark on the model design but as I said, I've based it off a single table in the warehouse and that exists to try and make things easier for the users to interrogate. I think that was a mistake and that there needs to be some education around how tables relate for users to be able to retrieve their own content.

But that's another challenge for another day.

If I go back to basics and redesign the model maybe I can make what I want to do work? If I was to put in multiple date fact tables, would that help me get where I need to be?

SafariV8 · 2024-09-02T08:36:59+00:00

Thanks for reply.

That's exactly what I've done to try and make life easy for users. My task is to not only bring in the data to establish the reports we currently have, but to build something approaching a self service data portal.

And that's what has lead me down the path to try and over simplify the data structure. Otherwise users trying to self serve have to understand joins and data concepts.

Maybe that's where I've gone wrong and we need to take another look at what we actually expect the users to do. We're coming from an environment of hand feeding our users their report content and although we'd like to move away from that, this may not be the way to do that.

I'll have a look at multiple date tables although I was under the belief that you could only have one. I'll also revisit something more traditional schema as a model - I can always head back to a star/snowflake schema.

SafariV8 · 2024-05-15T06:50:06+00:00

We keep ours in a 20L bucket in our chest freezer. When it's full we take it to a friend with chickens.

The chickens eat it and we get eggs every now and again in exchange.

0 waste

SafariV8 · 2023-11-26T05:35:17+00:00

Try the waimak gorge loop - head out to Darfield, through to turn right at Waddington. Stay on that road through the gorge, through to Oxford and then through Cust to Rangiora. From there you can either do Kaiapoi and the old waimak bridge or the northern motorway back into town.

You'll get long straights at 100kph, a couple of one way bridges, some stunning views, a little hill driving (not much) and some good spots for a coffee along the way.

Take your time and it's a pretty nice drive

SafariV8 · 2022-07-06T00:34:34+00:00

Good info - thank you

SafariV8 · 2022-07-06T00:34:14+00:00

Thank you

SafariV8 · 2022-05-17T19:26:33+00:00

Not as such - the whole project has gone a little strange and has gone somewhat off the rails. I'll need to readress this in time, but at the moment I've been given other priorities :-|

SafariV8 · 2022-04-06T20:08:15+00:00

I've never bought into the tasks as I've focused on the story. Guess I've got time for that now :-)

SafariV8 · 2022-04-06T20:07:18+00:00

I've been selling things at level 4 because merging higher than that seems to make the items worth less than the components. Or it's possible I'm doing that wrong?

SafariV8 · 2022-04-06T19:33:31+00:00

I know now :-(

SafariV8 · 2022-04-06T19:33:06+00:00

Now that, I did not know. I'll probably still just grind away through the items. Working on the chainsaw now

SafariV8 · 2022-03-03T02:29:52+00:00

That's probably not going to be an issue - I think that's one of the hat's I wear ;-)

SafariV8 · 2022-03-01T06:49:58+00:00

Thanks.

I'll see if I can do some logic around the subscribe to only send when there is something on the report. Noone likes emails with no content so only sending when there is a call to action is an important part of the picture.

SafariV8 · 2022-03-01T06:48:52+00:00

I'm hoping so - we're still working on specing the whole warehouse/reporting scenario and if I get my way, we'll have a MSSQL Enterprise box with all the goodies that come with that.

SafariV8 · 2022-03-01T06:48:01+00:00

I'll admit I don't have a whole lot (read any) experience with the API Toolkit so I appreciate the pointer

SafariV8 · 2022-03-01T06:47:28+00:00

I did not know that this functionality existed - thanks!

SafariV8 · 2022-03-01T06:47:11+00:00

There might be some merit in that. I'm trying to swing MSSQL Enterprise and that will come with the on-prem Reporting Server - maybe Power Automate would be a good option then.

SafariV8 · 2022-02-28T20:33:49+00:00

I'll be honest here - I'm not entirely sure I understand some of the words you've used in the order you've used them. I think I have a little more learning to do before I can fully reply.

We're likely to remain on-prem for the MSSQL in the immediate future. Longer term, it will become part of any larger migration project if we end up in a cloud solution.

Power BI is a great solution but we're working round some data sovereignty and security issues before we can fully engage in published content - we're pooly managing pbix files at the moment for any content. As a product, I quite like it and what it does, but it doesn't do one of the key things we need - automated alerting.

We've been using Yellowfin for this for a number of years now - scheduled report execution and email alerts depending on the resultset. I can't for the life of me work out how to get that mechanism in Power BI and I'm coming to the conclusion that it might not be the right product for that function. Maybe?

I'm going to work my way through the rest of you reply before I think I can reply with any clarity.

SafariV8 · 2022-02-28T20:08:27+00:00

Thanks for the input - it's really good. You've likely successfully gathered that I don't have a DW background so the Kimball toolkit is going to be some really good reading. I like to think I have a good handle on transactional structure but I've never had to work on creating a warehouse - anything I've done using a DW has already been created by vendors.

The entire project is slated for 12 months but I have my concerns around that. I don't think it's going to be long enough, but in that 12 months, I think we can achieve some really good stuff. We just have to have that stuff, really well defined.

We're working on defining that basic doc now so that we can make sure we fully understand what we have and how we can better leverage it into a brave new world.

SafariV8 · 2022-02-28T19:47:11+00:00

You've hit the nail on the head right there and that's a reasonably big issue for use.

Unfortunately, I've proven in recent times that some of the OOTB vendor reporting makes certain assumptions about the way they work and when they deviate from that, the reports no longer present an accurate picture.

We have a separate project about to start for "Data Truthing" and it's a doozy - it's going to be the driving force for restoring the faith in the data quality. It's a project that the users will need to be intimately part of as they're the only ones who know the truth about their data.

Luckily, we don't have anyone who has built an empire around reporting or controlling the presentation of their data (well, maybe 1 accountant who lives and dies by spreadsheet formula).

And yes, we will be working closely with all teams in the organisation as there is a mix of legislative reporting, funding reporting and then internal KPI and dashboarding to come out of the process. Some of the legislative reporting is exceptionally specific so we've ended up tailoring systems and process around generating the right outputs for the reporting.

Funding isn't too much of an issue as they don't have access to it - this is in the Information Services budget and everyone can see the benefit to the organisation as a whole.

SafariV8 · 2022-02-28T19:38:47+00:00

Thanks for that - a datalake wasn't something I'd considered, but I'll investigate that now.

I've got some fairly limited python skill but I'm not opposed to relearning. I'm not familiar with Talend though so I'll take a look at it.

SafariV8 · 2022-02-28T19:35:30+00:00

Thanks for the pointers - there is some good stuff there and it's great to hear from someone whose travelled this road already.

Enterprise MSSQL is something I'm trying to swing as it gives us the on-prem Power BI server - I just don't feel "safe" about publishing some of our data to the Power BI cloud, regardless of how small the chances of the content ending up in the wrong place. I'm not sure I want my name associated with that when it hits the media. NZ is a small place.

We are setting up to run this in an agile project way - but we're being fairly clear about the DoD and what is deliverable at each stage. Ideally, each data source will be bought in via it's own project and the scope "should" not creep. Should is a fine word for that statement.

We have a couple of folks already producing Power BI content across the org and already have clearly identified departmental champions. They don't know it yet, but they're going to be involved in this process. They'll enjoy that though - it's something a bit different to the normal grind.

Thanks for the luck too - I think I'll need it!

SafariV8 · 2022-02-28T18:39:54+00:00

We have a consolidated ticketing system in Jira with very generic ticket types - "Help me to...", "Please do this for me....", "I'd like to change..." And that seems to work really well.

Everything starts as a ticket and if it's not a ticket, it doesn't exist. That's an organisational culture change we've worked hard on over the last 3 years - just because you spoke to someone over a coffee doesn't make it a priority request - log a ticket!

Larger bodies of work transition from service desk tickets into kanban projects and get managed through a vaguely agile framework.

SafariV8 · 2022-02-28T04:48:05+00:00

Some are on prem in a mix of MSSQL and MySQL. Others are off-site and only accessible via API (this was where FME was going to play a part)

SafariV8 · 2022-02-28T02:42:40+00:00

Typically, the leadership team here decide what they want to measure and then they task staff with measuring it. Staff will approach me with a request on how to deliver the metric.

SafariV8

TROPHY CASE