Category Archives: SQL Server

Querying SSRS Report Definition Using T-SQL

Do you want to have all reports that used a table in their report definition?

Are you looking for a report that has a desired parameter name?

Have you written a new version of a SQL view or stored procedure and you need to modify all the reports working on top of the version of the object, but, you don’t know what those reports are?

Have you modified an SSAS object and you need to know which reports might be affected?

If you have any of the above questions or in general you need to retrieve all SSRS reports which have a specific string in their report definition, just connect to the SQL Server instance which holds your   REPORTSERVER database through SSMS and simply execute the SQL scripts below:

SELECT C.NAME

       , CONVERT(NVARCHAR(MAX),CONVERT(XML,CONVERT(VARBINARY(MAX),C.CONTENT))) AS REPORTXML

FROM  REPORTSERVER.DBO.CATALOG C

WHERE  C.CONTENT IS NOT NULL

            AND  C.TYPE = 2

          –AND  C.NAME LIKE ‘%REPORT_NAME%’

     AND CONVERT(NVARCHAR(MAX),CONVERT(XML,CONVERT(VARBINARY(MAX),C.CONTENT))) LIKE ‘%DESIRED_STRING%’

Enjoy!

Batch Index Rebuild without Using Cursor

Today I came across a cube processing performance issue with one of our clients. So I started a step-by-step troubleshooting including optimising named queries. In some cases the named queries were actually querying some SQL views from the source data warehouse.

After all, I created about 35 new indexes and I needed to justify that all of those indexes are really used. As I processed the faulty cube several times during my step-by-step troubleshooting process it seemed all of those indexes were used.

But, I knew that I created some indexes that covered by some of the new ones and those indexes won’t be used.

I needed to rebuild all the indexes, however, rebuilding all of those indexes from SSMS UI would be such a pain. So I needed to do a batch index rebuild.

So I googled and I’ve found some scripts which actually are doing the job, but, all of them were using cursors. Sadly, I hate cursors so they are the last item in my book. Indeed, I’ll never use cursors until it’s absolutely necessary and there is no other better choices.

Therefore, I decided to do it in my way and I wrote the following script. I thought I’d be happy to share it with you guys as it might help some of you as well.

declare @ix varchar(max), @tbl varchar(max), @counter int, @CustomIx Varchar(max)

declare @table table (id int, tbl varchar(max), ix varchar(max))

set @CustomIx = ‘YOUR_INDEX_NAME_STARS_WITH’ –Custom index name will be like MY_IX_***

insert into @table (id, tbl, ix)

SELECT   ROW_NUMBER() over (order by ix.[NAME]) id

           , OBJECT_NAME(ixstat.[OBJECT_ID]) AS [OBJECT NAME]

         , ix.[NAME] AS [INDEX NAME]

FROM     SYS.DM_DB_INDEX_USAGE_STATS AS ixstat

         INNER JOIN SYS.INDEXES AS ix

           ON ix.[OBJECT_ID] = ixstat.[OBJECT_ID]

              AND ix.INDEX_ID = ixstat.INDEX_ID

WHERE    OBJECTPROPERTY(ixstat.[OBJECT_ID],‘IsUserTable’) = 1

          and  ix.[NAME] like @CustomIx+‘%’

 

set @counter= (select max(id) from @table)

 

while @counter >=1

begin

    set @ix = (select ix from @table where id = @counter)

    set @tbl = (select tbl from @table where id = @counter)

    exec(‘ALTER INDEX ‘+@ix+‘ ON [dbo].[‘+@tbl+‘] REBUILD PARTITION = ALL ‘)

    print @tbl + ‘.’ +  @ix + ‘ Rebuild successful’

    set @counter-=1

end

Continue reading Batch Index Rebuild without Using Cursor

Browsing Cubes Remotely from Excel Trough a VPN Connection without Using Windows Authentication

Technically when you connect to another network through a VPN connection you can see all allowed machines on that network. So it is easy to connect to a SQL Server instance using SQL Server authentication. However, I’m explaining this part for some of you guys that might be new to connecting from Excel directly to a database on SQL Server and create flashy reports on Excel.

But, what about connecting directly from Excel to a remote Analysis Services instance without using Windows Authentication? You’re right! I’m saying you can connect directly from your own Excel to a remote SSAS server without using windows authentication. Well, technically there is no SQL Server Authentication mode available for Analysis Services. So what does that actually mean when I say “without using windows authentication”? If you’re interested in finding the answer keep reading this article.

Scenario:

You’re working as a BI consultant, you’ve been told that a client needs to have some simple reports on Excel as follows:

·         You should connect to the client’s server using a provided VPN connection

·         The VPN connection could be established through a Windows VPN, Cisco VPN etc. so the VPN client or the port and protocol used don’t actually matter

·         Microsoft Excel is NOT installed on the client’s server

·         You’re NOT allowed to install Excel on the server

