February 3, 2021 at 6:16 pm
Okay, I'm super confused. If your data is already in a tabular model, don't you use DAX (or maybe MDX) to query it? If the issue is DAX, read Rob Collie's book, and then there's the huge leap to Ferrari & Russo's book(s). (Rob Collie's book is child's play compared to F&R).
If you have to do ETL, then use PowerQuery. There aren't a lot of books on PowerQuery, but Gil Raviv wrote one, published by Microsoft. I've heard mixed reviews.
February 3, 2021 at 6:32 pm
I do use MDX. I have to. They wont switch the SQL Server to listen to SQL!!
I have the books. It is _very_ difficult to write an MDX query with multiple WHERE or other filters.
Excel VBA can "PowerQuery" many different ways. Works better than going thru an official "SQL" "database" where variant data types lose information.
I suppose the relevant thing here is, what is the scope of SQL Saturday and related events? Only for DBAs, not for Business intel statisticians who have their separate read-only reporting servers? If so, where is the equivalent group to SQL Saturday for us folks?
February 3, 2021 at 8:33 pm
I do use MDX. I have to. They wont switch the SQL Server to listen to SQL!!
I have the books. It is _very_ difficult to write an MDX query with multiple WHERE or other filters.
Excel VBA can "PowerQuery" many different ways. Works better than going thru an official "SQL" "database" where variant data types lose information.
I suppose the relevant thing here is, what is the scope of SQL Saturday and related events? Only for DBAs, not for Business intel statisticians who have their separate read-only reporting servers? If so, where is the equivalent group to SQL Saturday for us folks?
Lordy... Ok... now believe I see where you're coming from. They have relegated you to using only those tools that they think someone in your position should need to use and you have no way, except by some "resourcefulness" that you shouldn't need to resort to, to explore the possibilities of adding to or designing new cubes nor even check the quality of the data sources of existing cubes (etc) when you come across an anomaly. It's a bit like giving a chemical engineer all of the components parts they need already premixed and telling the engineer that they can't even check the distilled water unit for contamination or even read the labels on the bottles for expiration dates, etc, etc.
If I'm reading all of that correctly, it's no wonder you're a bit sour on all of this SQL and DBA stuff. Every time you try to do something better, you're told to "go to your room". 🙁
[EDIT] Almost forgot... I don't know the name of specific events like those you're looking for but I do know they exist. IIRC, they even had a couple of "BI" SQL Saturdays ("BI" (Business Intelligence) being the operative "word" here) prior to the pandemic and there are user groups that should still exist. You can probably find a lot of that on "Meetup" and "EventBrite" and in other searches. I don't know you or how good a Data Scientist you are but you might find some of that boring. My encouragement is that even if such a meeting were to be totally full of hooie, at least you'd have confirmation of how not to do things. You might also bring a strong hand to the table to help others which would, in the long run, also help you in some of the most expected ways.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 3, 2021 at 8:38 pm
Jeff: Right on! You describe my situation well!
Ratbak brought up SSIS. Another beef. Wasnt the ultimate for SSIS that the business user could add their business rules in the data stream / workflow? Ha. Never happened in my lifetime.
From Semiconductors to USMC to War in Afghanistan to state government, the "DBAs" and "IT" of this world's answer is "No".
So the typical business unit statistician has to be very scrappy. While DBAs get to go to SQLSaturday and -- do what? Talk about SQL? Really?
February 3, 2021 at 8:41 pm
Jeff: Cubes might be actually useful if I could add the Business Rules, the time dimension, etc etc.
As it is, they wont even let me do a 1-line copycube. So I have to slice the cube back to 100 different tables to my own datamart every morning to start my reporting day for the whole state.
February 3, 2021 at 8:52 pm
Jeff: Right on! You describe my situation well!
Ratbak brought up SSIS. Another beef. Wasnt the ultimate for SSIS that the business user could add their business rules in the data stream / workflow? Ha. Never happened in my lifetime.
From Semiconductors to USMC to War in Afghanistan to state government, the "DBAs" and "IT" of this world's answer is "No".
So the typical business unit statistician has to be very scrappy. While DBAs get to go to SQLSaturday and -- do what? Talk about SQL? Really?
Have a look at my [EDIT] in my previous message. You're not alone in all of this. And not all DBAs are like the ones you've had to work with. There are people at work that I've given privs to do their jobs (and convinced management it's the right thing to do). I monitor my systems pretty tightly and so when I see one of them write a really nasty query, I get with them and actually show them how to do what they want to do better instead of cutting them off at the knees like some people would do. Being careful to lockdown the box itself, I also give the Developers sysadmin privs so they can experiment (my only restrictions are don't do backups/restores and don't take it upon themselves to grant privs to anyone or application without us hammering that out together).
As for SQL Saturdays, they usually have some pretty awesome stuff on SSIS, SSRS, SSAS, and a shedload of "data scientist worthy" subjects although, like I said previously, might not measure up to your standards as an actual Data Scientist. If that happens, you should consider teaching it to the rest of us poor slobs. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
February 3, 2021 at 8:56 pm
HaHa, thanks Jeff for the great discussion. Now if I can figure out which groups to join... Dont know of any SQL Saturdays coming up... Where do I go for my kind of stuff?
February 3, 2021 at 9:06 pm
HaHa, thanks Jeff for the great discussion. Now if I can figure out which groups to join... Dont know of any SQL Saturdays coming up... Where do I go for my kind of stuff?
SQLSaturdays are in kind of rough shape right now. The parent organization (PASS, the "Profession Association for SQL Server") went bankrupt and dissolved on Jan 15th. RedGate (the company that own this site and the "Simple Talk" site) announced they purchased all the rights to all that just several days ago and they are still sifting things out.
But, like I said, if you search on Meetup and EventBrite, you can find a wad of other events that had/have no association with PASS and are still doing well on their own.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 3, 2021 at 9:09 pm
Hopefully, some of the others on this site that are more into the arts and sciences of Data Analysis and Data Science and Business Intelligence will see this post and kick in with some of the events that they know of.
How's'bout it folks? Do you have any recommendations for such events?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 3, 2021 at 9:33 pm
I just think it would be tough for someone drilling down in data without some sort of database support. Pointing adhoc tools like VBA or Excel at OLTP servers really makes my agent orange act up unless these folks are willing to trade some insight into what they're doing with what I'm doing.
Time and again, I see the same pattern, reports that are analytical in nature bog down OLTP and then hey guess what, everyone decides to nolock everything and I get it, its an easy solution that needs no separate data warehouse. I've come to realize that all this RBAR and cursor based stuff that ERP guys produce is meant for business transactional operations and it comes down to the fact that large set based queries that aren't nolocked produce contention, whereas the cursor / iterative approaches fetch some records, get out of the way, then fetch more, repeat. Yes they're slow, but they're also built with the intention of keeping the business going.
On an old system, it was just a thing, accounting would complain, I'd check the database, and there would be Crystal Reports running against the entire years worth.
I like data warehouses, if you want big picture stuff, trends for your execs, point your fancy mouse pilot point and grind stuff anywhere but OLTP.
Sure, maybe I'm wrong, change my mind!
February 3, 2021 at 9:52 pm
SSC-Insane, No-one like me queries any OLTP live servers. That's not what we are talking about.
As you know, the business guru is given read access to a daily snapshot server. From which I make my Excel datamart.
100% of the business report customers want output in either Excel or pdf (easy save-as from Excel) or Word (again, easy from VBA).
So it's much more efficient to keep the datamart in Excel for any table less than a few hundred thousand records.
Data warehouses do not have quality control. I have quality control charts on all my datamart tables. Going back years.
Data warehouses are a day behind in their data and months behind in reporting capability. They are a mausoleum for data.
February 3, 2021 at 11:11 pm
From Semiconductors to USMC to War in Afghanistan to state government, the "DBAs" and "IT" of this world's answer is "No".
Why are you surprised?
Don’t you know what “DBA” stands for? “Don’t Bother Asking”.
My fellow DBA in his birthday was given a present from the workmates - a toy big red button which says “No” in different tones every time you press it. So they can complete any request with pushing that button - and get their answer straight away.
🙂
_____________
Code for TallyGenerator
February 3, 2021 at 11:25 pm
Nope, learned what DBA means here!
I cant say no. I always say yes. When Bedford changes back from a town to a locality, I can make the change in 1 minute in my lookup master. I can then get the report done for the state legislature. No waiting months for the DBA to say no.
February 4, 2021 at 12:34 am
So you are saying that SQL Saturday is only about live databases, not those used for reporting?
No, it’s not what I’m saying.
It’s your choice to use SSAS, MDX and Excel to build the reports. It’s not safe to use those tools to query live data. The only option left for you to use relocated snapshots of the data from the last day. Which you, apparently, are not so happy about.
Or - you may change tour ways, and instead of demanding access to the data work with DBA’s (or become one yourself) in order to produce an interface which would deliver data sets which you need for you reports and do it in timely manner without compromising the overall performance of the database.
it’s not an easy job, not every DBA would be capable of doing that. So, it’s a question of the balance between gains and expenses. See if you can make your business case.
_____________
Code for TallyGenerator
February 4, 2021 at 2:05 am
SSC Guru, I've not had any demand for less than one day latency. So I have no need to improve to querying the live database.
I dont need to be a DBA to make my own interface and xlSQL! Already done that! I've made my business case. My datamart/reportmart is the model for our state.
Guess I was looking for encouragement that someone here would "have had my back" for advocating switching my non-live-OLTP SQL server to listen to SQL rather than listening to only MDX or DAX. Would also save them from having to cube my data every day. Wouldnt have to maintain the measures and dimensions I never use. May there be some reason they chose tabular mode when they should have known I couldnt use any of the cube features since I cannot load business dimensions to the cube?
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply