SQL Things

We recently found a view in our warehouse which had only been created for demonstration purposes but the underlying tables had changed over time.  The view was broken and, of course, someone found it.  This necessitated a remediation which would identify broken views and create a record of the issue.  Ideally, there would never be broken views if this process was in place so we could also create a record showing how few issues we have had.   At first, I thought of creating a SQL Agent job which would query the view and fail when the view was not working. But this seemed like a lot of work as views got added over time.  So, something more flexible was called for. I wanted to create a procedure that would test all of the views in a database and record the relevant data if the view broke.  I created a procedure with a try-catch block and selected the top(1) record from the views and when there was a failure the catch block would insert the record...

STRING_AGG is a function released in SQL Server 2017.   The Oracle equivalent LIST_AGG has been available since 11gR2.  In the meantime, SQL Server users have resorted to black magic in order to get string values from separate rows. STRING_AGG is very welcome but there are some gotchas to be aware of.  Below is a simple example that works just like you think it would. SELECT STRING_AGG(FIrst_Name, ', ') WITHIN GROUP (ORDER BY First_Name) LIST FROM             (SELECT DISTINCT   First_name FROM [dbo].[Employee]     WHERE First_Name like 'A%' OR First_Name like 'B%') A ORDER BY LIST; You can run the query on any database you have string values and you will get something like this: A David, Aaren, Aaron, Abdul, Bob, Brian... If you want to split by the first letter of the name, do not make the mistake of using an over clause.  You will get an error stating STRING_AGG is not a window function....

We recently upgraded to Power BI Report Server on premises.  We have a directory name "President's Office".  While still on SSRS there was no issue with the name of the directory.  After upgrading we received an error when clicking on the ellipsis in the upper right corner of the folder.  The underlying reports would run but we could not manage them.  The error persisted for all sub-folders and reports.  The error did not provide much in the way of details: An error has occurred. Something went wrong. Please try again later. I cracked open developer tools in Chrome to see what was happening when we clicked on the ellipsis.  When clicking on the ellipsis it made an API call which resulted in a 404 error. The API call looked like this: reports/api/v2.0/Folders(Path=%27/President's%20Office%27)/AllowedActions Look closely and you will notice the apostrophe in the directory name is not encoded.  This also explains why all of the ...

On a recent tabular "dashboard" with several bar charts in a small area our team found vertical positioning of the labels to be troublesome.  When the labels were set to "bottom" and the bar was too short the label would truncate the significant digits.  On the other hand, when the labels were set to "top" the numbers would be truncated if the column was too tall. Example of bar chart with label set to bottom. Same chart with the middle column label set to top. First we thought about using Smart Labels  but we quickly found that smart labels are not so smart when the labels are turned 90 degrees to make them vertical.  As you can see in the examples above the size of the numbers would make horizontal labels messy. So the obvious choice was to use a function to place the label on top when the bar was small and on the bottom once the bar was tall enough. In Report Builder, go to your chart and select a specific column - not just the chart....

I recently had a case where I wanted to get a regular academic term n terms from a given date.  For example if I was in the Fall Term of 2018, I wanted to be able to get the term 2 terms before which would be Spring 2018. The problem is we use term codes, which have changed formatting over the years, and we also have partial terms which I didn't want to consider.  The data ends up looking something like this: TermSK TermCode 1 201780 6 201810 17 201850     SELECT ROW_NUMBER() over(order by term_code desc) Row_Num, Term_Code     FROM dimTerm     WHERE Term_Type =  'Regular' Row_Num TermCode 3 201780 2 201810 1 201850 The results made it really easy to grab any term preceding by simply subtracting the desired previous terms from the generated Row_Num.

I am going to do a quick dimensional modeling primer before using the conceptual model to create a logical model.  Dimensional modeling creates a star schema comprised of two basic types of tables: Fact Tables - these tables contain measures and keys.  The measures are the facts.  If I am counting students then the fact may be as simple as using the value 1 to indicate the existence of registration for a specific term.  The measures can be additive, semi-additive, or non-additive.  Additive measures can be easily summed.  Semi-Additive measures like account balances for example would mean nothing if summed.  Non-additive facts such as GPAs may need to be broken down into their parts (IE individual grades).  The fact table will be the center of the "star". Dimension Tables - these tables are filters or slicers you will use to aggregate or refine your data sets.  For example, there may be a student dimension table which includes informatio...