SQL Things

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 sub-folders and repor

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 information about a student which permits a

Conceptual data models are easy to skip.  In a simple business process conceptual models can appear over simplified.  In the case of student retention the business process is very simple on the surface, which cohort does the student belong in and did they come back year over year?  However, I am learning that the conceptual model is a great way to communicate with colleagues who do not understand database theory or technical jargon.  Also, as part of a more complex ecosystem the conceptual model can more easily answer the question "What do we have in our Warehouse?" I put the following conceptual model together using One Note.  I am always tempted to look for tools that make things easier and more repeatable, but in this case just having some simple shapes stops the desire to over complicate the model and get into the implementation details too soon. An easy way to communicate with non-technical stakeholders. Next, if I start to add other subject areas such as stud

I am going to be doing a series of blog posts on data modeling student retention in higher education.  I recently saw a presentation which made me think a lot about data modeling in general and more specifically about student retention.  This series of blog posts will document the process my team is using to determine if the data model I observed is appropriate for our institution. The first step in creating a data model is understanding the business process.  I will start by laying out the rules and vocabulary when it comes to student retention. Student retention in higher education is based on an academic year.  New students starting in Fall are "retained" if they attend school the following Fall.  If a first time in college (FTIC) student attended school for the first time in Fall of 2010 and also attended in the Fall 2011, they were retained. The next important business concept and term is cohort .  For the purpose of student retention a cohort is a group of FTIC s

The first thing I will do is create a System-Versioned  table in SQL Server 2016.  Before creating the table, I will determine if the object already exists and if true I will drop the table. If you have an interest in system-versioned (temporal tables) note the alter table statement before I drop the object. IF OBJECT_ID('WHILELOOPTEST') is not null BEGIN ALTER TABLE  [dbo].[WHILELOOPTEST] SET ( SYSTEM_VERSIONING = OFF  ) DROP TABLE   [dbo].WHILELOOPTEST END Next, I create the system versioned table so we can see the number changes caused by the following WHILE loop. Create TABLE WHILELOOPTEST ( ProductPK int identity(1,1) primary key, Product_Cost money, Start_Date datetime2 generated always as row start hidden NOT NULL , End_Date   datetime2 generated always as row end hidden NOT NULL, PERIOD FOR SYSTEM_TIME(Start_Date, End_Date) ) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.WHILELOOPTESTHISTORY)); /** Insert Test Values  **/ INSERT INTO WHIL

I have been asked many times the difference between a data mart and a data warehouse. I have often heard the terms used interchangeably. But let's face it, we can't all be right all the time. So, below is my break down of data warehouses and data marts. Data Warehouse A data warehouse is simply a database.  The exact kind of database technology you use for business applications can be used for a data warehouse; you can use Oracle or SQL Server.  But stop before going to tell your boss all of your databases are data warehouses.  The difference between your application database and your data warehouse is how the schema is designed and the source of the data.  A data warehouse is specifically designed to make reporting easier and to ensure reporting does not place an undue burden on the transactional source system. Data warehouses can include data from many sources all brought together for convenience.  Note: A data warehouse is not a data graveyard for old data you no longe

This post is a very simple, possibly over simplified, example of conceptual, logical, and physical data models.  The conceptual model includes business objects and the cardinality between the objects.  The logical model includes the cardinality, attributes, and addresses potential primary and foreign keys.  The physical model includes table names, column names, and data types.  The problem with hiding the potential complexity of this process is giving the impression that data  modeling (aka modeling business processes) is easy and fast and therefore trivial.  The modeling process is not trivial.  Devoting adequate time to designing, implementing, testing, and redesigning the data models in a project can be the difference between a impactful project  and having to start over after the business objectives are not met. Conceptual Logical  Physical

Below is a query you can use to find out how much of the pages in the memory buffer pool actually contain data.  SQL Server does not read parts of  8 KB pages into memory, so if the page fill is poor based on poor table and index design, then you may be holding up a bunch of memory with nothing in it. SELECT DB_NAME(DATABASE_ID) Database_Name,         PAGE_TYPE,                 COUNT(DATABASE_ID) / 128 as MB_USED, SUM(cast(FREE_space_in_bytes as bigint))/(1024.0)/(1024.0) as MB_EMPTY, (SUM(cast(FREE_space_in_bytes as bigint))/(1024.0)/(1024.0)/ (COUNT(DATABASE_ID) / 128)) * 100.0 as Percentage_Empty FROM Sys.dm_os_buffer_descriptors WHERE database_ID = --<YOUR DATABASE ID> and page_type in ('data_page', 'index_page') GROUP BY DB_NAME(database_id), page_type; Example Results: The source for the buffer pool data is the DMV  sys.dm_os_buffer_descriptors . The function  DB_NAME()  with take the DATABASE_ID and return the name of the database

I recently discovered our application monitoring tool only looks at the total size of the data and log file.  In our case we had excessive database growth but the monitoring tool only knew how much the data file grew, not how much space the runaway process used up inside of the data file before the data file was forced to expand. Below is a simple query which will allow you to get the current size of the data and log files along with the space still available.  USE <YOUR DATABASE> SELECT DB_NAME() AS Database_Name, NAME FileName, SIZE/128.0 Current_Size_MB, SIZE/128.0 - FILEPROPERTY(NAME, 'SPACEUSED')/128.0 Free_Space_MB, GETDATE() DateTime FROM SYS.DATABASE_FILES; First, use the function DB_NAME()  with no parameter to get the name of the current database. Next, you will need the NAME and SIZE of the files from DMV  SYS.DATABASE_FILES . Name is the name of the file. Size is the number of 8KB pages in the file.  Since a MB is 1024 KB, you divide the nu

I recently inherited some administration duties for a SQL Server instance.  The database was growing out of control and performing poorly.  The following is what I learned while trying to improve the state of the indexes. The primary application database is ~ 85 GB in size and of that total amount there is a single table which is ~34 GB.  The single table includes an additional ~6 GB of indexes.  The total space on the data drive was ~90 GB. Needless to say, the performance of the application depends entirely on the performance of that single table.  The rules were, I could not add, delete, or disable any of the indexes and I had zero control over the design the database.  It was a vendor product and any manipulation at that level would have violated our licensing and support agreement. There were two goals in de-fragmenting the indexes: 1) Improve Performance and 2) Reclaim misused disk space. Number 1: If you have the licensing for Enterprise Edition, use it.  SQL Serve