-
Capturing user activity in the database using SQL Server Auditing functionality
SQL Server comes with a build in audit functionality that saves a lot of development effort when one of the business requirements states is to keep a record of user activity in the database. For each query executed in the database, SQL Server Audit captures the names of the affected tables, SQL statement used for…
-
Vertical and horizontal lines or strips on Line Chart in SSRS
How do you show a vertical or horizontal line on a line chart in SSRS? After hours or research it seems that approach involving StripLines works the best. Horizontal or vertical likes can be used on the chart to indicate baselines or highlight particular sections as on the sample below: So, how do you build…
-
Scripting table data
Every now and then I need to create a SQL script to move table data between environments or to simply load table during database deployment process. For a number of years I am using the stored procedure created by Narayana Vyas Kondreddi (http://vyaskn.tripod.com). This is a great tool for scripting data without any problems! Attached…
-
Small cell value suppression in SSAS cube output
I am currently working on project building information system for one of the provincial health registries. Patient data protection and privacy is one of the main requirements that touched all parts of the information system including standard reports and SSAS cubes. One of the requirements I’ve encountered while working with SSAS cubes was that measure…
-
The importance of data type for averaging
Here is a small cautionary tale for those, who use Average function in SQL Server. The data type of the aggregated data element greatly influences the result of AVG aggregate function.With source data set defined as INT, the result of the straight…
-
Database recovery procedure for corrupted databases
Follow the following procedure to recover corrupter SQL Server database. It is assumed that database is in full recovery mode, full database backup is available and transaction log backups was taken sometime before the failure.If database has been corr…
-
Quick and simple aggregation of Hierarchical Data in SQL Server
Sometimes data containing hierarchies have to be aggregated and total calculated on different levels. The following script will help archive fast calculation of totals on each level of hierarchy.INPUT DATA STRUCTURE AND VALUES0100***Level 1*****$210110…
-
Statistical function in T-SQL
In my current project I need to calculate mean, mode, median and percentiles (95th percentile, 99th percentile) of a given data set.In this article I’ve put together a summary of what I’ve discovered during my research.The following code snippets …
-
Script to de-normalize Parent – Child Hierarchy
Parent Child data hierarchies are great for storing hierarchical data, however it becomes a major pain when you need to convert hierarchy structure into a flat view (e.g. to be used in reporting or SSAS cubes). While SSAS provide a build…
-
Use sp_replrestart to fix “The process could not execute ‘sp_repldone/sp_replcounters’” error
This error is generally raised by the log reader agent while running transactional replications. The error indicates that distributor and subscribe databases has data that may be more recent than data in the publisher database (the agent compares lates…
Toronto, Ontario, Canada
Eastern Time Zone
info@maximconsulting.ca
Author: Max
Archive
Categorise
- aggregation
- audit
- average
- backup
- baseline
- cell value overwrite
- compliance
- cube
- data driven subscriptions
- data export
- datetime
- de-normalize
- graph
- hierarchy
- local time
- MDX
- parent-child
- recovery
- restore
- rounding
- script table data
- Small cell value suppression
- ssas
- ssrs
- statistical functions
- stipline
- Uncategorized
- UTC
