PASS Summit 2014…. 3rd Year!

sqlpass1

SQLHiker Family in Pike Place Market

sqlpass2

Selfie in Session

1

SQL karaoke

 

2

Space Needle

 

I’m back from the PASS Summit 2014 which had 3,941 delegates and 1,959 pre-conference registrations across 56 countries for a total of 5,900 registrations. The event’s sold-out Exhibit Hall features 57 partners, including Platinum sponsors Dell Software and HP. The visible first-timers badges were everywhere. My third year and keeps getting better each year. I did a lot of the usual things, walked in #SQLwalk, attended two pre-conference seminar, catching with coworkers and friends, visited with exhibitors, and went to a few after hours events (that included karaoke). You know, the remarkable Summit stuff. I was sporting my new t-shirts and sqlhiker logo designed by Daniel McLeod. Pre-conference I attended Big Data: Deploy, Design, and Manage Like a Pro with Adam JorgensenJohn Welch, and Buck Woody this was a well put together seminar the presenters knew there material. The level and nature of participation from students and presenters was simply exceptional, concrete exercise, concrete examples, excellent analysis and feedback. The time flew by! The second pre-conference I shadowed along in the SSIS: Problem, Design, Solution presented by the Knight brothers Brian KnightDevin Knight. The seminar materials were well worth it and included the two first class SSIS speakers insight for 5 hours; liked there approach.I also attended a series of PASS sessions at the event. I really enjoyed the sessions “Bullet-Resistant SSIS Packages” by Tim Mitchell and” Join Us! Getting Started as a Technical Speaker” by Eddie Wuerch. Both went be on the usual for explaining their topic. They are speakers I hope to be someday. I also went to a couple of community sessions in and around the work I will be doing at Fiserv. All good sessions; but this year is the first time I took a technical speaking course and something has clicked. I always knew enjoyed speaking and doing company Lunch n Learns. Just never thought about doing it for other DBA’s. So this year I’m setting new goals: Complete 24 blog post, volunteering some time to the Atlanta MDF chapter, and submitting as a speaker in a SQL Saturday. I just need to work out the timing and a topic. Any recommendations are welcome.With the long Veteran day following week after the conference; it gave my wife and I a chance to celebrate our anniversary and take in the tourist sites for an extra few days: Space Needle, Pike Place Market, Crab House, Bainbridge Island, Chihuly Garden and Glass, Seattle Great Wheel and Seattle Art Museum: SAM.The Summit is always a special event for me each year. I have been to many technical conferences in my career, but none compare to the experiences I’ve had since I began participating at PASS Summit. Whether you’re an attendee or a speaker, your experience will be enhanced exponentially by participating. First timer? No problem! This community welcomes you and wants you to have a positive experience. All you have to do is engage with other people. Smile. Ask a question. I hope to meet you and make your acquaintance.
Advertisements

SQL PASS Summit 2014 Checklist

This year we are headed back to Seattle, WA. First time for me. New job and new goals to be focused on at this year SQL Summit. I’m so ready to be with the #sqlfamily and discover the coastal seaport city of Seattle. This year my wife Stacy will be my guest and attend the evening events. After the conference her and I are getting some R&R at a B&B for our anniversary. A few extra days on the pacific coast will be good for us.

Be ready to be physical and mentally drained and mind blow at the same time. Its the best way I can describe the summit. I enjoyed it some much last year; I’m going back. I didn’t giving my employers an option. Told everyone at the office this is a conference for me and is hands down the best event for SQL DBAs.

So what do I need to do before SQL PASS Summit 2014:

Registration

  • Pre-Con
  • Full Summit
  • 1 Day

Travel plans

  1. Transport / Accommodation
  2. It would also be worthwhile to start planning on how you’d want to get to Washington State Convention Center, i.e. by car or public transport, etc.
  3. Weather in November

Review Sessions

  1. Use the schedule builder to put together a list of sessions to go to
  2. Guidebook App

Review Local Attractions

  1. Trip Advisor

Check out the networking events ,#sqlrun, #sqlwalk and SQL Karaoke.

Stay Connected on Social Media (What to know whats going on? Get on Twitter!)

  1. #sqlpass
  2. #summit14
  3. #SQLFamily

Things to take with:

  1. Mobile devices (Chargers)
  2. Bag to carry brochures and etc.
  3. Walking, Running, and Drinking shoes
  4. Have your business cards ready
  5. Water Bottle

Work covered- Out of Office enabled on email. Noting the time differences for reply.

See if you know any one else coming

