Category Archives: Begin BI

Basic concepts to understand more on Business Intelligence

Search a Text string in whole SQL Server database

Recently I have been asked to merge a new datasource in my current SQL Server solution for client. For example I had data from US in enterprise data warehouse solution, now clients wants to add UK data in same dimension and facts tables.

Now UK database could be in a complete different structure and I will be looking each column to find if Dimension Tax Type data and then do a SELECT DISTINCT Col_Value FROM UK.Table_X. Or I could automate and quickly find all the columns in UK database which hold anything like Tax.

Searching a small text in a whole database could be for multiple reasons:

  • Merging new data source
  • Looking for something in unknown database
  • Understanding different language datasource etc.

So following is the code for same. Use it, utilise it, enhance it, extract whatever good you can from it.

Let me know if you want clarity on any section. I have given good detailed comments for understanding, still comments section is open for any query and feedback.
Inputs are only needed in section 1 (and Section 4 but only if desired).


/*--- Section 1: Declare variables to be provieded for search-----*/
DECLARE @SearchString nvarchar(MAX)
DECLARE @test_tableschema varchar(5)


/*--- Section 2: Provide values for search---*/
SET @SearchString = 'Tax'
SET @test_tableschema = 'dbo'


/*--- Section 3: Start procedure. Declare variable for filtering attributes from database
on which you want to search text---*/
--DECLARE @FilterTable table (TableName varchar(100))
DECLARE @Col_list table (TableName varchar(100),ColName varchar(100), RID INT Identity(1,1))


/*--- Section 4: This part has SQL query to get a list of columns from tables you want to check text in ---
---You can be more creating and filter tables or columns based on name (first two commented lines)
---You can filter columns by columns data types to make sure least count of columns are actually checked
---You can use where clause with T.Table_Name like 'a%' to check only tables with name starting with A
In case of big database we can pick few alphabets to check the data.
---This whole section is for filtering least column (or keep all) from overall column list in database
which in turns help in quick search
*/
--INSERT INTO @FilterTable (TableName)
--VALUES ('UTILICA_forbolag'),('UTILICA_forkategori'),('UTILICA_foromfattning');


INSERT INTO @Col_list(TableName, ColName)
SELECT (T.TABLE_NAME),C.COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS AS C
INNER JOIN INFORMATION_SCHEMA.TABLES T
ON C.TABLE_NAME = T.TABLE_NAME AND C.TABLE_SCHEMA = T.TABLE_SCHEMA AND T.TABLE_TYPE = 'BASE TABLE' AND T.TABLE_SCHEMA = @test_tableschema
--WHERE T.TABLE_NAME in (SELECT DISTINCT TableName from @FilterTable);

/*--- Section 5: No need to change anything below. Declared variables for usage in final query ---*/
DECLARE @test_table varchar(max)
DECLARE @test_columnname varchar(max)
DECLARE @Final_Result table (TableName varchar(100),ColName varchar(100), Found_Flag int, QuickQuery varchar(250))
DECLARE @ROWID INT
DECLARE @found_output INT
DECLARE @Flag_Query nvarchar(max)


SET @ROWID = 1
WHILE Exists (Select RID from @Col_list)
BEGIN
SELECT @test_table = TableName, @test_columnname = ColName FROM @Col_list WHERE RID = @ROWID
SET @Flag_Query = 'SELECT @found_flag = MAX(CASE WHEN '+@test_columnname+' like ''%'+@SearchString+'%'' THEN 1 ELSE 0 END) FROM ' +@test_tableschema +'.'+@test_table +' (nolock)'
EXEC sp_executesql @Flag_Query , N'@found_flag int out', @found_output out
IF @found_output>0
BEGIN
INSERT INTO @Final_Result (TableName, ColName,Found_Flag, QuickQuery)
VALUES(@test_table,@test_columnname,@found_output, 'SELECT DISTINCT '+@test_columnname+ ' FROM '+@test_tableschema +'.'+@test_table+' (nolock)')
END
DELETE FROM @Col_list WHERE RID = @ROWID
SET @ROWID = @ROWID + 1
END
/*--- Section 6: Listing all the TableName, ColumnName found with data and a Select statement for seeing all the distinct values in that column
where search result were found. ---*/
SELECT * FROM @Final_Result;

Advertisements

Missing out on SSAS Data Source Views

We usually don’t give much importance to the Data Source Views (DSV). But if we work on it properly, it makes your life very easy.

Refer to few of the following notes on DSV, which might help in making it more useful for your project.

  1. Its only DSV where you can pull multiple data sources tables. This is the only place where you bring multiple data sources table in 1 DSV and later use in cube. how else ?? ­čśŤ
  2. While creating manual DSV (New Diagram), you can pull one table only and then add related tables one by one (option is Right Click–> Show related tables). So you for example you can literally pull a fact table and call related tables for related dimensions. Also for those dimension call related tables for snow flake tables and in the end get one good quality Diagram without much hassle.
  3. You also get to see original name of column even if you change to Friendly┬á Name for it (doesn’t work for table though). Following is the screenshot of how column name appears after renaming it. Table is also renamed from original DimGeography but only new name appear.
  4. you might want to see only original name, to avoid any confusion with table or column or may be just want to know what came from source. Right Click –> un-check option Show Friendly Names.
  5. You cannot delete a column just by Click–>Delete on DSV. Change the table to Named Query and remove the column from list of SELECT is your option.
  6. DSV always warn you for incorrect relationships. For example, where it should be Many to one or one to many, Matching data type warning etc. Always listed to these warning. This is your practice ground for dimension usage relationships.
  7. Did you use Zoom –> To Fit option ? If not then try it thank me later.
  8. You can also manipulate values in Explore Data. Like select random or top sorted sample. Even increase/decrease the number of sample rows, by default its 5000. To achieve this, Explore Data for a table and in window with sample click on Setting option and manipulate as your choice (Don’t forget to click on refresh button to sample, all highlighted in following screenshot).
  9. If you are new to SSAS DSV or tutoring, use Name Matching Criteria for quick relationships from tables pulled from data source. Click anywhere on DSV and look at properties with NameMatchingCriteria.

I could talk about some more interesting DSV related things, but length of the post is a concern. Let me know if you need details on these or anything more under comments section.