·         As it is a costly process the client will not setup a virtual machine in their network so that you can remotely connect to it and install Excel then connect to their SQL Server/Analysis Services instances

·         There is no trust relationship between your network and the client’s network, so your domain user name and password could not be authenticated on the client’s network

·         The client needs to have some reports on Excel on top of a SQL Server database and OLAP cubes on Analysis Services (SSAS)

·         You have the right to run an application as administrator on the remote server

·         You need to connect to the remote server directly from your own Microsoft Excel installed on your machine

·         The client also provided a remote desktop access to the server

·         On the remote desktop SQL Server Management Studio (SSMS) is installed

·         In the remote SQL Server your account is a member of the “securityadmin” server role so you can create a new SQL Server Login

Continue reading Browsing Cubes Remotely from Excel Trough a VPN Connection without Using Windows Authentication

An Easy Way for SSAS Batch Processing

Batch processing is available in Analysis Services. Which means we can send multiple processing commands to the server in just a single SQL Server Job. With SSAS batch processing we can control which objects to be processed and in what order in a batch.  As batch processing reduces the amount of time taken to commit changes it offers better data availability. We can easily generate XMLA codes  for batch processing through SSMS (SQL Server Management Studio). You might see lots of discussions about this in other websites and lots of them are saying you need to right click on the objects one by one and generate the scripts. Then put all scripts together in another XMLA script. But it is such a pain when you have lots of objects that should be selected one after another to generate the batch processing XMLA. Sadly, it is not the end of the story. You need put all scripts together by copying and pasting the scripts several times. Today I want to show you a very easy to the job which saves lots of your time.

I’m using “Adventure Works 2012 Multidimensional” as an example and I’m going to batch process some dimensions.

  • Connect to the SSAS server from Management Studio
  • Expand the database
  • Expand dimensions

image

Continue reading An Easy Way for SSAS Batch Processing

How To Implement a Composite Key In SSAS Tabular Model

As you might know SSAS tabular models do not support composite keys so you always must have just one column to make a unique row through the whole table. This is such a pain especially when you are new to the tabular models and don’t have that much detail information about it. So when you import some tables with existing relationships based on composite keys, the Table Import Wizard will ignore those relationships.

So what should we do to solve the problem?

The solution is to combine the values of the composite keys. 

Here is how you can do the job?

·         Creating a view on top of the source tables:

1.  If you’re using SQL Server 2012 and above you can use the “concat” function to combine the values. The function combines several expressions regardless of their data types. So you can use it like this select CONCAT (1, 1.22100001,‘First’) SQL2012 and the result would be something like this

clip_image001

2.  If you’re using earlier versions of SQL Server then you need to mind the data types. So for the above sample the SQL code would be select cast(1 as char(1)) + cast(1.22100001 as char(10))+‘First’ SQL2008 . As we expect the result is the same.

·         Adding a new computed column to all tables involved in SQL Server before importing the tables to the tabular model

·         Adding a new calculated column to all tables involved after importing the tables to the tabular model

As a quick note, you’ll need to remove the existing relationships imported from SQL Server and create the new relationship based on the combined keys.

Easy peasy!

Dynamically Passing Parameters to a SQL Stored Procedure from PowerPivot Using VBA

In this post I express an easy way to refresh a PowerPivot model dynamically based on SQL Server Stored Procedures. Let’s start with SQL Server Management Studio (SSMS) 2012 and use Adventure Works 2012 database. Run the following script to see the results in SSMS:

exec [dbo].[uspGetBillOfMaterials] 727, ‘2009-01-02’

 

First parameter: Start Product ID

Second Parameter: Check Date

Results:

powerpivot01

Now we want to see the results for the following script:

exec [dbo].[uspGetBillOfMaterials] 762, ‘2009-01-02’

 

Results:

powerpivot02

Now we want to do the same with PowerPivot.

Continue reading Dynamically Passing Parameters to a SQL Stored Procedure from PowerPivot Using VBA

Power Query and SQL Server Stored Procedures

Today I want to explain how you can pass parameters to a SQL Server stored procedure. I myself was looking for a way to pass parameters to a SQL Server stored proc from Power Query. I spent a lot of time to search for a good article over the internet that explains how I could pass parameters to a stored proc from Power Query and show the results in Excel. But, I couldn’t find that much information around this as I expected. So, I decided to do some work around and you can read the results in this post. To simplify the solution, I’m going to use uspGetBillOfMaterials stored procedure in AdventureWorks 2012 database. The stored procedure accepts an integer number as ProductID and a date as CheckDate. So we need to pass two parameters to uspGetBillOfMaterials  to get the results.

If we execute the stored proc in SSMS using

exec [dbo].[uspGetBillOfMaterials] 727, ‘2009-01-02’

, we’ll get the following result:

image

Now, lets go to do some works on Power Query. So open Microsoft Excel and go to Power Query tab and select SQL Server database.

image

Now type Server, Database and SQL Statement, then click OK.

Continue reading Power Query and SQL Server Stored Procedures