February 1, 2021 at 9:54 pm
My data is in SQL Server SSAS Tabular. It does not speak SQL!
The vast majority of business IT is done by those of us Statisticians and others who have no access to SQL.
We have to scrap for data anywhere we can find it.
The standard answer from IT is "No".
So the appropriate ETL is Excel VBA, Power Pivot, Power BI, or similar.
So what's this SQL all about if it isn't serving the business?
February 1, 2021 at 10:20 pm
SQL does serve the business. Not always so for IT, especially the one's that are saying "NO" to Statisticians and others without providing help. I get they don't want a ton of people with ad hoc queries running around crazy but that's not the fault of SQL. It's "the IT people" and, frankly, it's the Statisticians because they don't necessarily know how to work with SQL nor have an appreciation for how badly they can lock up a system if they don't do things right.
You need a good DBA or DBA team that can help provide code and the education to help you write better code and also show you how to have the stuff you need waiting for you when you login in the morning AND it can be done with some of the tools you're used to using for the graphical side of the world.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 1, 2021 at 10:33 pm
Been there. Done that. From semiconductors to automotive to home medical to military to health in government.
The only DBA or DBA Team are _not_ there for the business.
The DBA has never and will not "have the stuff you need waiting for you when you login in the morning AND it can be done with some of the tools you're used to using for the graphical side of the world"
So I _am_ the ETL. The data quality. The backup artist. The maintainer of the Dashboard. The maintainer of all the choropleth maps. The producer of thousands of reports. Every morning I push the button for my xlQL VBA script to run, downloading the 100 snapshot data tables over 20 minutes while I drink my coffee. Then another 10 minutes to run the day's automated reports and send them to various file servers.
It _is_ done with the tools I use for the graphical side of the world. By me. DBAs never have and never will do that.
February 2, 2021 at 1:59 am
Heh... you need better DBAs. To be honest, though, I feel your pain. The DBAs I've had to work with in the past made sucking sounds in the areas you speak of. Now, as a DBA myself, I use their model of how NOT to be a DBA. Before the lockdown, my desk was with the Developers and folks needing help and the right kind of access weren't very much further away just so I could help others. I also wrote (and continue to write) a whole lot of code to help others with the very things you speak of.
Anyway, sounds like you've got what you need (you'd actually make a great application DBA from the sounds of it). Just realize that some of us DBAs actually are "there for the business" and are enablers rather than obstacles. It's a shame you haven't met/worked with some of them in your work history.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 2, 2021 at 3:22 am
I've been in similar situation.
data analysts from around the world wanted to be able designing and running customised reports from the live data as it's being updated by continuously arriving new sets of inbound feeds.
Having no illusion about the quality of the SQL done by data analysts I said "No".
Instead, I developed a bunch of views which covered all the areas the analysts were interested in and allowed them querying the data without causing excessive locking and disruption to the normal data processing.
Sorry, dear datascientist7, but that's the only way I could see. The data tools you use for analytical purposes usually work as they are the only ones using the data. Being allowed directly into SQL Server they will block each other and bring the SQL Server instance to its knees.
Using data replication into snapshot tables is a common workaround. But I dont think it's the best one. I saw the case when numerous massive replications cause SQL Server being effectively out of service for more than a hour every weekday morning. After working with the data analysts I managed to create a set of interfaces which allowed them to get all the reports done within 15-30 minutes, much faster than the replications would provide them with the data to work on. And with no disruption to other activities on the databases.
so, I'm with Jeff here - you need better DBA's. May be not so DBA's but quality SQL developer(s).
And don't you dare to put your beautiful graphical data tools anywhere near live SQL data! The database will be out of reach for anybody/anything else for the whole time your reports are being produced.
_____________
Code for TallyGenerator
February 2, 2021 at 4:31 am
Thanks for your nice discussion Jeff and Sergiy!
Yes I was a little tough on DBAs. Anyone can do anything really.
Back to the point here -- so what is SQLServerCentral or SQLSaturday all about? That's what I dont understand. What's the real meaning of SQL here? Surely not SQL.
February 2, 2021 at 6:39 am
What do you put in "SQL"?
Because SSC is where I learnt to do SQL right.
So, it must've be about SQL, to some extent. 🙂
_____________
Code for TallyGenerator
February 2, 2021 at 7:57 am
Perhaps the point is: name the event: Data Saturday, not SQL Saturday. There is more in the world of data analysts than SQL.
February 2, 2021 at 1:39 pm
And it's more than data analysts. So this is worthy of a larger discussion of what is and what is not of interest to this group. Let's scope it.
February 2, 2021 at 7:24 pm
And it's more than data analysts. So this is worthy of a larger discussion of what is and what is not of interest to this group. Let's scope it.
Heh... Ok... what do you suggest for a "scope"? Except for the fact that you're a bit pissed off that the DBAs tell you "no" (which is sometimes right and sometimes not depending on what you think you want to do), what do you think the purpose of a database and SQL (the data language for relational and other types of databases) is? Why do you think we frequent this site? What is the actual knowledge that you want to gain about all of that by coming to an "SQL" site?
p.s. While we're at it, what is your definition of "data scientist" and "data analyst"?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 2, 2021 at 9:10 pm
>what do you think the purpose of a database and SQL (the data language for relational and other types of databases) is?
not sure that would help scope this forum. I have to use MDX to query an SSAS cube.
>Why do you think we frequent this site?
I have no idea. You can answer that, right?
>What is the actual knowledge that you want to gain about all of that by coming to an "SQL" site?
What I need most right now is how to query using multiple filters in MDX.
p.s. While we're at it, what is your definition of "data scientist" and "data analyst"?
>"data scientist"
Data scientists examine which questions need answering and where to find the related data. They have business acumen and analytical skills as well as the ability to mine, clean, and present data. Businesses use data scientists to source, manage, and analyze large amounts of unstructured data. https://ischoolonline.berkeley.edu/data-science/what-is-data-science/
"data analyst"
The data analyst serves as a gatekeeper for an organization's data so stakeholders can understand data and use it to make strategic business decisions. It is a technical role that requires an undergraduate degree or master's degree in analytics, computer modeling, science, or math. https://www.northeastern.edu/graduate/blog/what-does-a-data-analyst-do/
February 3, 2021 at 7:23 am
OK, let’s add another definition:
SQL : Structured Query Language.
I have to use MDX to query an SSAS cube.
MDX is not SQL, it’s a different tool to work with data. Different letters in their names should be the first clue.
You are coming to SQL forum and complaining it’s not MDX enough? That’s a bit funny.
I can tell you why they don’t want you to run your queries on live production database. MDX queries on SSAS, as well as other analytical tools, like Tableau, etc., treat the data as its solemnly in their possession. There is no consideration for any concurrent users querying or modifying the same data sets. They read the full data set into memory, all whatever GBs are in there and then apply filters, transformations, etc. While they are on it, it’s important that the underlying data won’t be changed, or the report would be based of fragments of data having no integrity between them. Everything your query touches must be locked from other users. And from my personal experience I can say those analytical queries touch almost every table in the database.
So, while you’re enjoying you cup of coffee waiting for your queries to complete, the database is effectively offline for the rest of the world.
Which is probably not an option for the business, that’s why the DBA’s say “no” to your requests.
If you want to get real time reports from live data you need to learn actual SQL - structured query language, - and learn how to build queries in such ways so they don’t block other users/processes from using the data at the same time. It’s not as easy as constructing filters in MDX, but this forum could be much help if you decide to go that direction. Because it’s about SQL - just as it’s name suggests.
_____________
Code for TallyGenerator
February 3, 2021 at 4:40 pm
They have a separate backend server from the live database, of course.
Just for me. I run all the business intel for this division statewide.
I query the separate SSAS server, as you know.
So there is no issue about gumming up the live server, as you know.
>If you want to get real time reports from live data you need to learn actual SQL
So you are saying that SQL Saturday is only about live databases, not those used for reporting?
February 3, 2021 at 5:20 pm
So you are saying that SQL Saturday is only about live databases, not those used for reporting?
Not at all. Many of us do reporting using TSQL. MDX isn't the only way to get data into/from a reporting database. One can use SSIS processes, TSQL processes (often scheduled using SQL Agent jobs), replication, or Always on Availability Groups, for instance.
February 3, 2021 at 5:54 pm
>One can use...
Who is One? Not 99% of anyone in a business unit that needs the data the most!
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply