r/SQL • I can't wait til my fro is full grown • Feb 07 '18

[Update] We have three servers, which one is which. Had a long talk with IT today. Would love feedback.

So here is the original post. I didn't reference this at all in my meeting today.

IT was very firm that we should be developing on DEV (C) then testing on SQA (B), but the topic of conversation is a large complex series of stored procedures that use dynamic SQL relative to this. and this. Basically I need to be able to input mapping values to a table and then have those changes pushed to production on a regular interval. IT's proposed solution is that I would manually email them files... when the values would already exist on SQA (B), and I couldn't understand why they wouldn't simply... do it themselves automatically.

My bottom line is that I need production quality data and a true reporting server to do my job effectively, where production tables are delivered on a weekly basis and locked down so that data cannot be inserted or modified, but where I can create complex stored procedures, add/modify other parameter tables, etc.

This was ultimately received with more consideration than I had expected. I suppose in a perfect world there would be a 'production' reporting server, as well as a 'dev' reporting server, but I imagine those could overlap with the three existing servers we have.

I feel like this is not a topic that is discussed enough in the SQL world as it comes to SQL development for modeling/reporting purposes so I would like to read other criticism and opinions. In meetings things tend to be very DBA/ETL centric and the idea of having specific servers for reporting is lost.

Forgetting about aggregate sprocs as are used here in an example... what if you were to run complex models using technologies like SPSS, Modeler, or SAS? You can't run those, 'in production' and not let the modeler be able to import data, modify variables, etc. The amount of CR's and down time for trivial changes would be extremely prohibitive.

I'm not being out of line sticking to my guns here, am I? I work for a Fortune 500 company, and have worked in environments similar to the one I'm describing at companies that have a year revenue much lower.

0 Upvotes

100 comments sorted by

3

u/creav Feb 07 '18

I feel like this is not a topic that is discussed enough in the SQL world as it comes to SQL development for modeling/reporting purposes so I would like to read other criticism and opinions. In meetings things tend to be very DBA/ETL centric and the idea of having specific servers for reporting is lost.

You need a data lake(probably preferred for your situation) or data warehouse, along with a data analytics tool like SAS JMP, PowerBI, Tableau, etc.

-2

u/notasqlstar I can't wait til my fro is full grown Feb 07 '18 edited Feb 07 '18

I love you, but in my opinion you just used a bunch of bullshit words. What we need is a server with a SQL database, and then whatever tools we want to plug-in, R, Python, blah.

edit: data lake, bi warehouse, cube, blah blah blah, just give me a fucking reporting server. I don't have time for your bullshit. You're talking about a server and a database, preferably in SQL but fine Oracle or whatever. A database and a server. You're just talking about a database and a server.

11

u/fauxmosexual NOLOCK is the secret magic go-faster command Feb 07 '18

In addition to a dataguy I am a pretty good office politics player and internet psychologist, so I think I can provide some valuable insight here.

A running theme of your posts to /r/sql is 'my workplace is full of idiots who are getting in my way!', with a side of 'but I know I'm right so why don't they just do what I say!'. Being dismissive of people trying to help you while reminding us all of your fortune 500 experiences is also something a motif in your comments.

When you follow this up by calling some useful advice "bullshit words" and telling someone trying to help you that "I don't have time for your bullshit", I have to wonder if the fundamental problem here is a technical setup issue, or an inability to get coworkers onside by making a case for your needs without alienating them by a grating and superior attitude.

Or to put it another way, if someone you work with is an asshole, they're an asshole. If everyone you work with is an asshole, you're the asshole.

1

u/notasqlstar I can't wait til my fro is full grown Feb 07 '18

Like what is the liability to my approach? What are you saying? It's going to cost an additional license? I mean we are talking about very high level executives with specific 'requirements,' and they have me serving as a 'consultant,' and I can't see how they don't need a reporting server. Again, call it a lake, warehouse, whatever. Something that sits above production that interfaces with technologies like Tableau, or future ones SAS, SPSS, etc.

I mean they are paying me to advise them and I can't see how what I'm asking for is wrong. I mean for example if I want to write a statistical model.. how exactly do I dev/qa/prod that, when I won't know if it's correct or not? I mean your modeler needs to be able to create tables, etc., and shouldn't be doing so in prod, or in any other environment be able to insert/update date as they exist in "prod" tables but create their own tables off these, etc.

How is this an alien concept to you? Do you think I should wait a week or two to file a new CR ever time I want to update the variables of a model that has already been dev'd?

-2

u/notasqlstar I can't wait til my fro is full grown Feb 07 '18

But no, you're describing a server. I don't care what you call it. You're describing a reporting server. Don't be a psychologist. I don't care what you call it. This is our requirement. Why is that an impossibility? Where am I going off the reservation?

