u/Good_Skirt2459

Hello, I have the requirement of making a report which stores historical snapshots. The snapshots are identified by a snapshot date, with each snapshot having data for 4 quarters.

The access pattern will always be a lookup by snapshot date and quarter, and then some filters on top of that.

Snapshots run 2x weekly and generate about 40,000 records each time. Only about 1m records per year. A safe assumption is that the will be kept for 5 years, so 5m records in this table.

I'm pretty new to SQL server and databases in general, but based on my research, I think a clustered index on the snapshot date and quarter would work well here as it would enable quick access to the data according to my usage patterns.

What do you think? Can anyone give more insight on this or good resources (I'm trying to build my knowledge to be able to make these decisions independently)?

So I'm working out a small nonprofit, which is a big part of the reason that this doesn't necessarily bother me, but I'm just wondering if this is a normal experience. I have 1 YoE as a business analyst only doing reporting and now this job.

So at my job, I have to do a lot of integration, maintaining database integrity, database design, indexes, looking at the query planner, stuff like that, things that maybe would kind of be considered data engineering from what I gather in this subreddit.

I also wind up doing a lot of end user reporting. This can be a mixture of custom web applications, SSRS and other small tools that come with our ERP.

I also do a lot of tech support basically helping end users who use the database kind of understand what they're doing, what certain flags mean in our ERP, how to reset passwords, things like that.

I could list everything but basically think of it as we're a small institution that has an ERP and I'm basically like support for that ERP. So that includes, you know, ingesting the data, exporting the data, but also just general, tiered, whatever, tech suppor and helping out with whatever kind of data interaction needs they have.

In some ways, I appreciate it because I stayed very close to the customer. I really understand the business implications of my work deeper than maybe a lot of people get to.

Is this kind of typical? Like I said, I'm not complaining. It's a non-profit, which is part of the reason it doesn't really bother me. I get paid enough and I know I'm not fulfilling multiple functions for someone else to make more money. But I'm just wondering if this is a typical experience for this job or if larger/ for-profit institutions would tend to have these things more split up.

And more importantly, do you think this could have a negative effect on my career Because frankly I'm not able to get into any of these things as deeply as I might want to. Although I am able to fulfill the business needs and do things I think in a solid way.

I ran into this a few times. Seems like a lot of libraries in the Dotnet ecosystem want me to pay. A lot of PDF libraries do this, even though they are wrappers around something else. Devs deserve to get paid but recently I was working on a project involving PDFs and just used python instead because of it. Is this really the way this ecosystem works? I like to do a lot of hobby/small personal projects, so I'm thinking if there a lot of things behind paywalls I might spec my programming skill into something else.

I was thinking it would be really bad ass if I could eat a ghost pepper and have no reaction. I have a pretty high spice tolerance already.

I have an SSRS report which utilizes a few different datasets, all of which have multiple values to return. The report is filtered to one specific user ID, and each dataset has a "WHERE" clause to filter by that ID.

I need to change this report for only showing the result for entity to multiple entities.

My understanding is that I can not use a literal SSRS list based on a dataset of all the IDs to show because the list would not evaluate each dataset for every row.

Therefore, I thought of using a subreport.

I was told by a colleague that this is unmaintainable, since now you have two different reports to maintain. He said that instead, I should do a cartesian join of every possible combination of my data, and use groups to filter it down. (The result would be one single dataset for the entire report.)

This seems FAR less maintainable to me. Now you have this giant query which does so many things and this cartesian join does not sound performant at all (although I don't think it matters given the scale of data for this particular report).

How would the experts of r/ssrs approach this problem?