Spread about SQL PASS Summit

First Timers events

1 Goal

One of the ways to get the most of the PASS Summit is to have at least one goal on what you want to achieve, even if it was just to “hang out” with #SQLFamily. HAVE FUN!

 

 

Any Hikers and or DBAs power snacks

WIN_20140919_194828

Mountain Trail Mix

 

For the last few years I have been snacking on one of the cheapest and tastiest treats. Thanks to my wife Stacy for finding and introducing it to me. Walmart has the best trail mix for any dbahiker that needs a power snack. It has real M&Ms chocolates, peanuts, raisins, almonds, and cashews. Thanks Walmart. Great for any kind of hike or just sitting at my desk writing code or administrating servers or users.

Update dbase blank columns before published in SQL Server

dbase_SSIS When I was designing a SSIS package to move dbase (DBF) files to SQL Server tables. I noticed I was going have to clean up the VARCHARs and DATETIMEs in the database before I could publish the data for BI Team. In my SSIS Package these are the last two tasks. The two queries below dynamically get the columns data types and names and does update statements on the columns to correct them.

Update DateTimes to NULL

declare @tab table (id int identity(1,1) primary key,SQLtable varchar(50),SQLcolumn VARCHAR(50))
insert into @tab(SQLtable,SQLcolumn)

select
so.name table_name
,sc.name column_name
--,st.name data_type
from sysobjects so
inner join syscolumns sc on (so.id = sc.id)
inner join systypes st on (st.type = sc.type)
where so.type = 'U'
and st.name IN ('DATETIME', 'DATE')
declare @sql varchar(8000), @i int,@count int, @sqltable varchar(50), @sqlcolumn varchar(50)

set @i=1
select @count= count(*) from @tab
while @i <= @count

begin
select @SQLtable=SQLtable from @tab where id=@i
select @sqlcolumn =sqlcolumn from @tab where id=@i

set @sql = '
UPDATE '+@SQLtable+'
SET '+@SQLcolumn+' = CASE WHEN '+@SQLcolumn+' = ''1899-12-30''
THEN NULL
ELSE '+@SQLcolumn+'
END
'
--To print in the Result/Message box
print @sql
--To EXECUTE
--EXEC (@sql)

set @i=@i+1
end

Update Varchars to NULL

declare @tab table (id int identity(1,1) primary key,SQLtable varchar(50),SQLcolumn VARCHAR(50))
insert into @tab(SQLtable,SQLcolumn)

 select
 so.name table_name
 ,sc.name column_name
 --,st.name data_type
from sysobjects so
inner join syscolumns sc on (so.id = sc.id)
inner join systypes st on (st.type = sc.type)
where so.type = 'U'
and st.name IN ('nvarchar', 'varchar', 'char', 'nchar')
--Added because of Error
AND sc.name <> 'group'

declare @sql varchar(8000), @i int,@count int, @sqltable varchar(50), @sqlcolumn varchar(50) 

set @i=1
select @count= count(*) from @tab
while @i <= @count

begin
select @SQLtable=SQLtable from @tab where id=@i
select @sqlcolumn =sqlcolumn from @tab where id=@i 

set @sql = '
 UPDATE '+@SQLtable+'
 SET '+@SQLcolumn+' = CASE WHEN LEN('+@SQLcolumn+') = 0
 THEN NULL
 ELSE '+@SQLcolumn+'
 END
'
--To print in the Result/Message box
print @sql
--To EXECUTE
--EXEC (@sql)
set @i=@i+1
end

Turn Unusable Date into usable SQL DateTime

We had situation where we had an application that feed a varchar date into a column that was not usable to our BI report writers. The column had 3 months letters and a space and the year which was numeric.

Analysis_Date

 

To create the sample Table:

SELECT * INTO [Table]
FROM (
SELECT N'Apr 2014' AS [Analysis_Date] UNION ALL
SELECT N'Feb 2012' AS [Analysis_Date] UNION ALL
SELECT N'Jan 2014' AS [Analysis_Date] UNION ALL
SELECT N'Jul 2014' AS [Analysis_Date] UNION ALL
SELECT N'Jun 2014' AS [Analysis_Date] UNION ALL
SELECT N'Mar 2014' AS [Analysis_Date] UNION ALL
SELECT N'May 2014' AS [Analysis_Date] UNION ALL
SELECT N'Apr 2013' AS [Analysis_Date]
) t;
SELECT [Analysis_Date]
FROM [Table]
--DROP TABLE [Table]
GO

 

