Month: February 2014

Business Analytics VC event on 2/27: Microsoft SQL Server 2014 Countdown event!

Standard

Here are the PASS Business Analytics Virtual Chapter meeting details:

Topic: Microsoft SQL Server 2014 Countdown: Enabling Familiar, Powerful Business Intelligence

Date: 2/27

Time: 12 PM EST

If you’ve not registered for the virtual event, please use the following  URL:

http://bit.ly/SQLPASSBAVC

Event Description:

There has never been such an abundance of available and useful information as there is today both across the web and across your organization. However, users are challenged with effectively discovering and connecting to this information so that they can gain the meaningful insights they need. In this session we will discuss how to empower users with business intelligence through familiar Office tools, and how to manage all your data needs with a powerful and scalable data platform. See the full Microsoft SQL Server 2014 Countdown Webinar Schedule at: http://www.sqlpass.org/SS2014Launch. Please join our experts from engineering and technical marketing teams as they discuss new functionalities and enhancements in SQL Server 2014.

We hope to see you at the event tomorrow!

Regards,
Paras Doshi
VP – Marketing | PASS BA VC

TSQL Script: How to get list of all tables or view in a database?

Standard

I was documenting the list of tables/views in a data mart & staging databases & I found the following scripts useful:

TSQL To get list of all tables:

[sourcecode type=”sql” wraplines=”false”]
SELECT * FROM <DatabaseName>.information_schema.tables
where TABLE_TYPE=’BASE TABLE’
[/sourcecode]

TSQL To get list of all views:
[sourcecode type=”sql” wraplines=”false”]
SELECT * FROM <DatabaseName>.information_schema.tables
where TABLE_TYPE=’VIEW’
[/sourcecode]

Alternatives (for SQL 2005 onwards):
[sourcecode type=”sql” wraplines=”false”]
SELECT * FROM SYS.TABLES
[/sourcecode]

Business Analytics VC: 13 Tips for KPIs with the Microsoft SQL/BI Stack

Standard

Online @ http://BAVC.sqlpass.org
12:00 – 13:00 (GMT-05:00) Eastern Time (US & Canada)
OnlineMeetingURL: https://attendee.gotowebinar.com/register/7675239130599046913
Date: Thursday, February 20

Abstract:
In this webcast, I’ll present 13 tips for designing and creating Key Performance Indicators (KPIs) using the Microsoft SQL BI Tools. I’ll cover a bit of practical theory on KPIs and balanced scorecards, and then dive into some specific examples of basic KPIs, trend-based KPIs, data-driven KPIs from actual applications. I’ll also cover some recommended practices for creating KPIs. Along the way, I’ll use SSAS OLAP, SSAS Tabular, Reporting Services, Excel, and PerformancePoint Services to demonstrate the creation and surfacing of KPIs.

About Kevin:
Kevin S. Goff (http://www.kevinsgoff.net) is a Microsoft SQL Server MVP Database architect/developer/speaker/author Regular columnist for CoDe Magazine on .NET, SQL Server, and Business Intelligence topics Frequent speaker at community events in the Mid-Atlantic region. Host of BDBI Radio (http://www.bdbiradio.com), a weekly webcast on SQL/BI topics

Microsoft Self Service BI mentoring for an American Healthcare company

mockup fwa case tracking health care american company
Standard

Business Summary: Need custom reporting based on FWA (Fraud, Waste & Abuse) cases data.

Technical Summary:
– Led a class for reporting analysts to walk them through the process of creating custom reports based on FWA (fraud, waste, abuse) cases data
– Assisted in developing a dashboard for the Team using Microsoft Self Service BI Tools

Mock up:

The underlying model made it possible for the reporting analysts on the FWA team to create mockup’s like one shown below to satisfy their custom reporting needs:

mockup fwa case tracking health care american company

SSAS MDX: Alias for a Measure Name

Standard

I was working on a requirement that needed me to write MDX code that had an alias for a measure name. If it was SQL, you could just do something like select [column name] as [new column name] from [table name] but in MDX it’s not straight forward. What you could do though is declare members in your MDX statement. so to alias a measure name & your code would look something like:

[code type=”sql”]

with member [measures].[new name] as [measures].[old name]
select
{[measures].[new name]} ON COLUMNS,
{[Date].[Calendar Hierarchy].[ALL]} ON ROWS
FROM [Cube Name]
[/code]

if you’re executing MDX code using OpenQuery, then you’ve one other option, something like this would work:

[code type=”sql”]

Select "[measures].[old name]" as "[measures].[new name]" from openquery(ConnectionName,’select {[measures].[old name]} ON COLUMNS from [cube name]’)

[/code]

Note: After taking inputs from business, Naming should be taken care in the cube. My requirement was to pull data from multiple cubes & so I was aliasing it so that I could standardize the names across different cubes.

I hope this gives you a good starting to alias names while writing MDX code.

SQL Spatial Data Types: A Tip to fix STIntersects Method returning NULL values.

Standard

I was working on some queries that used SQL Spatial data types and ran into a problem where STIntersects function was returning NULL. I was expecting it to say 1 or 0, but it kept on returning NULL. Here’s how I solved it:

1. On Technet article for STIntersects, it said “This method always returns null if the spatial reference IDs (SRIDs) of the geometry instances do not match.“

here’s my pseudo code for you:
– – Function
– – Input Parameters @Area Geometry, @Point1 varchar(10), @Point2 varchar(10)
DECLARE @p point;
SET @p = geometry::STGeomFromText(‘POINT(@Point1 @Point2)’, 0);
– -Note that the 0 in the above line is SRID
return(SELECT @p.STIntersects(@area));

2. So next step was to make sure that the two geometry instances that I was using had same SRID’s.

3. Since I was passing the @area (geometry data type) to the function, I had to check the SRID for @area.
so wrote a single line of code that used the STSrid function.

Select @area.STSrid

That told me that the SRID of the geometric data type was 4xxx

4. So now I modified the original code to change the SRID of @p variable to same as that of @area variable.

So here’s the updated code:
DECLARE @p point;
SET @p = geometry::STGeomFromText(‘POINT(@Point1 @Point2)’, 4xxx);
return(SELECT @p.STIntersects(@area));

5. Now since the SRID of @area and @p matched, it started returning 1 or 0 values as expected.