January 4, 2016 at 4:06 pm
Solomon Rutzky (12/29/2015)
Eric M Russell (12/26/2015)
Well, im still not sure about RegEx in a data warehouse. Parsing text columns suggests things like product descriptions were not identified by category in a way thats useful to the business during the dimensional modeling phase.Although parsing data is common when staging data from the source system, I just wouldnt want to see end users writing regex or clr queries against the data warehouse, which should be conformed. It would suggest poor upfront modeling and requirements gathering.
Does this mean that you don't allow for "contains" / LIKE '%' + @SearchTerm + '%' type filtering of dimension tables? Or does such a desire fit into your second paragraph as merely indicating the need for another categorization?
I am really just asking. I do not claim to be a BI / DW expert, and much of my experience in that area was on a system that, for various reasons, probably broke more rules than it followed ;-).
These LIKE type of searches can be done on degenerate dimensions. Such as a search for a license plate number.
----------------------------------------------------
January 14, 2016 at 10:29 am
MMartin1 (1/4/2016)
Solomon Rutzky (12/29/2015)
Eric M Russell (12/26/2015)
Well, im still not sure about RegEx in a data warehouse. Parsing text columns suggests things like product descriptions were not identified by category in a way thats useful to the business during the dimensional modeling phase.Although parsing data is common when staging data from the source system, I just wouldnt want to see end users writing regex or clr queries against the data warehouse, which should be conformed. It would suggest poor upfront modeling and requirements gathering.
Does this mean that you don't allow for "contains" / LIKE '%' + @SearchTerm + '%' type filtering of dimension tables? Or does such a desire fit into your second paragraph as merely indicating the need for another categorization?
I am really just asking. I do not claim to be a BI / DW expert, and much of my experience in that area was on a system that, for various reasons, probably broke more rules than it followed ;-).
These LIKE type of searches can be done on degenerate dimensions. Such as a search for a license plate number.
Ok. So it does seem like there is at least limited application for RegEx searches (i.e. on degenerate dimensions) in cases where the functionality of the LIKE operator doesn't suffice.
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
April 30, 2016 at 3:20 pm
I've been playing around with Oracle Data Integrator, formerly Sunopsis. It is primarily an ELT tool rather than an ETL tool.
To be honest, most of the SSIS packages I've seen lean strongly towards ELT with the capabilities of SSIS really managing workflow more than transforms.
If you are going to do a lot of ELT then SQLCLR makes a lot of sense but maybe you would have an explicit staging DB and possibly server for ELT type work.
In terms of SQLCLR calling a web service. Possible but why would you? I have seen the results when a decree from the padded cell of the ivory tower insisted that all interactions with all databases be via web services. RBAR madness ensued and it demonstrated how to load 3,000 records in 30 minutes.
If you are nervous about SQLCLR per se then I would work through http://www.sqlservercentral.com/articles/Stairway+Series/104406/. I learned a lot from the series and it put to be some of the myths surrounding SQLCLR.
May 1, 2016 at 12:35 am
David.Poole (4/30/2016)
In terms of SQLCLR calling a web service. Possible but why would you? I have seen the results when a decree from the padded cell of the ivory tower insisted that all interactions with all databases be via web services. RBAR madness ensued and it demonstrated how to load 3,000 records in 30 minutes.
Hi David. "Why use a Web Service?" is a great question. It could be to get a batch of data to work on from a service outside of the company, and the advantage here is that it can be done inline in the import process without needing to call an external app or have an external app scheduled, and then needing to import that data. So it can save a few steps. It can also be used to push data out of the system, possibly logging events outside of a Transaction so they can't get rolled-back. It can also be used to kick-off an asynchronous service.
The key to using a Web Service within SQL Server productively is to not use it for any silly reason, such as the understandably frustrating situation that you ran into. The problem there wasn't Web Services or even SQLCLR, but of silly people making baseless decrees about technology that they don't fully understand, rather than letting the team determine what is best.
And this can happen with any feature or technology. I have been directed to partition tables, no matter how small, because Table Partitioning was being used as a means of managing / helping a multi-tenant system in which each customer had their own Partition_Number. Not only did it not help, it actually hurt the system. We also had a rule that if you needed a "cursor" operation, you had to use a WHILE loop instead of a CURSOR since CURSORs are bad. Problem is, the WHILE loops are actually worse. And I have seen folks use JSON instead of XML because "XML is bloated". And this was on SQL Server 2012, with no built-in support for JSON, and storing that JSON in an NVARCHAR(MAX) field could easily take up more space than the optimized SQL XML datatype.
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
May 1, 2016 at 6:32 am
I'd be curious to know which ports and protocols have to be open in the firewall to allow SQL Server to use web services.
As far as I can tell TCP deals with corruption of the package being sent where as UDP is fire and forget.
You transmit on a port relevant to your service but have to open up ephemeral ports to get an appropriate response. Doesn't this mean that as well as having to open up TCP1433 we have to open up the full ephemeral range 1024 to 65535?
Mechanically I can see that making web service calls direct from SQLServer would be convenient and possibly the most efficient mechanical path. Security wise I'm not so sure
May 1, 2016 at 8:17 am
David.Poole (5/1/2016)
I'd be curious to know which ports and protocols have to be open in the firewall to allow SQL Server to use web services.You transmit on a port relevant to your service but have to open up ephemeral ports to get an appropriate response. Doesn't this mean that as well as having to open up TCP1433 we have to open up the full ephemeral range 1024 to 65535?
Mechanically I can see that making web service calls direct from SQLServer would be convenient and possibly the most efficient mechanical path. Security wise I'm not so sure
There is nothing inherently different about making Web Service calls from CLR hosted in SQL Server vs CLR hosted on the Windows OS. The outgoing calls should be on ports 80 and 443 depending on if you are using HTTP or HTTPS. But there is no listener so no incoming calls. I have never heard of anyone doing any special configuration in order to call an external Web Service via SQLCLR.
The default port for SQL Server native communication, 1433, has nothing to do with Web Service calls.
Security-wise there are no issues as it is outgoing-only.
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
May 1, 2016 at 11:55 am
OK so it is port 80/443/8443 out to the web service. If a client of SQLServer calls SQLServer then ingress is on 1433 but we have to allow egress on 1024 to 65535 may be narrower if it's to a *mix box or recent Windows server
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply