February 27, 2009 at 8:01 am
I have tables, that store processed data from other different sources (not SQL Server and maybe not database)
Now i update this tables periodicaly by jobs, that runs stored procedures.
I'm looking for some solution for realtime data-processign and aggregation.
In perfect case i call just "select top(100) * from table"
and some code will be executed before select.
Finally i will have actual data from this select.
i'm trying to use UDF, but i can't call my stored proc in this UDP.
In some places i can use stored procedures with SELECT statement in,
but it isn't comfortable and many code must be rewrited to use it solution.
February 27, 2009 at 11:11 am
KPC, I fail to understand what your issues are. You will need to be bit more specific for us to help you. What is the perfect scenario? What are the issues you are facing?
-Roy
February 27, 2009 at 11:16 am
it sounds like if someone runs a SELECT command, you want to make sure you have the "latest and greatest" data from remote servers/locations, is that right?
instead of running yourstored procedure or other process to copy the data fom remote locations into your local tables, why not create VIEWs that point to linked servers and get the data on demand directly from those sources?
do you REALLY need the data locally so you can UPPDATe COPIES of the data, or is this just for read only/SELECT commands? or do you really transform the data a lot in your process, or can that be done automatically by using VIEWS and case statements..(CASE STATUS=1 THEN 'OPEN' , etc)
Lowell
February 27, 2009 at 11:21 am
the other important question is, how often does the remote data change? what is the time tolerence for the application(is it bad that the data is 1 min/10 min/1 day out of date?)
you could schedule a job to run your procedure, so if it was schedule to run, say every hour, that your local data would be at most 1 hour behind the remote data.
Lowell
February 28, 2009 at 3:12 pm
Yes, i'm really need to stored it localy, becouse that data will be used by MSAS for example, or by reporting service.
But before this data can be used it must be prepared and processed by my SP's.
Remote data changes and updates every second, and the main problem in fact, that structure of remote data may be changed.
(table columns or any data in tables)
Now i'm using procedures for update and processing tasks. Also i have views, but using that views take tonns of time.
View used with cache tables (insert into view_table from view where id > @last_stored_id)
Remote data stored on MySQL server and in files (log files for example).
Lowell, yes you right.
I want to be sure i have the "latest and greatest" data from remote servers/locations.
Now it works like this:
rem. datas -> SP updates -> SP process -> views -> OLAP/report/other
In perfect scenario i want to run all this chain or part when SELECT called.
February 28, 2009 at 4:11 pm
ok a few ideas;
first to be clear, there is no way to update the data "before" a select statement gets called.
While I know it's possible for the data to be updated every second, the data that is relevant to your report must have a tolerance to old data...say 10 minutes behind is not a "bad thing" for example. and of course the data probably does not have to be as "up to date" at 2am as it does at 9am.
1. views of linked servers are slow, but that's the only way to get true up to the moment data.
2. a stored procedure which returns a dataset, and runs your existing update procedure AND runs the select statement is possible...but how long does the update take? probably just as long as a remote view. when you run this process, ow long does it take? sub-one second?:
rem. datas -> SP updates -> SP process -> views -> OLAP/report/other
3.My other suggestion, running a job in SQL Agent to get the changes every 1min/10min/hour or whatever is appropriate is probably the best solution.
if you've already automated the migration via stored procedures, I would simply create a jon to call that stored procedure(s?)
it's simple to do in
Lowell
March 1, 2009 at 3:05 am
Hi,
theres a free tool you can use if you want to know of data changes that occur over time, its called data profiler.
If your data is on different kind of database (MySQL, Excel,text or whatever) you can use another query tool that can track changes, but this one is not for free.. 😉
--------
SQL Data compare , Synchronize data from diffrent surces, and many other SQL tools[/url].
March 2, 2009 at 7:18 am
"instead of select" will be perfect =)
But SQL Server don't have this option.
Now i'm upgrading data every hour by SQL agent.
It's running SP, than caching views.
All be OK, but now other subsystems need to take realtime data from OLAP.
Reporting service allows SP to use as data source, but not MSAS.
MSAS accepts only tables, querys (with select) and views.
Maybe exists some solution in MSAS for example to call some stmnt before processing.
Or in MSIS (now i'm using linked servers and SP's)
Any solutions with CLR will be nice.
(can i call SP's or other code from CLR UDF ?)
Views to linked servers fails on data convertation.
(it's put numeric(20,0) instead bigint, it is unacceptable for foreign keys)
rem. datas -> SP updates -> SP process -> views -> OLAP/report/other
This process takes about 5 seconds.
itamar (3/1/2009)
Hi,theres a free tool you can use if you want to know of data changes that occur over time, its called data profiler.
This tool very unstable, i can't use it.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply