BI-Common Solution

Sql running

2023-09-17T10:23:00.002-07:00

SQL show running queries

2012-09-03T22:17:00.002-07:00

SQL show running queries

We sometimes need to find out running queries on sql server box. To show running queries using t-sql I use the syntax below

SELECT *
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)

Know Your Calculated Measure Definition Using MDX Query

2012-05-15T05:52:00.001-07:00

select measure_name, expression from$system.mdschema_measures
where measure_unique_name = '[Measures].[Internet Ratio to Parent Product]'

Definition used for calculated Measure [Measures].[Internet Ratio to Parent Product]

Case When [Product].[Product Model Categories].CurrentMember.Level.Ordinal = 0 Then 1 Else

[Measures].[Internet Sales Amount] / [Product].[Product Model Categories].CurrentMember.Parent, [Measures].[Internet Sales Amount] ) End

DrillDown Dashboard In SSRS

2012-05-06T04:46:00.000-07:00

Drilldown Chart (Dashboard)in SSRS without using Sub Reports.

This post has been moved to this link Please visit this Link

In SSRS as we develop drilldown reports in the same way we can develop drill down charts. Once you click on the main chart the corresponding detail level chart should display on the same report without using Sub report.

Let’s develop a drilldown chart

We have one bar chart showing total sale yearly. Once you click on the any year bar the corresponding pie chart should display with product wise sale.

Steps to create a drilldown chart.
1. Create a new report and name it dashboard with your shared data source (I am going to use sample Adventure works database)
2. Create a dataset with the following query
SELECT Floor(SalesLT.SalesOrderDetail.LineTotal) as Sale, DATEPART(yyyy, SalesLT.Product.SellStartDate) AS Year FROM SalesLT.Product INNER JOIN SalesLT.SalesOrderDetail ON SalesLT.Product.ProductID = SalesLT.SalesOrderDetail.ProductID
3. Now you need to add a bar chart to your report
4. Right click on the chart and set the chart properties

Now our first bar chart is ready to preview.

Our main task is to create a sub chart on clicking on the year bar(Bar Chart) corresponding pie chart should display with information.

5.Now create another data set for the second chart and name it Product.

SELECT top 5 SalesLT.ProductCategory.Name, Floor(Sum(SalesLT.SalesOrderDetail.LineTotal)) as LineTotal FROM SalesLT.Product INNER JOIN SalesLT.SalesOrderDetail ON SalesLT.Product.ProductID=SalesLT.SalesOrderDetail.ProductID INNER JOIN SalesLT.ProductCategory ON SalesLT.Product.ProductCategoryID= SalesLT.ProductCategory.ProductCategoryID WHERE (DATEPART(yyyy, SalesLT.Product.SellStartDate) = @year)

Group by SalesLT.ProductCategory.Name

The above query has parameter named year

6. Now drag another pie chart on the report and map it with the new dataset.

7. Right click on the chart and set its properties

8. Pass the parameter from main chart(bar) to pie chart

How to pass the parameter from main chart to detail chart (pie)

Now right click on the bar and then click on the bar chart series properties.

8.1 Go to Action tab and click on option Go to report.

8.2 Choose the same report i.e. Dashboard

8.3 Click on Add button and add parameters the report

9. Set some default parameter to your report so when you preview your report it should not ask you for parameter it should render with some set of parameter.

10.Go to the year parameter and right click on it and set default value to it as -1

11. Now we need to set some visibility to second chart so when you run your report first time only bar chart should display on clicking on the any bar of the first chart than only second chart should display.

12. Now you right click on the Pie chart and then click on the chart properties.

13. Then you click on visibility.

14. On show and hide based expression place this

=IIF(Parameters!year.Value>0,false,true)

Now you report is ready to preview

For the look and feel of the charts format your charts as per your requirements.

Preview your report first you will see this chart in pic. below

Click on any year bar you will have your pie chart along with bar chart

My MSDN Contribution

2012-04-28T22:45:00.002-07:00

Hybrid SQL

Hybrid SQL: - Hybrid Sql is nothing but it is a combination of TSql statements and MDX.

To create hybrid sql. We need to create a linked Server

Linked Server:- linked server enable the SQL Server Database Engine to execute commands against OLE DB data sources outside of the instance of SQL Server. Linked servers are configured to enable the Database Engine to execute a Transact-SQL statement that includes tables in another instance of SQL Server, or another database product such as Oracle. Many types OLE DB data sources can be configured as linked server

How to create a linked server:-

1. To create a linked sever you have admin right of SqlServer.

2. Navigate to Server Objects > Linked Servers

3. Right click on Linked Servers and select New Linked Server.

Let’s take a sample database AdventureWorksDW2008R2 and create a linked server

USE

AdventureWorksDW2008R2

EXEC

sp_addlinkedserver

@server='Test',

@srvproduct='',

@provider='MSOLAP',

@datasrc='localhost',