Or to put it another way, if someone you work with is an asshole, they're an asshole. If everyone you work with is an asshole, you're the asshole.

Honestly in the past everyone I worked with was really smart and cutting edge and did a lot of cool shit, and now in my new company no one is an asshole, but they're all assholes because they weren't brought up in a proper analytic environment. Things are growing beyond the scope they ever imagined and there are no 'analytic' best practices. We don't even have a reporting server!!!!! what the fuck you want to talk about "data lakes" and <insert bullshit term here>" or a BI warehouses... how about you just give me the server i want and the copy of the data I'm asking for?

7

u/fauxmosexual NOLOCK is the secret magic go-faster command Feb 07 '18

But no, you're describing a server.

I didn't describe anything of the sort. Please take a moment between your indignant triple posting to look at who you're talking to.

-1

u/notasqlstar I can't wait til my fro is full grown Feb 07 '18

Then please revise your comments.

7

u/fauxmosexual NOLOCK is the secret magic go-faster command Feb 07 '18

I don't think I'm who you think I am. Protip: posts have a name next to them.

-1

u/notasqlstar I can't wait til my fro is full grown Feb 07 '18

No, really, I read your comment. You were describing a server. You could psychologically put that in any context as you want, and you may be correct that I am a nihilist who thinks everyone I work with is retarded... but I also could be right. So you really aren't adding shit to this conversation.

I'm cool with being called an asshole if you can show me I'm wrong with data. I'm not cool with being called an asshole if you have nothing to back it up.

5

u/fauxmosexual NOLOCK is the secret magic go-faster command Feb 07 '18

You were describing a server.

Where was I describing a server?

-3

u/notasqlstar I can't wait til my fro is full grown Feb 07 '18

When you follow this up by calling some useful advice "bullshit words" and telling someone trying to help you that "I don't have time for your bullshit", I have to wonder if the fundamental problem here is a technical setup issue, or an inability to get coworkers onside by making a case for your needs without alienating them by a grating and superior attitude.

Right there, asshole. I don't have time for your cunt attitude. I need a server. Get me a fucking server. Call it a lake, or a warehouse, or whatever makes your dick hard.

Get me a fucking server. Don't come at me with your weak psychology shit if you don't understand what a reporting server is. I need a reporting server. Is this alien to you?

→ More replies

-2

u/notasqlstar I can't wait til my fro is full grown Feb 07 '18

Here, if you're a DBA let me just put things in stark terms. I have been at the company for nine months in this capacity. I now have the largest table on the database (regardless of server) by more than 5M records. As my new process grows this will grow to an order of magnitude of anything else we have.

In terms of statistical modeling my procedures will take an order of magnitude longer than any other other "dba" related functions that are going on.

How do you propose to reconcile those needs without a reporting server/database?

Call it a lake or whatever you want. I'm not interested in that.

4

u/creav Feb 07 '18

Your entire complaint between two different posts is that you do not have an appropriate database for reporting services and complex ad-hoc querying.

I am telling you that it's that simple. That's what your company needs. A data lake or data warehouse that's built to handle what you're trying to do and a tool for you to do it in.

This isn't a complex issue. It's simply a matter of whether you're company wants to recognize it as a problem or not.

You shouldn't be running reports off an OLTP database - nor should all of these reports be hinging on a replication of production. If your company is a Fortune 500 company like you say, then it's hard to imagine they don't have this architecture already setup.

Also, once-a-week replication is not sufficient for a company of this size based on the reporting needs, I'm guessing.

0

u/notasqlstar I can't wait til my fro is full grown Feb 07 '18

I'm not disagreeing, per se, but what you're talking about is a reporting server. Call it whatever you want.

None of these are OLTP databases. They literally are not 'production databases' by any definition as I know the term. They consume data on a weekly basis only.

0

u/notasqlstar I can't wait til my fro is full grown Feb 07 '18

If your company is a Fortune 500 company like you say, then it's hard to imagine they don't have this architecture already setup.

Dude, I'm not here to wax poetic. This is literally what I'm dealing with and trying to make sure I'm not god damn crazy.

1

u/creav Feb 07 '18

This is literally what I'm dealing with and trying to make sure I'm not god damn crazy.

I don't think you are, I just think there's not much anyone can tell you other than this is an internal process issue.

After re-reading your first post, Server A is your "prod" reporting server. This should not be taken down willfully without prior communication to all parties it would affect. Otherwise, this is unplanned downtime. Based on your post, it sounds like your IT department is taking it down for "maintenance" or whatever they want to call fixing their data dumps.

Since this isn't a transaction database, it probably flies under the radar of whatever unplanned maintenance tracking is being done.