First I broke the data into two columns as you can see below with a SUBSTRING.

SELECT DISTINCT
DDA_Hist.Analysis_Date ,
SUBSTRING(DDA_Hist.Analysis_Date, 5, 9) AS [Analysis Year] ,
LEFT(DDA_Hist.Analysis_Date, 4) AS [Analysis Month]
FROM Table

This produces:

Analysis_Date1

 

Then I took the two substrings and mashed them to recreate the beginning of the month datetime. The magic happens with the SUBSTRING and CHARINDEX.

‘XXJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC’

this string lets me count the months and get a number instead of the months first three letters.

SELECT DISTINCT
DDA_Hist.Analysis_Date ,
SUBSTRING(DDA_Hist.Analysis_Date, 5, 9) AS [Analysis Year] ,
LEFT(DDA_Hist.Analysis_Date, 4) AS [Analysis Month],
 CAST(CAST(SUBSTRING(DDA_Hist.Analysis_Date, 5, 9) AS VARCHAR(4))
 + RIGHT('0'
 + CAST(CHARINDEX(LEFT(DDA_Hist.Analysis_Date, 3),
 'XXJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC')
 / 3 AS VARCHAR(2)), 2) + RIGHT('0'
 + CAST('01' AS VARCHAR(2)),
 2) AS DATETIME) AS Analysis_Datetime_BeginMonth
FROM Table

This produces:

Analysis_Date2

Click to enlarge

Taking that same logic we can also get the end of that month datetime as well.

SELECT DISTINCT
DDA_Hist.Analysis_Date ,
SUBSTRING(DDA_Hist.Analysis_Date, 5, 9) AS [Analysis Year] ,
LEFT(DDA_Hist.Analysis_Date, 4) AS [Analysis Month],
CAST(CAST(SUBSTRING(DDA_Hist.Analysis_Date, 5, 9) AS VARCHAR(4))
+ RIGHT('0'
+ CAST(CHARINDEX(LEFT(DDA_Hist.Analysis_Date, 3),
'XXJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC')
/ 3 AS VARCHAR(2)), 2) + RIGHT('0'
+ CAST('01' AS VARCHAR(2)),
2) AS DATETIME) AS Analysis_Datetime_BeginMonth</pre>
<pre> DATEADD(DAY,
-DAY(DATEADD(MONTH, 1,
CAST(CAST(SUBSTRING(DDA_Hist.Analysis_Date,
5, 9) AS VARCHAR(4))
+ RIGHT('0'
+ CAST(CHARINDEX(LEFT(DDA_Hist.Analysis_Date,
3),
'XXJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC')
/ 3 AS VARCHAR(2)), 2) + RIGHT('0'
+ CAST('01' AS VARCHAR(2)),
2) AS DATETIME))),
DATEADD(MONTH, 1,
CAST(CAST(SUBSTRING(DDA_Hist.Analysis_Date, 5,
9) AS VARCHAR(4)) + RIGHT('0'
+ CAST(CHARINDEX(LEFT(DDA_Hist.Analysis_Date,
3),
'XXJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC')
/ 3 AS VARCHAR(2)),
2) + RIGHT('0'
+ CAST('01' AS VARCHAR(2)),
2) AS DATETIME))) AS Analysis_Datetime_MonthEnd
FROM Table

This produces:

Analysis_Date3

Click to enlarge

Have Fun!

Flattening a Parent Child Hierarchy with EnumeratedPath

HFM_Hierarchy

This is for companies with HFM repository on MS SQL or users wanting to flattening a parent-child hierarchy. If you are administratoring HFM and have users who would like to have that HFM Account Hierarchy in SQL. These scripts will help you with flattening a parent-child hierarchy for internal reporting. They are designed and can be very useful for both SQL and HFM. By using a recursive CTE we can enumerate the path from the leaf to the root/roof (Bottom to the top). See the first image to envision the HFM parent child hierarchy tree.

First I created a temp table to work with one item’s parent-child hierarchy with 14 levels. The -1 is the top of the house  itemid in HFM; other applications/ examples may have NULL for the top parentid. Once you have this working you will want to change the first SQL view below to point to the hfm.dbo.Company_Account_Layout table.

