Restoring objects in SQL Server

This is a code googled in panic after occasional deletion of a stored procedure.

wordpress__recovery

Does real magic saving hours of work that could be spent on re-writing:

DECLARE
@Database_Name NVARCHAR(MAX) = 'SecretDbName',
@Date_From DATETIME = '21 Oct 2015',
@Date_To DATETIME = GETDATE()

DECLARE @Compatibility_Level INT

SELECT @Compatibility_Level=dtb.compatibility_level
FROM master.sys.databases AS dtb WHERE dtb.name=@Database_Name

IF ISNULL(@Compatibility_Level,0) <= 80 BEGIN
 RAISERROR('The compatibility level should be equal to or greater SQL SERVER 2005 (90)',16,1)
 RETURN
END

SELECT
[Database Name],
Convert(varchar(Max),
Substring([RowLog Contents 0],33,LEN([RowLog Contents 0]))) as [Script]
FROM fn_dblog(NULL,NULL)
WHERE [Operation]='LOP_DELETE_ROWS' AND [Context]='LCX_MARK_AS_GHOST'
AND [AllocUnitName]='sys.sysobjvalues.clst'
AND [TRANSACTION ID] IN
(SELECT DISTINCT [TRANSACTION ID] FROM sys.fn_dblog(NULL, NULL)
 WHERE Context IN ('LCX_NULL') AND Operation in ('LOP_BEGIN_XACT')
 AND [Transaction Name]='DROPOBJ'
 AND CONVERT(NVARCHAR(11), [Begin Time]) BETWEEN @Date_From AND @Date_To)
 AND Substring([RowLog Contents 0], 33, LEN([RowLog Contents 0]))<>0

 

Advertisements
Posted in Administration, programming, Uncategorized | Tagged , , | Leave a comment

Governed Data Discovery vs BI

Governed Data Discovery vs BI:

Is there the best strategy?

wordpress__two-direction-arrow-sign

Governed Data Discovery is one of the trending buzzword since 2014.

The term made its debut in Gartner’s 2014 Magic Quadrant Report. Gartner first popularized the term data discovery and now is adding governed to the term.

Today’s Problem

In many cases even successfully realised Business Intelligence projects contribute company’s IT capability but don’t increase business capability. This happens mainly because resulting analytics doesn’t match the context of a business or simply saying reports and dashboards don’t answer business questions. Knowledge is presented the way wisdom cannot be extracted from it.

The GDD Solution

Governed Data Discovery – the ability to meet the dual demands of enterprise IT and business users” – asserts Gartner.

GDD pretty much differs to BI. There are key points describing their major characteristics:

BI Process

  • Defining business goals metrics
  • Analyzing business processes
  • Identifying operational data
  • Define business logic and rules
  • Define mapping and quality rules

Analysis -> Design -> Develop

  • Reports: Define what happened
  • OLAP: Defined metrics/dimensions
  • Dashboards: Defined metrics/goals
  • Scorecards: Defined KPIs/variance

Governed Discovery Process

  • Clear business goal (mission)
  • Inherent business knowledge
  • Access data and work with context
  • Ability to iterate analytic models
  • Ability to share and verify findings

Discover -> Verify -> Govern

  • Discovered context to governed
  • Discovered analytic models
  • Data shows how the business works
  • Data shows how the market works

Realisation

To be a true Governed Data Discovery solution, the BI platform needs to offer four main components:

1) Self-Service Central Control

A built-in, self-service, and centralized administrative toolset to govern an organization’s BI. This robust and extensive administrative backend is required to provide the capability for managing every user’s experience, security, content, and data access from a singular, intuitive interface. PowerBI is a good approximation to a platform that can support GDD solution.

2) Data Governance

The data needs to remain centralised so to have one version of truth for data.

A Data Lineage capability that tracks the lifecycle of the model is also highly recommended especially in large-scale projects.

3) The Content Lifecycle