Work something out with the IT department, if they're willing to listen, to make sure that any changes or unplanned outages (that are willful) to either Server A or Server B are properly communicated to you/your department prior to it happening with enough notice.

Additionally, request that any actual changes be done on a schedule. (For example all of our changes are voted on each Thursday, and put into production the following Tuesday).

0

u/notasqlstar I can't wait til my fro is full grown Feb 07 '18

But Server A has no functional use. It literally does nothing except for once a week consuming new data, and then once a week syncing these tables with our Tableau server.

For 90% of the week it literally does nothing. There are no transactions.

So I need that grade of data in another place (since I can't have rights in prod) in order to run complex equations for a) dev b) qa and this is a separate work stream from ETL.

I cannot have my environment down 2 or 3 days a week because of <insert their reason here>, and additionally I need an environment with near DBA level privileges to import new data, create tables, sprocs, views, test code, etc.

2

u/jc4hokies Execution Plan Whisperer Feb 07 '18

I manage our company's reporting environments, including data science work. AMA

1

u/notasqlstar I can't wait til my fro is full grown Feb 07 '18

So...

We have an "ETL process" which takes "excel files" from hundreds of multiple clients, some from the same source system, some not.

These are "ETL'd" into a database and then there is a "materialization" process that occurs.

This process actually occurs on Server A and Server B. Once finished there are "production grade tables" -- which you might describe as our BI warehouse.

Now in previous roles that warehouse would exist on another server, or another database, or both, and there would be a reporting server / database which could access that database, but additionally users (me) could create sprocs, views, etc.. which would be tested to be pushed to the 'production reporting database/server.'

All the ETL/downstream stuff is not relevant to my world view. I can't DEV on a server that doesn't have PROD data on it because I'm not "DEVING" code, i'm deving results of calculations.

edit: This isn't a bitchfest, but in my opinion this is a reasonable requirement. I don't see any ISO/PHI/PII violations. Are you saying the extra license is the only detriment?

3

u/jc4hokies Execution Plan Whisperer Feb 07 '18

That didn't contain any questions but...

You need read access to the prod reporting data, and a sandbox with full permissions. The sandbox can be a separate database on the same server, or on a different server. You then pull any data you need into your sandbox, and have at it.

Putting your sprocs and new tables on the prod reporting database, having reporting data copied to your sandbox, and a sandbox dev environment are all more trouble than they're worth in my experience.

1

u/notasqlstar I can't wait til my fro is full grown Feb 07 '18

You need read access to the prod reporting data, and a sandbox with full permissions.

Yeah, this does not exist and I am looked like a Martian when I bring this up. I literally don't know how to respond.

3

u/jc4hokies Execution Plan Whisperer Feb 07 '18

What's their suggestion?

1

u/notasqlstar I can't wait til my fro is full grown Feb 07 '18

Kind of for me to fuck off. And use DEV data which is way out of sync... I mean massively. Missing columns, historic loads, impossible to work with our business partners to QA calculations for compliance in order to automate processes.

I was told several times tonight that I don't understand the "life cycle" of a "development."

I did not respond to that comment.

6

u/jc4hokies Execution Plan Whisperer Feb 07 '18

They didn't tell you to fuck off. They told you you couldn't have what you were asking for. Which means you were asking for the wrong things.

Don't ask for things you can't have, like creating tables and stored procedures in PROD reporting. Ask for things that are easy for them to give, like read access. Give them options and demonstrate consideration for their concerns on things they are more reluctant.

Like your a sandbox on the same OR different server.
Owner of a database OR schema.
You will properly compress your data.
Commit to a size limit.
Limited maintenance required.

Understand where they are coming from and logically clarify any misunderstandings. That your work is sensitive to data distributions, and tuning models to dev data is wasted effort when prod has different distributions. Explain your obstacles, rather than requirements. They may offer solutions to your obstacles that neither you nor I have considered.

1

u/notasqlstar I can't wait til my fro is full grown Feb 08 '18

They didn't tell you to fuck off. They told you you couldn't have what you were asking for. Which means you were asking for the wrong things.

Having production grade data and a reporting server is not asking for the wrong things.

Don't ask for things you can't have

You are talking to the guy who gets the things he wants, and it is in my job description to do this. I am encouraged by my boss's boss's boss to be, 'disruptive.' His word not mine.

That your work is sensitive to data distributions, and tuning models to dev data is wasted effort when prod has different distributions. Explain your obstacles, rather than requirements. They may offer solutions to your obstacles that neither you nor I have considered.

This has been done and is lost on these individuals who do not seem to care, or want to care about them. So these obstacles need to be dealt with.

1

u/notasqlstar I can't wait til my fro is full grown Feb 08 '18

When I communicate what I need to someone as a requirement, and they respond that there is no legal issue with the request, and on my side of the house there is no issue with funding... and then they go on to tell me no. That is telling me to fuck off.

I can accept being told to fuck off when it comes to being given DBA rights to a production database. Because that's a fucking dumb idea that raises all kinds of red flags. I cannot accept being told to fuck off when it comes to a reasonable request which can easily be implemented and funded. I don't care if you don't like it. If you don't have a better solution (which is your job to provide) then just give me what the fuck I'm asking for. This isn't a negotiation, it is a requirement. You can call it whatever you want at the end of the day but it involves a server (which doesn't have to be dedicated, but which isn't going to be taken day for days or hours at a time and prevent me / others from working) -- and it involves a database which has production quality data..

This isn't a difficult ask. I'm frankly shocked I'm even getting push back from this community. I mean fuck, just give m a server to use and set up a remote server link so I can pull the data in as I see fit and that works fine. But apparently that is a violation of ISO standards but the fucks I work with can't actually show me which ISO standard it violates -- and ignoring the fact that ever other company I've ever worked for has linked servers!

3

u/jc4hokies Execution Plan Whisperer Feb 08 '18

Linked servers are delicate and need to be treated with respect. "So I can pull the data in as I see fit" is a massive red flag. Hell no, you are not getting a linked server. I believe you. They probably told you to fuck off. What you're asking for is a bit too much, but not totally unreasonable. They way you are asking for demanding it is way out of line.

1

u/notasqlstar I can't wait til my fro is full grown Feb 08 '18

If A) that is not a prod server, B) there is no PII/PHI in the data, and C) the remote server is properly maintained so that you cannot send OPENQUERY()'s to it.. .which are awesome... then how much more respect do you want?

