Traditional BI architecture and data latency issues

In BI world, the traditional analytics model take more effort and cost to implement as compared to recent trends in the analytics world.

Traditional BI Architecture

Now the issues with this architecture is that :

  • Data has to be sourced
  • ETL has to be performed to create DW or datamaet
  • Create OLAP/Cubes
  • Then able to pull the data for reporting purposes.
  • No real time analytics and clearly there is data latency

Hence the recent trend is shifting to give the power to business owners to have the ability to slice and dice the data using power query as information workers ETL tool. Then surface the reports using power view or power maps.

Traditional BI Architecture 2

SSRS – Sys.WebForms.PageRequestManagerServerErrorException: An unknown error occurred while processing the request on the server. The status code returned from the server was: 400

Recently stumbled upon this error when viewing SSRS reports in native mode.

Sys.WebForms.PageRequestManagerServerErrorException: An unknown error occurred while processing the request on the server. The status code returned from the server was: 400

Fix is to modify web.config in report manager and report server folders.

</system.web>
<appSettings>
<add key=”aspnet:IgnoreFormActionAttribute” value=”true” />
<add key=”aspnet:MaxHttpCollectionKeys” value=”100000″ />
</appSettings>
<runtime>

 

 

Quickly find out the service accounts SQL services

TSQL query to find out the if the SQL services are started or stopped, accounts running them etc.,

(Tested in SQL 2012 and above)

1
select servicename, startup_type_desc, status_desc, last_startup_time, service_account, filename from sys.dm_server_services

Check SQL Server and SharePoint Version Build Dates

Whenever you would like to know the version of SQL server, there are couple of ways :

  • TSQL Command
1
SELECT @@VERSION
  • SQL_version_4
  • SSMS GUI

SQL_version

  • TSQL command
  • 1
    XP_READERRORLOG

SQL_version_2

  • TSQL command
1
2
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
xp_msver
  • SQL_version_3

Then you can check if the SQL server needs any SP updates using below table (source in hyperlink). Feel free to check for SQL & Sharepoint updated build numbers.

SQL_Server_build_numbers

 

SQL_Server_2016_build_numbers

CREATE FILE encountered operating system error 5 (failed to retrieve text for this error. Reason 15105) while attempting to open or create the physical file

After rebuilding the system databases, I was trying to restore the MDF files and received this error. It tells that it isnt able to access the MDF file. To confirm this I have verified and re-verified all the permissions running the SSMS to have full control on the DATA folder containing MDF files. Still it continued to raise error.

SSMS_as_Admin_1

It is a windows permission issue. If it is an instance, give permissions to the MSSQL$ instance account full permissions on the data folder.

Or the brute force method is to run SSMS as administrator.

Fetch meta data information of collation settings

Use below system function to get the context of collation settings on SQL server.

1
2
3
4
5
SELECT COLLATIONPROPERTY('<collation>', 'CodePage')

SELECT * FROM fn_helpcollations()
WHERE name = 'SQL_Latin1_General_CP1_CI_AS'
OR name = 'Latin1_General_CI_AS'

Verify collation of all system databases as compared to user database

Use below script to check the collation setting at system database level as compared to the user database.

1
2
3
4
5
6
7
print 'database [' + db_name() + '] collation is: ' + CAST( DATABASEPROPERTYEX ( db_name(), N'Collation' ) AS VARCHAR(128) )
print '--------------'
print 'tempdb database collation is: ' + CAST( DATABASEPROPERTYEX ( 'tempdb', N'Collation' ) AS VARCHAR(128) )
print 'master database collation is: ' + CAST( DATABASEPROPERTYEX ( 'master', N'Collation' ) AS VARCHAR(128) )
print 'model database collation is: ' + CAST( DATABASEPROPERTYEX ( 'model', N'Collation' ) AS VARCHAR(128) )
print 'msdb database collation is: ' + CAST( DATABASEPROPERTYEX ( 'msdb', N'Collation' ) AS VARCHAR(128) )
print '--------------'

Cannot resolve the collation conflict between SQL_Latin1_General_CP1_CI_AS and SQL_Latin1_General_CP1_CI_AS in the equal to operation

In one of the reports in BIDS, I was recently facing this issue

<pre>Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in the equal to operation. (Microsoft SQL Server, Error: 468) </pre>

 

Collation_Conflict_Error

Similar error in another instance from SSMS:

Collation_Conflict_Error_2

It is clearly talking about the mismatch of collation. I was using temporary table in the stored procedure that was sourcing the dataset for the report. Now tempDB was of different collation than the user database.

Collation is granulated in SQL server at different level :

  • Server
  • Database
  • Table
  • Column

Easy fix is to use COLLATE DATABASE_DEFAULT to default the collation of joining columns. But making the code change in all procedures where temp tables were used seemed too daunting. So the best option is to bring the collation of tempDB same as User database (same as server collation). I didnt have a specific need to have my database in a specific collation luckily, so the challenge was much easy.

It is also possible that collation change on the database happened much more recently. Then it is even a bigger problem because all new DB objects will be created with the new collation where as the existing tables remain in older collation. So whenever a join is issued against old and new columns, collation conflict will certainly occur. The best option suggested in this scenario is to migrate the entire database using SSIS packages or BULK Insert or BCP commands. Use below query to find out if few tables are in different collation as compared to others :

1
2
3
4
5
6
7
8
USE master
GO
SELECT DISTINCT c.collation_name
FROM sys.COLUMNS c INNER JOIN sys.TABLES t
ON t.object_id = c.object_id
WHERE c.object_id
IN (SELECT object_id FROM sys.objects WHERE TYPE = 'U')
AND c.collation_name != 'NULL'

I had rebuilt system databases on the SQL Server to have the same collation as user database to fix the issue and I will talk about in future posts.

Data Viz Principles

Viz_1Viz_2Viz_23Viz_4Viz_5

Cannot alter the database model because it is a system Database

I had collation mismatch issue between tempDB and a user database. I wanted to change the collation of model database as it has template for tempDB after every restart of SQL Server. So I issued :

ALTER DATABASE model COLLATE SQL_Latin1_General_CP1_CI_AS ;

model_alter_failureIt was natural to expect that model being a system database has different configuration as compared to user databases. The best option in this case is to follow these steps :

  • Verify if you can get hold of another SQL Server with same Major version and minor version i.e Compatibility mode along with SP, CU, etc., If the the other server is lower version than upgrade to the same version as destination. If the server version is different (including SP) then it will throw this error

model_restore_error

  • Back up the model database in source
  • Restore in destination (if the database complains that model is in use then kill all user connections )