Content repository in a centralized, shared paradigm – that also tracks the content life-cycle is needed. This ensures content integrity and makes it easy to find and implement any changes or upgrades.

4) Secure & Protected

A strong security model is vital so that the data is not only secure from the external source, but also kept confidential internally.

Approaches

All approaches in data management can be split up into 4 cases:

wordpress_gdd

Despite theoretically good results are achievable in all the cases some of them seem much harder for realisation than others. It’s barely possible to imagine that Big Data (see on right-bottom variant) can be provided to end-users without cleaning and basic transformation, that obviously involves Semantic Integration factually adding context it. And realisation this way moves from variant in right-bottom corner to one in right-top.

For GDD the most realisable variant is a combination of well-prepared data (level of “information”) and full flexibility for end users to extract “wisdom” from it:

wordpress_wisdom_info_pyramid

Concluding, there is no way in real world to avoid structuring data before delivering it to the business for decision support. The only choice is whether to do it rigid way (BI) or flexible (GDD).

Source1, Source2

Posted in Big Data, Business Capability, Business Delivery, Data to Knowledge | Tagged , , , , , , | Leave a comment

Smart Data – Extracting Quality From Quantity

Amit Sheth defined “Smart Data” as “realising productivity, efficiency, and effectiveness gains by using semantics to transform raw data into Smart Data.”

Clarifying “Smart Data” concept can be understood as automation of the process represented by the arrow linking “Information” and “Knowledge” entities on the classic diagram:

Semantic and Knowledge

Smart Data provides value from harnessing the challenges posed by volume, velocity, variety, and veracity of big data, and in turn providing actionable information and improving decision making.  It is about extracting value by improving human involvement in data creation, processing, and consumption, resulting in enhanced human experience.

Continue reading

Posted in Big Data, Data to Knowledge, Smart Data | Tagged , , , , , , | Leave a comment

Querying Tabular model XML/A with XPath

There can be various reasons to extract metadata from Tabular model, for example to compose documentation or logical model diagram. The next collections are useful from architectural point of view:

  • list of dimensions,
  • list of measure groups and measures,
  • list of calculations with their formulas,
  • list of hierarchies.

There are a lot of utilities allowing to browse XML-scripts and efficiently query them. However most likely Tabular developer has SQL Server Management Studio under hand and usage of XQuery looks convenient way to go.

logo_xquery_xpath

Continue reading

Posted in programming, Uncategorized | Tagged , , , , , , , | Leave a comment

From SQL to NoSQL to NewSQL

History of RDBMS

Every time new technology emerged it’s evolution ended up in realisation as relational system (RDBMS). In other words, the business before adopting the stuff always demanded atomicity, consistency, isolation, and durability (ACID).

data_storage_evolution

Continue reading

Posted in Big Data, Business Capability, Business Delivery, R&D, Uncategorized | Tagged , , , , , , , , | 1 Comment

SQL or NoSQL: that is the question.

Relational database management system (RDBMS) have been a primary data storage mechanism for decades. NoSQL databases have existed since the 1960s, but have been recently gaining traction and the business faces a challenge of their efficient adoption.

rdbms_nosql

There are many tutorials explaining how to use a particular flavor of SQL or NoSQL, but few discuss why you should choose one in preference to the other (“SQL or NoSQL – that is the question”). Hope to answer the tough question here covering the fundamental differences in business capabilities. Here should be noted that since the author is Microsoft fan all the next is written keeping in mind Azure environment with such a products as Azure SQL Database, Azure Parallel Data Warehouse, Azure Data Lake Analytics and Azure Spark on HDInsight.

Continue reading

Posted in Big Data, Business Capability, Uncategorized | Tagged , , , , , , , , , , | Leave a comment

Azure PDW is generally available

Microsoft released Azure SQL Data Warehouse accompanying  with a bouquet of impressive distinct capabilities that can be found in the press-release.

An item attracted my attention was the next:

Continue reading

Posted in Big Data, Business Capability, Business Delivery | Tagged , , , , , , | Leave a comment