SELECT * INTO tmp_GridResults_1
FROM (
SELECT N'860' AS [ItemID], N'861' AS [ParentID] UNION ALL
SELECT N'861' AS [ItemID], N'868' AS [ParentID] UNION ALL
SELECT N'868' AS [ItemID], N'908' AS [ParentID] UNION ALL
SELECT N'908' AS [ItemID], N'1056' AS [ParentID] UNION ALL
SELECT N'1056' AS [ItemID], N'1704' AS [ParentID] UNION ALL
SELECT N'1704' AS [ItemID], N'1706' AS [ParentID] UNION ALL
SELECT N'1706' AS [ItemID], N'1371' AS [ParentID] UNION ALL
SELECT N'1371' AS [ItemID], N'1708' AS [ParentID] UNION ALL
SELECT N'1708' AS [ItemID], N'1709' AS [ParentID] UNION ALL
SELECT N'1709' AS [ItemID], N'1711' AS [ParentID] UNION ALL
SELECT N'1711' AS [ItemID], N'1903' AS [ParentID] UNION ALL
SELECT N'1903' AS [ItemID], N'2104' AS [ParentID] UNION ALL
SELECT N'2104' AS [ItemID], N'-1' AS [ParentID] ) t;
SELECT [ItemID], [ParentID]
FROM tmp_GridResults_1

This produces:

HFM1

Click to enlarge

In this cte I’m going to build the tree Path, ItemID, ParentID and the Level. Remember change the hfm.dbo.Company_Account_Layout table after you have the temp table working.

CREATE VIEW [dbo].[vw_HFM_CTE] AS

WITH cte([ItemID] ,[Path],[Level], [ParentID])
AS
(
--Parents
SELECT e.[ItemID],
Path=CAST(CAST(e.[ItemID] AS VARCHAR(500)) + ',' AS VARCHAR(1000)),
0 AS [Level],
[ParentID]
FROM [dbo].[tmp_GridResults_1]AS e
--FROM hfm.dbo.Company_Account_Layout AS e
WHERE e.[ParentID] = -1
UNION ALL
--Levels up
SELECT
l.[ItemID],
Path1=CAST(CAST(cte.Path AS VARCHAR(500)) + CAST(l.[ItemID] AS VARCHAR(500)) + ',' AS VARCHAR(1000)),
[Level] + 1,
l.[ParentID] AS [ParentID]
FROM [dbo].[tmp_GridResults_1] AS l
--FROM hfm.dbo.Company_Account_Layout AS l
INNER JOIN cte
ON l.[ParentID] = cte.[ItemID]
)
SELECT *
FROM cte
GO

This produces:

HFM2

Click to enlarge

Then to keep things easy and clear for me I break into another view. I built a SQL view to flattening a parent-child hierarchy path with another cte that converts the Path column to XML and then cross apply to unpivot the data then I pivot the data back on the levels.

I’m including HFM’s Label and Description from the COMPANY_ACCOUNT_ITEM and COMPANY_ACCOUNT_DESC tables.

The three case statements are separating the label, description, and child item. I also replace brackets around the work [NONE] in our labels.

CREATE VIEW [dbo].[vw_HFM_CTE_Results] AS
 WITH cte1 AS (

 SELECT
 t2.ItemID,
 t3.split,
 ROW_NUMBER() OVER (PARTITION BY t2.ItemID ORDER BY t2.ItemID) AS num
 FROM
 (
 SELECT *,
 CAST('<X>'+REPLACE(t.path,',','</X><X>')+'</X>' AS XML) AS xmlfilter
 FROM [HFM].[dbo].[vw_HFM_CTE] t
 WHERE [Path] NOT LIKE '%,2101,%'
 ) t2
 CROSS APPLY
 (
 SELECT col1data.D.value('.','varchar(50)') AS split
 FROM t2.xmlfilter.nodes('X') AS col1data(D)) t3

 )

