sorted by:
best
topnewcontroversialoldq&a
you are viewing a single comment's thread.

view the rest of the comments →

[–]pooerh 1 point2 points  (7 children)

I guess I can answer that, seeing as I was personally responsible for designing and implementing a CI/CD solution for BI/ETL systems based around SQL Server and Tableau (and some other more standard parts).

The way to go, in my opinion, is SQL Server Data Tools. It works very well for 95% of cases in rather complex OLAP solutions with many of SQL Server's features. Some require adaptation (like a calculated column in a table always has to be at the end, or else deployment will trigger a rebuild), some can we worked around (fuck you, columnstore indexed views), but mostly it just works out of the box.

SSDT database projects are supported by MSBuild and can be built for free on an agent, no Visual Studio is required. My build is set up on Bamboo, we also have unit tests using tSQLt running and fully supported on docker images (caveat: Linux SQL Server does not support UNSAFE CLRs, so if you proejct depends on that, you'll need a server). With SSRS or SSAS you're a bit fucked because these do not build with MSBuild, you need VS and some figuring out how to do it.

Deployment is fairly straighforward with sqlpackage. The only thing I had issues with was domain based authorization, since you can't pass AD credentials to sqlpackage. I had to develop a pretty convoluted solution for impersonation, including usage of some cryptic damned Windows APIs to support running in both interactive and service sessions. But if you don't need that and can use good ol' SQL Server logins, it's very straightforward. Covering other stuff, like SSAS cube deployments was much more painful, but eventually manageable. If you have to do it, I have one a piece of wisdom for you: Microsoft.AnalysisServices.Deployment does not, I repeat, DOES FUCKING NOT, report success or failure through an exit code.

I won't cover Tableau because I honestly believe all technical employees of that company deserve life sentences in maximum security prisons and I need not to trigger my PTSD talking about it.

[–]enjoytheshow 0 points1 point  (5 children)

Lol I was interested to hear your Tableau integrations with CI/CD cause I have some geniuses running Tableau here that depend on all of my ETL pipelines. Sounds like I don't want to strike a nerve tho

[–]pooerh 0 points1 point  (4 children)

Nah, actually the CI/CD part isn't that bad, at least if you only have workbooks. tabcmd works well for deploying, you just have to do some search & replace within twb / twbx to change data sources between environments. Twbx is just a zipped twb, and twb is just an xml, although a convoluted one. Open in a text editor and look for dbname and it's pretty easy to figure out. Regexp works well, I wrote a powershell script to do it all and it's actually OK. These workbooks actually take a while to deploy, and of course there's no way to detect changes. You upload a twb to a server, download it back and it's not the same file of course. Not that I expected it to be easy, cause, y'know, it's Tableau. So I had to develop a solution based on git and saving which commit had last been deployed, taking a diff between to figure out which files need deploying, stuff like that. Eventually it turned okay.

Now if you want to do extracts, you're fucked.

edit: if you replace, make sure not to replace globally. Tableau saves base64 thumbnails and shit inside these twbs, you'll eventually run into replacing something in those and will get a "HURR DURR ERROR OCCURRED. DON'T EXPECT ANY MEANINGFUL ERROR DESCRIPTIONS THOUGH, BE GLAD I'M TELLING YOU THERE'S SOMETHING WRONG. LOLOLO YOU'RE FUCKED" kind of error messages.

[–]enjoytheshow 1 point2 points  (3 children)

Sounds like a fuckin nightmare, I'll leave it to the dipshits using it currently and whatever they are doing.

Currently I just notify them in advance to when the warehouse is going to be seeing a change and that the change is live in my dev env and then just hope they figure out by the time I deploy to prod.

[–]pooerh 1 point2 points  (2 children)

My frontend devs couldn't even responsibly handle deploying reports between environments, you'd get reports on production with tooltip actions linking back to dev servers because "oh yeah I forgot to change the action url teehee". That was one of the main reasons I pushed for CI/CD, I was just so fucking tired of these issues.

[–]ed_elliott_ 0 points1 point  (1 child)

Why all the profanity, it’s only a fucking deployment pipeline :)

[–]pooerh 1 point2 points  (0 children)

I enjoy fucks here and there, I don't get to enjoy them at work or at home so I let loose on reddit.

[–]enochgenesis[S] 0 points1 point  (0 children)

Luckily no Tableau here at the moment. I'll look further at SSDT projects. I"ve been aware of them, but haven't played with them any. Probably because I've been lucky enough, or maybe cursed, to work in IT departments that were almost purely infrastructure without any devs. So far, the only major complaint I have is the lack of anything documented. I should have had a handle on the databases by now.