@catalog='Adventure Works DW 2008R2'

4. Once the linked server is set we need another function OPENQUERY

Open Query: Executes the specified pass-through query on the specified linked server. This server is an OLE DB data source. OPENQUERY can be referenced in the FROM clause of a query as if it were a table name. OPENQUERY can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement. This is subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENQUERY returns only the first one.

Syntax: - OPENQUERY (linked_server,’query’)

Now you can write your Script

USE

AdventureWorksDW2008R2

Declare @MDXExpression as Varchar(MAX)

Create Table #temp(

Country varchar(Max),

Sale_Amount float,

Product_cost float,

Tax_amount Float,

TProduct_Cost Float

)

Set @MDXExpression =

'SELECT

NON EMPTY

{

[Measures].[Sales Amount]

,[Measures].[Standard Product Cost]

,[Measures].[Tax Amount]

,[Measures].[Total Product Cost]

} ON 0,

NON EMPTY

{

NonEmpty ( [Sales Territory].[Sales Territory Region].[Sales Territory Region])

} ON 1

FROM

[Adventure Works]';

Exec ('SELECT * INTO ##TestTemp1 FROM OpenQuery(Test,''' + @MDXExpression + ''')')

Insert into #temp SELECT * from ##TestTemp1

Here we are joining MDX output with Tsql Table DimSalesTerritory and Mdxoutput #temp

SELECT SalesTerritoryGroup, #temp.*

FROM DimSalesTerritory inner join #temp on #temp.[Country]= DimSalesTerritory.SalesTerritoryCountry

1.As you can see we have create linked server above with named test.

2. Now we have created variable @MDXExpression which we are using keep the MDX query in it and pass it to the Targeted server through open query.

3. Now we will dump the output of MDX query in #testtemp

Exec ('SELECT * INTO ##TestTemp FROM OpenQuery(Test,''' + @MDXExpression + ''')')

4. Then we can join the Mdx output with TSQl Table as we did above in last Select clause

Single comma separated row into multiple rows

2012-04-23T01:46:00.002-07:00

1.Single comma separated row into multiple rows.
Lets create table
Create table #temp (Country varchar(15), City varchar(50))
Insert into #temp Values ('INDIA','Pune, New Delhi,Mumbai, Chandigarh')
Insert into #temp Values ('USA','New York, New Jersey,Washington, California')
lets see the Out of this table below
#Output1:-
Country   City
INDIA     Pune, New Delhi,Mumbai, Chandigarh
USA        New York, New Jersey,Washington, California
Excecute this query
SELECT temp.Country,
Split.temp.value('.', 'VARCHAR(20)') AS city
FROM (SELECT Country,           CAST ('<M>' + REPLACE([City], ',', '</M><M>') + '</M>' AS XML) AS String FROM #temp) AS temp CROSS APPLY String.nodes ('/M') AS Split(temp);

Output2:-
Country    city
INDIA     Pune
INDIA     New Delhi
INDIA     Mumbai
INDIA     Chandigarh
USA         New York
USA         New Jersey
USA         Washington
USA          California
2.Multiple rows into a single comma separated row as #Output1

Now you put the output2 in another table i.e #temp2
SELECT temp.Country,
Split.temp.value('.', 'VARCHAR(20)') AS city
into #temp2
FROM (SELECT Country, CAST ('<M>' + REPLACE([City], ',', '</M><M>') + '</M>' AS XML) AS String       FROM #temp) AS temp CROSS APPLY String.nodes ('/M') AS Split(temp);

Now Select This Query

select distinct t1.Country, (
select t.City + ','
from #temp2 t
where t.Country=t1.Country
order by t.City
for xml path( '' )
)

from #temp2 t1

SSRS- Subscriptions

2012-02-19T04:48:00.001-08:00

SSRS- Subscriptions

· A subscription is a standing request to deliver a report at a specific time or in response to an event, and in an application file format that you specify in the subscription. It’s an alternative way to deliver the report to end user in automated manner on predefined schedule.

· The objective of the subscription is to reduce the On-Demand access to the report server, therefore it also reduce network traffic and provide readymade business analysis to the client for specific event ..

· Subscriptions are processed on the report server and are distributed through delivery extensions that are deployed on the server. By default, you can create subscriptions that send reports to a shared folder or to an e-mail address. If the report server is configured for SharePoint integrated mode, you can also send a report to a SharePoint library.

There are two type of subscription

· Standard Subscriptions

· Data Driven Subscriptions

Standard Subscriptions

Standard subscriptions are created and managed by individual users. A standard subscription consists of static values that cannot be varied during subscription processing.

How to set subscription

· Go to report manager

· Click on the report for which want to subscribe

· Now click on “Manage”(See below on Pic.)

As you Click on subscribe you will see this screen.

Now you click on “New Subscription”

Now you set your Subscription option

While setting Subscription your Sql server Agent should be in running mode