SELECT ItemID
 ,[1] AS [1_Path]
 ,CASE WHEN LEN([1]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([1],'[',''),']',''),0,CHARINDEX('|',REPLACE(REPLACE([1],'[',''),']',''))) END AS [1]
 ,CASE WHEN LEN([1]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([1],'[',''),']',''),CHARINDEX('|',REPLACE(REPLACE([1],'[',''),']',''))+1,CHARINDEX('|',REVERSE(REPLACE(REPLACE([1],'[',''),']','')))-1)
 END AS [1_Label]
 ,CASE WHEN LEN([1]) = 0
 THEN NULL
 ELSE REVERSE(SUBSTRING(REVERSE(REPLACE(REPLACE([1],'[',''),']','')),0,CHARINDEX('|',REVERSE(REPLACE(REPLACE([1],'[',''),']','')))))
 END AS [1_Description]
 ------
 ,[2] AS [2_Path]
 ,CASE WHEN LEN([2]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([2],'[',''),']',''),0,CHARINDEX('|',REPLACE(REPLACE([2],'[',''),']',''))) END AS [2]
 ,CASE WHEN LEN([2]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([2],'[',''),']',''),CHARINDEX('|',REPLACE(REPLACE([2],'[',''),']',''))+1,LEN([2])-CHARINDEX('|',REVERSE(REPLACE(REPLACE([2],'[',''),']','')))-CHARINDEX('|',REPLACE(REPLACE([2],'[',''),']','')))
 END AS [2_Label]
 ,CASE WHEN LEN([2]) = 0
 THEN NULL
 ELSE REVERSE(SUBSTRING(REVERSE(REPLACE(REPLACE([2],'[',''),']','')),0,CHARINDEX('|',REVERSE(REPLACE(REPLACE([2],'[',''),']','')))))
 END AS [2_Description]
 ------
 ,[3] AS [3_Path]
 ,CASE WHEN LEN([3]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([3],'[',''),']',''),0,CHARINDEX('|',REPLACE(REPLACE([3],'[',''),']',''))) END AS [3]
 ,CASE WHEN LEN([3]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([3],'[',''),']',''),CHARINDEX('|',REPLACE(REPLACE([3],'[',''),']',''))+1,LEN([3])-CHARINDEX('|',REVERSE(REPLACE(REPLACE([3],'[',''),']','')))-CHARINDEX('|',REPLACE(REPLACE([3],'[',''),']','')))
 END AS [3_Label]
 ,CASE WHEN LEN([3]) = 0
 THEN NULL
 ELSE REVERSE(SUBSTRING(REVERSE(REPLACE(REPLACE([3],'[',''),']','')),0,CHARINDEX('|',REVERSE(REPLACE(REPLACE([3],'[',''),']','')))))
 END AS [3_Description]
 --------
 ,[4] AS [4_Path]
 ,CASE WHEN LEN([4]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([4],'[',''),']',''),0,CHARINDEX('|',REPLACE(REPLACE([4],'[',''),']',''))) END AS [4]
 ,CASE WHEN LEN([4]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([4],'[',''),']',''),CHARINDEX('|',REPLACE(REPLACE([4],'[',''),']',''))+1,LEN([4])-CHARINDEX('|',REVERSE(REPLACE(REPLACE([4],'[',''),']','')))-CHARINDEX('|',REPLACE(REPLACE([4],'[',''),']','')))
 END AS [4_Label]
 ,CASE WHEN LEN([4]) = 0
 THEN NULL
 ELSE REVERSE(SUBSTRING(REVERSE(REPLACE(REPLACE([4],'[',''),']','')),0,CHARINDEX('|',REVERSE(REPLACE(REPLACE([4],'[',''),']','')))))
 END AS [4_Description]
 -------
 ,[5] AS [5_Path]
 ,CASE WHEN LEN([5]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([5],'[',''),']',''),0,CHARINDEX('|',REPLACE(REPLACE([5],'[',''),']',''))) END AS [5]
 ,CASE WHEN LEN([5]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([5],'[',''),']',''),CHARINDEX('|',REPLACE(REPLACE([5],'[',''),']',''))+1,LEN([5])-CHARINDEX('|',REVERSE(REPLACE(REPLACE([5],'[',''),']','')))-CHARINDEX('|',REPLACE(REPLACE([5],'[',''),']','')))
 END AS [5_Label]
 ,CASE WHEN LEN([5]) = 0
 THEN NULL
 ELSE REVERSE(SUBSTRING(REVERSE(REPLACE(REPLACE([5],'[',''),']','')),0,CHARINDEX('|',REVERSE(REPLACE(REPLACE([5],'[',''),']','')))))
 END AS [5_Description]
 -------
 ,[6] AS [6_Path]
 ,CASE WHEN LEN([6]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([6],'[',''),']',''),0,CHARINDEX('|',REPLACE(REPLACE([6],'[',''),']',''))) END AS [6]
 ,CASE WHEN LEN([6]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([6],'[',''),']',''),CHARINDEX('|',REPLACE(REPLACE([6],'[',''),']',''))+1,LEN([6])-CHARINDEX('|',REVERSE(REPLACE(REPLACE([6],'[',''),']','')))-CHARINDEX('|',REPLACE(REPLACE([6],'[',''),']','')))
 END AS [6_Label]
 ,CASE WHEN LEN([6]) = 0
 THEN NULL
 ELSE REVERSE(SUBSTRING(REVERSE(REPLACE(REPLACE([6],'[',''),']','')),0,CHARINDEX('|',REVERSE(REPLACE(REPLACE([6],'[',''),']','')))))
 END AS [6_Description]
 -------
 ,[7] AS [7_Path]
 ,CASE WHEN LEN([7]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([7],'[',''),']',''),0,CHARINDEX('|',REPLACE(REPLACE([7],'[',''),']',''))) END AS [7]
 ,CASE WHEN LEN([7]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([7],'[',''),']',''),CHARINDEX('|',REPLACE(REPLACE([7],'[',''),']',''))+1,LEN([7])-CHARINDEX('|',REVERSE(REPLACE(REPLACE([7],'[',''),']','')))-CHARINDEX('|',REPLACE(REPLACE([7],'[',''),']','')))
 END AS [7_Label]
 ,CASE WHEN LEN([7]) = 0
 THEN NULL
 ELSE REVERSE(SUBSTRING(REVERSE(REPLACE(REPLACE([7],'[',''),']','')),0,CHARINDEX('|',REVERSE(REPLACE(REPLACE([7],'[',''),']','')))))
 END AS [7_Description]
 -------
 ,[8] AS [8_Path]
 ,CASE WHEN LEN([8]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([8],'[',''),']',''),0,CHARINDEX('|',REPLACE(REPLACE([8],'[',''),']',''))) END AS [8]
 ,CASE WHEN LEN([8]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([8],'[',''),']',''),CHARINDEX('|',REPLACE(REPLACE([8],'[',''),']',''))+1,LEN([8])-CHARINDEX('|',REVERSE(REPLACE(REPLACE([8],'[',''),']','')))-CHARINDEX('|',REPLACE(REPLACE([8],'[',''),']','')))
 END AS [8_Label]
 ,CASE WHEN LEN([8]) = 0
 THEN NULL
 ELSE REVERSE(SUBSTRING(REVERSE(REPLACE(REPLACE([8],'[',''),']','')),0,CHARINDEX('|',REVERSE(REPLACE(REPLACE([8],'[',''),']','')))))
 END AS [8_Description]
 -------
 ,[9] AS [9_Path]
 ,CASE WHEN LEN([9]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([9],'[',''),']',''),0,CHARINDEX('|',REPLACE(REPLACE([9],'[',''),']',''))) END AS [9]
 ,CASE WHEN LEN([9]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([9],'[',''),']',''),CHARINDEX('|',REPLACE(REPLACE([9],'[',''),']',''))+1,LEN([9])-CHARINDEX('|',REVERSE(REPLACE(REPLACE([9],'[',''),']','')))-CHARINDEX('|',REPLACE(REPLACE([9],'[',''),']','')))
 END AS [9_Label]
 ,CASE WHEN LEN([9]) = 0
 THEN NULL
 ELSE REVERSE(SUBSTRING(REVERSE(REPLACE(REPLACE([9],'[',''),']','')),0,CHARINDEX('|',REVERSE(REPLACE(REPLACE([9],'[',''),']','')))))
 END AS [9_Description]
 -------
 ,[10] AS [10_Path]
 ,CASE WHEN LEN([10]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([10],'[',''),']',''),0,CHARINDEX('|',REPLACE(REPLACE([10],'[',''),']',''))) END AS [10]
 ,CASE WHEN LEN([10]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([10],'[',''),']',''),CHARINDEX('|',REPLACE(REPLACE([10],'[',''),']',''))+1,LEN([10])-CHARINDEX('|',REVERSE(REPLACE(REPLACE([10],'[',''),']','')))-CHARINDEX('|',REPLACE(REPLACE([10],'[',''),']','')))
 END AS [10_Label]
 ,CASE WHEN LEN([10]) = 0
 THEN NULL
 ELSE REVERSE(SUBSTRING(REVERSE(REPLACE(REPLACE([10],'[',''),']','')),0,CHARINDEX('|',REVERSE(REPLACE(REPLACE([10],'[',''),']','')))))
 END AS [10_Description]
 -------
 ,[11] AS [11_Path]
 ,CASE WHEN LEN([11]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([11],'[',''),']',''),0,CHARINDEX('|',REPLACE(REPLACE([11],'[',''),']',''))) END AS [11]
 ,CASE WHEN LEN([11]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([11],'[',''),']',''),CHARINDEX('|',REPLACE(REPLACE([11],'[',''),']',''))+1,LEN([11])-CHARINDEX('|',REVERSE(REPLACE(REPLACE([11],'[',''),']','')))-CHARINDEX('|',REPLACE(REPLACE([11],'[',''),']','')))
 END AS [11_Label]
 ,CASE WHEN LEN([11]) = 0
 THEN NULL
 ELSE REVERSE(SUBSTRING(REVERSE(REPLACE(REPLACE([11],'[',''),']','')),0,CHARINDEX('|',REVERSE(REPLACE(REPLACE([11],'[',''),']','')))))
 END AS [11_Description]
 -------
 ,[12] AS [12_Path]
 ,CASE WHEN LEN([12]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([12],'[',''),']',''),0,CHARINDEX('|',REPLACE(REPLACE([12],'[',''),']',''))) END AS [12]
 ,CASE WHEN LEN([12]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([12],'[',''),']',''),CHARINDEX('|',REPLACE(REPLACE([12],'[',''),']',''))+1,LEN([12])-CHARINDEX('|',REVERSE(REPLACE(REPLACE([12],'[',''),']','')))-CHARINDEX('|',REPLACE(REPLACE([12],'[',''),']','')))
 END AS [12_Label]
 ,CASE WHEN LEN([12]) = 0
 THEN NULL
 ELSE REVERSE(SUBSTRING(REVERSE(REPLACE(REPLACE([12],'[',''),']','')),0,CHARINDEX('|',REVERSE(REPLACE(REPLACE([12],'[',''),']','')))))
 END AS [12_Description]
 -------
 ,[13] AS [13_Path]
 ,CASE WHEN LEN([13]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([13],'[',''),']',''),0,CHARINDEX('|',REPLACE(REPLACE([13],'[',''),']',''))) END AS [13]
 ,CASE WHEN LEN([13]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([13],'[',''),']',''),CHARINDEX('|',REPLACE(REPLACE([13],'[',''),']',''))+1,LEN([13])-CHARINDEX('|',REVERSE(REPLACE(REPLACE([13],'[',''),']','')))-CHARINDEX('|',REPLACE(REPLACE([13],'[',''),']','')))
 END AS [13_Label]
 ,CASE WHEN LEN([13]) = 0
 THEN NULL
 ELSE REVERSE(SUBSTRING(REVERSE(REPLACE(REPLACE([13],'[',''),']','')),0,CHARINDEX('|',REVERSE(REPLACE(REPLACE([13],'[',''),']','')))))
 END AS [13_Description]
 -------
 ,[14] AS [14_Path]
 ,CASE WHEN LEN([14]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([14],'[',''),']',''),0,CHARINDEX('|',REPLACE(REPLACE([14],'[',''),']',''))) END AS [14]
 ,CASE WHEN LEN([14]) = 0
 THEN NULL
 ELSE SUBSTRING(REPLACE(REPLACE([14],'[',''),']',''),CHARINDEX('|',REPLACE(REPLACE([14],'[',''),']',''))+1,LEN([14])-CHARINDEX('|',REVERSE(REPLACE(REPLACE([14],'[',''),']','')))-CHARINDEX('|',REPLACE(REPLACE([14],'[',''),']','')))
 END AS [14_Label]
 ,CASE WHEN LEN([14]) = 0
 THEN NULL
 ELSE REVERSE(SUBSTRING(REVERSE(REPLACE(REPLACE([14],'[',''),']','')),0,CHARINDEX('|',REVERSE(REPLACE(REPLACE([14],'[',''),']','')))))
 END AS [14_Description]
 -------
