Generating audit information from analysis services (SSAS) was just a setting on the olap database(multidimensional), but in tabular mode it doesn't seem to work.
The way to go as I understand is to use the ASTrace tool: https://sqlsrvanalysissrvcs.codeplex.com/
It's basically a sql profiler running as a service, saving the result to a database.
I good idea is to customize the trace template, otherwise you'll be flooded. Do this using SQL Profiler, create template, adjust the events you capture and export the template file. The QueryEnd event could be a start. Use the config file and restart the service.
my Business Intelligence notes
tisdag 8 april 2014
fredag 13 december 2013
Predictive analysis (churn scoring) using Microsoft SQL server !
Predictive analysis using Microsoft SQL server in action! Churn scoring on a Swedish telecom Company we did using the standard data mining Tools within SQL Server at Random Forest AB.
Want to know more? Don't hesistate to get in touch!
In swedish
http://www.mynewsdesk.com/se/microsoft/pressreleases/bi-loesning-i-sql-server-ger-boxer-lojala-kunder-940835
Google Translate
http://translate.google.com/translate?sl=sv&tl=en&js=n&prev=_t&hl=sv&ie=UTF-8&u=http%3A%2F%2Fwww.mynewsdesk.com%2Fse%2Fmicrosoft%2Fpressreleases%2Fbi-loesning-i-sql-server-ger-boxer-lojala-kunder-940835&act=url
Want to know more? Don't hesistate to get in touch!
In swedish
http://www.mynewsdesk.com/se/microsoft/pressreleases/bi-loesning-i-sql-server-ger-boxer-lojala-kunder-940835
Google Translate
http://translate.google.com/translate?sl=sv&tl=en&js=n&prev=_t&hl=sv&ie=UTF-8&u=http%3A%2F%2Fwww.mynewsdesk.com%2Fse%2Fmicrosoft%2Fpressreleases%2Fbi-loesning-i-sql-server-ger-boxer-lojala-kunder-940835&act=url
måndag 28 oktober 2013
select max from multiple columns
I have a view reading from three tables: A, B and C. Each table has a Created timestamp. I want the view to show the latest timestamp in any of these three tables, to be able to find new rows since a certain date, even if it is just a new row in one of the tables.
I was about to do a case or a function, but found this little simple method.
This snippet returns 3 with column name of "value"
SELECT Max(v)
FROM ( VALUES (1), (2), (3) ) AS value(v)
Select Col1, Col2,
(
SELECT Max(v)
FROM ( VALUES (a.Created), (b.Created), (c.Created) ) AS value(v)
) as newDataCreated
From A
inner join B
on a.id = b.a_id
inner join C
on b.id = c.b_id
Works in SQL Server 2008 and newer versions.
I was about to do a case or a function, but found this little simple method.
This snippet returns 3 with column name of "value"
SELECT Max(v)
FROM ( VALUES (1), (2), (3) ) AS value(v)
Select Col1, Col2,
(
SELECT Max(v)
FROM ( VALUES (a.Created), (b.Created), (c.Created) ) AS value(v)
) as newDataCreated
From A
inner join B
on a.id = b.a_id
inner join C
on b.id = c.b_id
Works in SQL Server 2008 and newer versions.
måndag 23 september 2013
Predictive analysis using SQL Server 2012
It's amazing what you can do with the data mining tools within SQL Server 2012. This is something we've done for a client that we're proud of.
Don't hesitate to get in touch if you want to know more on the topic.
Random Forest customer evidence, data mining using SQL server 2012.
In Swedish:
http://www.microsoft.com/sv-se/kundreferenser/Microsoft-SQL-Server/Boxer-TV-Access-AB-NAV/Lojalare-kunder-med-Boxers-BI-l-sning/710000003248
In English (google translate):
http://translate.google.se/translate?sl=sv&tl=en&js=n&prev=_t&hl=sv&ie=UTF-8&u=http%3A%2F%2Fwww.microsoft.com%2Fsv-se%2Fkundreferenser%2FMicrosoft-SQL-Server%2FBoxer-TV-Access-AB-NAV%2FLojalare-kunder-med-Boxers-BI-l-sning%2F710000003248
onsdag 28 augusti 2013
Force prints to display
I you wan't to force print statements to display continuously when executing script in management studio, insert the following statement. SQL server do not treat severity 1-10 as an error, just a message.
RAISERROR ('Push the message!', 0, 1) WITH NOWAIT
torsdag 22 augusti 2013
T-SQl Query performance
Just some short notes on what to be aware of when experiencing bad performance.
- Table Scans for sure, but also be aware of key-lookups. Those normally can be avoided be including the lookup column in the index. This is in my experience one of the more common opportunities to improve performance and not that obvious to spot.
- Table compression actually increases Query performance, but slows inserts down a bit. Generally if you have big tables, use table compression
- If using a view as a source in a merge statement, you can save a lot of time by doing it in two steps, first store the result of the view in a temporary table and then do the merge with the temporary table as source.
- A left join can in some cases be faster than an inner join. Strange but true.
måndag 1 oktober 2012
T-sql toggle bit data type
Even after ten years in sql-server world, you can still find something you didn't know..
I have a field in a table called IsTrue of the type bit. I just want to toggle it, if it's true, set to false and if it's false, set to true.
I was on my way to do a case statement when I realized there is at least to better ways of doing this.
the bitwise NOT operator way
update table set IsTrue = ~IsTrue
the bitwise XOR operator way:
update table set IsTrue = IsTrue^1
found at:
http://johnnycoder.com/blog/2006/10/04/t-sql-tip-flip-bit/
Can't believe I didn't know you could do that in SQL Server... =)
I have a field in a table called IsTrue of the type bit. I just want to toggle it, if it's true, set to false and if it's false, set to true.
I was on my way to do a case statement when I realized there is at least to better ways of doing this.
the bitwise NOT operator way
update table set IsTrue = ~IsTrue
the bitwise XOR operator way:
update table set IsTrue = IsTrue^1
found at:
http://johnnycoder.com/blog/2006/10/04/t-sql-tip-flip-bit/
Can't believe I didn't know you could do that in SQL Server... =)
Prenumerera på:
Inlägg (Atom)