Ajan's BI Blog

Excel source can contain a table anywhere within a sheet, but for each table there is a starting cell and an end cell. In SSIS Excel SQL Command we can precisely select this table by defining those two cells. Consider the following scenario where the table within an excel sheet starts from the cell W7 and goes till AJ38 For this purpose, we can execute the SQL command option in SSIS and let’s discuss more on this. First of all, in a table within an excel sheet following are the parameters to be known Sheet Name - Monthly SSC Report$ (Sheet name can be noticed in the normal selection pane) Starting Cell - W7 Ending Cell - AJ38 Using the above information, we can form the following SQL command for Excel and extract the data, once it is extracted we can define column names manually and automate it but if there is a scenario where there is more data than the range specified in future it will not be extracted so it is better to have have the ending cell as far down as possi...

  You might have an SQL Server database with lot of unnecessary tables. But you are unable to remove them because you don't know if it is still being used or not. Sometimes they have been created as a backup and not deleted so now it's there as a redundant and taking up your space. In order to identify such tables the below query comes in handy. You can check the Last Interaction date and the table size to determine what to do. As a best practice it is better to ha ve a last updated date as a column in tables to identify the current relevant tables. S ELECT   last_user_scan LastInteraction,   t.NAME AS TableName,   s.Name AS SchemaName,   p.rows AS RowsCount,   SUM(a.total_pages) * 8 AS TotalSpaceKB,   CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS numeric(36, 2)) AS TotalSpaceMB,   SUM(a.used_pages) * 8 AS UsedSpaceKB,   CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS numeric(36, 2)) AS UsedSpaceMB,   (SUM(a.total_pages) -...

Sometimes having too many unwanted backup and temporary tables will increase the space by huge volumes so it is a best practice to often clean them and keep it tidy. This will improve the performance as well as reduce memory issues. The below stack question contains a query which can be used to identify the space consuming tables in SQL Server. I have attached the question and the query to be executed. https://stackoverflow.com/questions/2094436/how-to-find-largest-objects-in-a-sql-server-database/2094454 SELECT      t.name AS TableName,     i.name as indexName,     sum(p.rows) as RowCounts,     sum(a.total_pages) as TotalPages,      sum(a.used_pages) as UsedPages,      sum(a.data_pages) as DataPages,     (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB,      (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB,      (sum(a.data_pages) * 8) / 1024 a...

Blanks are hectic when it comes to joins, you will see absolutely no different between two values but they won't map with each other. This is a common scenario when you have data loaded from flat files and specially Excel. My below replace statement will work fine and remove all sort of blanks and leave you with pure value for a hassle free join. LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE([ Value to be cleaned ] , CHAR(10), ''), CHAR(13), ''), CHAR(9), ''), CHAR(160 ), '')))

One of the widely noticed issue in SSIS Excel connection manager is type guessing. For example, if there is a column with both numbers and characters and characters are coming after 10 rows of numbers. SSIS Excel connection manager predicts the column as of type numeric and outputs the character values as NULL. In order to handle this, we can edit the connection string and include these additional highlighted phrases to disable type guessing and set the type as TEXT so that all the values get captured properly Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Excel Sources\Distributors\Customer.xlsx;Extended Properties="Excel 12.0 XML; IMEX=1 ;HDR=YES; TYPEGUESSROWS=0;IMPORTMIXEDTYPES=TEXT\ "; The connection string for an excel source is found by right-clicking the excel connection manager and going to properties.

Temporal table is an add on to SQL Server 2016 which allows history maintaining. In simple terms it allows us to have a system versioning. For an example if we have a person's salary as 1000 usd as per today and tomorrow if it is updated as 1005 usd in such scenario the temporal table maintains two tables namely the generic one and a history table where the present record will be stored in the normal table and the previous one will be stored in the history table. Let’s dig in deeper that with an example, First off I will create a employee temporal table with system versioning enabled. In this case even though we are creating a single table it creates two identical tables with one having a history part concatenated to it's name. ***Note : For easier identification the queries are given in italic. CREATE TABLE [master] . dbo . Employee    (       [EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED      , [Name] nvarchar ...

                                                                                                  Sources are heterogeneous and SQL Server allows various types of manipulations and one of the interesting feature in SQL Server 2016 is Querying data of JSON Format.  There are four basic syntax which we can use against a JSON data type. We can demonstrate the use of these syntax with a simple scenario, first off I will define a simple JSON array and walk through each syntax one by one. Use of ISJSON()  ISJSON() syntax is much similar to the ISNUMERIC()  where the latter one checks for a numeric data type and outputs one if it’s true like wise ISJSON() out puts one if the evaluated value is in valid JSON Format. ...

Data Sources are heterogeneous and time to time you might require to query from a data base while being in another, in this case we will query a DB2 Database from SQL Server Management Studio. DB2 Connector First you need to download the DB2 connector to be able to connect to a DB2 Source, in order to do that go to this URL https://www.microsoft.com/en-us/download/details.aspx?id=52676 Select download and choose “ ENU\x64\DB2OLEDBV5_x64.msi ” to download and install the DB2 connector. Configure a Linked Server You can create a linked server to query a DB2 Data base from SSMS, in order to create a linked server go to “Server Objects” and expand it then right click “Linked Servers” and select New Linked Server, here you need to provide the following information. Below is an example for a provider string and you should insert all the relevant values for them. Data Source = ****; User ID=***;Password=*****;Initial Catalog=***;Provider=DB2...

I have been off track for a while due to heavy office commitments, even if it had me away I was able to gain a good chunk of knowledge which I’m willing to share with you all. Today I’m gonna discuss the impressive partition function which saved my day in so many ways. It is as simple as its name states, we are partitioning the query output by giving a set of condition. This can be useful for many needs like given below. 1.         Identify and remove the duplicates precisely. 2.        Unlike the conventional Group By syntax, this allows more customized grouping when retrieving data. Since I work with school data all night long I came up with a code from what I do. WITH HighestMark AS (   SELECT        [StudentID]       , [Year]       , [Semester]       , [FullName]     ...

Power BI is a visualization tool for intelligence analysis. In my personal carrier I have been working with this tool for over a period of half-year and the tool is very user-friendly with engaging UI and an easier learning curve. For the past few months, the arising question is to have a better filtering mechanism throughout the reports with multiple pages in power BI, so that people can precisely view what they want. This has been earlier enabled by the introduction of the drill-through feature which requires some hassle for the end-user to navigate to a specific point which involves multiple key presses and it is not a healthy experience in an end-user perspective.  The newly introduced sync slicers seem to be a better alternative than the drill through but how far does it fit and is it really better than the drill through? Let’s dig more on to this, and it's you to decide. First of all, I would like to say what does the sync slicers exactly does. “Sync slicer...

In my day to day work I work with DAX, it is a routine for me to create measures and query data for the user requirements, in my personal experience DAX allows to see what you can not simply see, it's a revolution of identifying what do you have in your hands, so I thought of highlighting few points and elaborating more on the usage of DAX.  First and foremost the important fact is there are three types of calculations that can be defined using DAX in a model and they are as following. 1.        Calculated Columns 2.        Calculated Tables 3.        Measures Even though we have both MDX and DAX for querying if we are adding a logic to the model it can only be achieved by DAX, Dax can also be used to add row level security, if we consider Power BI we can create roles and assign those roles with restricted data. For an example if we have regional data for China, Unit...