FROM
(
 SELECT cte1.ItemID, num, CAST(split+'|'+CAST(LTRIM(RTRIM(t4.Label))AS VARCHAR(20))+'|'+LTRIM(RTRIM(t5.[Description]))AS VARCHAR(8000)) AS split
 FROM CTE1
 INNER JOIN hfm.dbo.COMPANY_ACCOUNT_ITEM t4
 ON cte1.split = t4.ItemID

 INNER JOIN hfm.dbo.COMPANY_ACCOUNT_DESC t5</pre>
<pre> ON cte1.split = t5.ItemID
) AS sourcetable
PIVOT
(MAX(split) FOR num IN ([1], [2], [3], [4], [5],[6],[7],[8],[9],[10],[11],[12],[13],[14])
) AS pivottable
GO

This produces:

HFM3

Click to enlarge

Then I have a 4th view to filter alternate hierarchy paths and comment out the paths.

CREATE VIEW [dbo].[vw_HFM_Hierarchy]
AS
SELECT cte.[ItemID]
,t1.Label
 --,[1_Path]
 ,[1]
 ,[1_Label]
 ,[1_Description]
 --,[2_Path]
 ,[2]
 ,[2_Label]
 ,[2_Description]
 --,[3_Path]
 ,[3]
 ,[3_Label]
 ,[3_Description]
 --,[4_Path]
 ,[4]
 ,[4_Label]
 ,[4_Description]
 --,[5_Path]
 ,[5]
 ,[5_Label]
 ,[5_Description]
 -- ,[6_Path]
 ,[6]
 ,[6_Label]
 ,[6_Description]
 --,[7_Path]
 ,[7]
 ,[7_Label]
 ,[7_Description]
 -- ,[8_Path]
 ,[8]
 ,[8_Label]
 ,[8_Description]
 --,[9_Path]
 ,[9]
 ,[9_Label]
 ,[9_Description]
 -- ,[10_Path]
 ,[10]
 ,[10_Label]
 ,[10_Description]
 --,[11_Path]
 ,[11]
 ,[11_Label]
 ,[11_Description]
 -- ,[12_Path]
 ,[12]
 ,[12_Label]
 ,[12_Description]
 -- ,[13_Path]
 ,[13]
 ,[13_Label]
 ,[13_Description]
 --,[14_Path]
 ,[14]
 ,[14_Label]
 ,[14_Description]
 FROM [HFM].[dbo].[vw_HFM_CTE_Results] cte

 LEFT JOIN [hfm].[dbo].[COMPANY_ACCOUNT_ITEM] t1
 ON t1.ItemID = cte.ItemID

