SIMAH Ramadan Gathering

Its a nice gathering arranged by #SIMAH, to share those wonderful memories, below are some my clicks. Feel to post your comments and feedback.

See the full collection below..

Continue reading

Advertisements

SQL Server column-store clustered index not a magic stick

SQL Server has many new features added on the recent versions starting from 2014, most of the time people decide on the features without having a clear understanding on the internals. Columnstore cluster index is one among those, will discuss in detail in this post.

Microsoft claims “Using Columnstore clustered index it can load 600GB/hour on 16 core machine using 16 concurrent bulk load jobs running one per core, targeting the same table

Many misguided by the above statement that column store index is ideal for most other cases, since its capable of loading 600GB/per hour , the same can handle the small data as well. Which is totally wrong.

When it comes to performance tuning there is no one size fits all.

On a traditional indexes, data stored as rows, all columns stored together in a page which is ideal for OLTP loads.

With Columnstore indexes the data stored as columns, since the whole columns is stored together results in better compression e:g think of a state name column in the fact table, more the repeated value better the compression it gets.

Lets get into the details on how the data is stored into the column store indexes.

Continue reading

Hand coding SQL for Data integration is a pain.

Hand coding SQL for Data integration is a pain. Most of the time it starts with a small requirements and grow with the business needs, later it will become a black box. It’s a good article from talend. Would like to explore it on a ETL perspective, if someone having it already may feel free to post your experience with Talend…data-integration-with-wealthport

https://www.talend.com/blog/2017/04/19/hand-coding-sql-data-integration-case-study/

For those who are using SQL Server SSIS is good option as well, to start with. Hope Microsoft will improve it as enterprise level ETL tool.

#DataIntegration

SQL Server reporting services, who execute what and when

SQL Server Reporting Services, a wide spread tool used by many enterprise level companies and organisation to deliver the data in a rich format. Even though it has its own folder level privileges to secure the information from an unwanted teams, it is not enough when it comes to some sensitive data.

There could be a requirement from an audit and compliance perspective , like below.

Ensuring the data viewed by the correct people and in the correct time, is also  a part of Data Governance.

The user should execute the report only when the proper request comes from the management, and also he should run the report against the requested parameter. The audit department may ask the data custodians to populate a report to ensure this…

The below query can be executed on the report server database on which your reports are deployed.

SELECT [LogEntryId],[InstanceName]
,[y.Name] as ReportName
,[UserName],[Format]
,[Parameters],[TimeStart]
,[TimeEnd],[TimeDataRetrieval]
,[TimeProcessing],[TimeRendering]
,[Status],[ByteCount]
,[RowCount],[AdditionalInfo]
FROM [ReportServer].[dbo].[ExecutionLogStorage] x
inner join Catalog y on x.[ReportID] = y.ItemID

Hope the information is useful for you.