Two handy queries which can help you find things in your database


--Search for a specific string in object-definitions:
SELECT DISTINCT
o.name AS Object_Name
,o.type_desc
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id = o.object_id
WHERE m.definition Like '%SELECT%'

--Find a column within the database:
SELECT
t.name AS TableName,
SCHEMA_NAME(schema_id) AS SchemaName,
c.name AS ColumnName
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%Customer%'
ORDER BY SchemaName, ColumnName









How to: Delete records from your MDS Entity table

Here’s an example script which you could use to delete records from your MDS Entity/table:

Delete records from your MDS entity table

 

Here’s the code:


--Start clean:
TRUNCATE TABLE stg.[Actuals_Leaf_Leaf]

--Retrieve CodeID's:
INSERT INTO stg.[Actuals_Leaf_Leaf] (ImportType,ImportStatus_ID,BatchTag,Code)
SELECT
4 AS ImportType
,0 AS ImportStatus_ID
,'Delete-20160310' AS BatchTag
,Code
FROM mdm.Actuals_Leaf a
WHERE a.Year = 2016
AND a.Month = 2

--Verify:
SELECT * FROM stg.[Actuals_Leaf_Leaf]

--Execute:
EXEC stg.[udp_Actuals_Leaf_Leaf] @VersionName = 'VERSION_1', @LogFlag = 0, @BatchTag = 'Delete-20160310'

--Verify results:
SELECT COUNT(*)
FROM mdm.Actuals_Leaf a
WHERE a.Year = 2016
AND a.Month = 2

--End clean:
TRUNCATE TABLE stg.[Actuals_Leaf_Leaf]

 

More info on: https://clinthuijbers.wordpress.com/2015/11/02/mds-how-to-truncate-an-entity-table/

 


 


 

Waste of digital real estate?

Some of you already know that I fancy Pie Charts. Indeed they are a waste of digital real estate, but it somehow brakes the static look and feel of dashboards with just tables and Line Charts. (Looking forward to the new types in SSRS 2016 btw!)

For now, anyone who would like the recreate this one: http://blog.hoegaerden.be/2009/10/25/pie-chart-techniques/

I do fancy Pie Charts















And what do you think about maps? A nice addition or a waste of digital real estate?

Geo Map - SSRS 2012 2014


















Two dashboards I’ve made for my clients, all of them are extremely happy! 🙂
(Both can be made on SSRS 2008R2, 2012 and 2014)

BumbleBI Dashboard example

 

BumbleBI Dashboard - SSRS 2012 2014