WHERE [FirstChildID] = -1
AND LEN([Label]) = 4
GO

This produces:

HFM4

Click to enlarge

The obvious downside with method is the static nature of the levels.

Happy Coding and Have Fun!

IBM Cognos for Microsoft Office

This is my first glanace at “IBM Cognos for Microsoft Office”. What is “IBM Cognos for MS Office”? “IBM Cognos for MS Office” provides an integrated environment for IBM Cognos products and MS Office and it’s free if you are licensed for IBM Cognos BI. You can use IBM Cognos for MS Office to select pieces of reports to embed in MS Excel workbooks, MS Word documents, or MS PowerPoint presentations, including data, metadata, headers, footers, and charts. It doesn’t seem to be built in MS Access or Outlook and I’m OK with that. You can use predefined reports or you can create new content using IBM Cognos PowerPlay Web, IBM Cognos Query Studio, IBM Cognos Business Insight Advanced, or IBM Cognos Report Studio.

I’m testing it with Cognos BI 10.2.1 FP2 and MS Office 2013 (32bit) on Windows 8.1 PC (64bit).

Office 2013

Office 2013

The tool has to be downloaded from ibm passport advantage and you need to look for and download “ofc_10.2_win_ml.tar.gz”

Once you have the compressed file opened you can install the tool on your PC  by running “\ofc_10.2_win_ml\win32\win32\issetup.exe”  It should install the software to “C:\Program Files (x86)\ibm\cognos\Cognos for Microsoft Office”. The reason I point this out there is a interesting folder called Automation going have to check this out. “C:\Program Files (x86)\ibm\cognos\Cognos for Microsoft Office\Automation” If anyone has worked with this please share.