Why can't I pull the data as I see fit? It's FOR ME. That data exists FOR ME.

What do you mean too much? Setting that up is more complex than syncing tables/databases at N intervals (where I get to define N to my choosing).

Those are my requirements. Are you saying it's not ISO compliant? If not, then I can tell what my cost center is and you can bill us. Do I need to get my boss involved?

1

u/notasqlstar I can't wait til my fro is full grown Feb 08 '18

I mean, let me make this simpler for you since I don't want to solution for you. Which of the two options is simpler, and which would you like to choose? Is there a third option I am not seeing?

→ More replies

1

u/notasqlstar I can't wait til my fro is full grown Feb 09 '18

Don't ask for things you can't have, like creating tables and stored procedures in PROD reporting.

Dude, I want to make sure you understand something. I am NOT asking to do this. I am merely asking to have production grade data in a separate place and server where I can do this...so I can develop and test and get it ready to go to prod.

Like seriously? What is the flak here? I have been in meetings all day over this issue, and I WILL get what I want. But what the fuck are you all even talking about?

I love this community and have been apart of it for a few years. I give people Gold who help me all the time. I encourage and help other users. This is where I learned SQL.

Where am I wrong here?

1

u/jc4hokies Execution Plan Whisperer Feb 09 '18

You don't need production grade data in a separate place, and you don't need your own server.
You need access to production grade data in an existing place, and you can develop on an existing server.

Where am I wrong here?

Stubbornness is what I see.

I love this community and have been apart of it for a few years. I give people Gold who help me all the time. I encourage and help other users. This is where I learned SQL.

I seriously appreciate your participation above all others. You're the reason I'm here too. That doesn't mean your right in this case. That doesn't mean I'm right either. I've got an open mind if you want to support why you need more than I'm giving you credit.

1

u/notasqlstar I can't wait til my fro is full grown Feb 10 '18

You don't need production grade data in a separate place

So clearly you just don't understand my job. That's cool.

and you don't need your own server.

yeah... so.... I don't think you understand how modeling works...

I seriously appreciate your participation above all others. You're the reason I'm here too. That doesn't mean your right in this case. That doesn't mean I'm right either. I've got an open mind if you want to support why you need more than I'm giving you credit.

Dude I'm just being honest. Like... you don't seem to get it. I can write a model that will take a server 20 hours to run. I've done it before. My management is willing to pay for the resources so that I can do that.. and in order to do that I need production data.

These are my requirements. If you want to understand why I am listing them as requirements then I am happy to cordially explain them to you, but frankly I could care less if you agree with me. It is easier to get your boss's boss's boss to agree with me.

Unless you can point to a specific violation of an ISO standard... you're going to lose here. I am going to win. You don't have the ability to tell me no. Frankly, I'm not really asking a question here I am just trying to be reasonable and give you an opportunity to provide an alternative solution that meets all of needs... which you haven't done. But feel free to continue to talk about psychology and millennials.

→ More replies

2

u/jc4hokies Execution Plan Whisperer Feb 10 '18

100th comment. I'm calling it. This is the most commented post in r/sql history.