Open your MS Office product. You should see a new tab on the ribbon called “IBM Cognos”

The first thing you are going have to do is setup the tool options to point to your Cognos server one time deal:

isapi –> http://servername/ibmcognos/cgi-bin/cognosisapi.dll

or

cgi –> http://servername/ibmcognos/cgi-bin/cognos.cgi

Then you can start Dragging and Dropping or Click on Import Content to pick specific content from a report.

These are MS Excel worksheet example screenshots.

Office Excel 2013

Office Excel 2013

IBM_COGNOS_Office_Excel_2

Ribbon Options and Publish the Office Object to your Cognos connection environment:

IBM_COGNOS_Office_Excel_4

photofun-256526381

Logging Path: “C:\Users\username\AppData\Local\Cognos\Office Connection\Logs\”

IBM_COGNOS_Office_Excel_6

Review the imported elements on the second tab:

IBM_COGNOS_Office_Excel_3

When you run a report with prompt(s)  a html windows will open for you to select your prompt(s) and then the data will populate in the MS workspace. The tool seem to remembers your filter selections. Not sure how to pick new filters.

MS PowerPoint with Chart:

IBM_COGNOS_Office_PowerPoint

“IBM Cognos for Microsoft Office” seems to be replacement for CAFE (Cognos Analysis for Excel).

Have Fun!