April 21, 2009 at 7:19 am
I created a lot of code to extract source data into dimensions and facts in SSAS, and to maintain them going forward. Due to the development time, I created a cube with many dimensions to meet as many reporting variables as possible. It took a lot of time, though the learning curve was a factor.
When I saw the demo for Transformer I saw that this effort was potentially obsolete in Cognos, as a graphic interface allows you to drag and drop columns directly from source data into dimensions and facts, and save the definitions for reuse
The 2 gb limit on cube size may be a limitation, but I would be happy to break down my big cube into multiple smaller ones when it is this easy.
I'm very new to both tools and I would welcome any comparisons/critiques from experienced users
April 23, 2009 at 5:34 am
Just built my first cube in Cognos 8 transformer - that's about a tenth of the time it took in SSAS.
Still much to learn but I won't be bothering with SSAS until I find a reason to need it.
I would like to hear other views though
April 23, 2009 at 7:03 am
Must admit i have a penchant for the transformer UI also, but have no issues moving between the two (ie SSAS 05/08 takes at most 5% longer than Cognos). For most of the work we do, the scalability and control that AS affords, plus the price point for customers, makes it the engine of choice for us. Also, unless you 'unroll' any parent-child dims you have, or move to TM1 (which is then not using transfomer anymore anyways), the handling of p-c dims in transformer is difficult.
Steve.
April 23, 2009 at 7:05 am
Another question/point - did you not use the UI in SSAS to drag and drop cols to make your dims?? not sure either re: your reference to writing a lot of code to build the cube/s. While 90% of my cubes these days are created via code (built a tool to build cubes) creating them via the UI is not taxing at all (is it?)
Steve.
April 23, 2009 at 7:32 am
It was my first cube in SSAS, and I have had to teach myself as I go, but I could not see, and still can't see, an interface that lets me drag columns into diemensions. Is it an add-in, or is it something I am missing?
I have therefore defined a lot of dimension and fact tables in a database and written store procedures to extract the data from the base table (called problem, and with over 400 columns) in the source database, and load the relevant columns into the dimensions.
In Cognos Transformer, I did not have access to SQL Server tables, so I cheated by linking problem in an Access database, and connecting to it there. I then dragged the columns from problem into the dimensions.
Could you please tell me how I get to the UI to do this in SSAS. It has been the building and testing of the storeprocs that has taken the time
April 23, 2009 at 10:14 am
Ok, now i am really interested..... you wrote procs to load columns to the dims?? Can you post a small piece of one of these? Also, any other hints on exactly what tools/technologies you're workign with here??? Sounds 8almost* like you're generating XMLA commands via procs to then push up agaisnt the server. Obviously 'doable' but not necessarily the way i'd like to spend the day 😉
Anyways, to your primary question - if you fire up SQL Server BI Development Studio (aka Visual Studio), get yourself to the point of being ready to create a dimension (so, either connect to an existing DB, or create a new one, create the datasource and datasourceview). Then right click on the Dimensions folder, select Add new Dimension, it's likely to run through the wizard, this is more a 'checkbox columns i want in as attribute dimensions' than click and drag, but still just as easy. Once you're thru the dim wizard, you'll be in the dim editor (which i guess you could have gotten to for an existing dim, simply by double clickingthe existing dim in the dimensions folder).
In the dim editor, you can define new attribute (dimensions) by dragging column(s) from the table to the Attribute section. I believe you can also drag directly from the table cols to the (User) hierarchies pane, but personally i always add the attribute first and then create a hierarchy.
Steve.
April 24, 2009 at 5:43 am
Thanks for the instructions. I built one dimension and it seemed to work OK, although given I don't understand much of the terminology yet, it asked more questions than Cognos and left me with uncertainties.
I got stuck with the date dimension though. We cut our data by resolution year and month. Having picked the resolution date for the year, it was not available for the month. I can't see how that is going to work, except by maintaining a universal date dimension through a store proc, as I am at present.
In Cognos I had just dragged the resolution date to the dimension window and it created a date dimension, asking which elements I wanted to include.
Here is some of the code I created, if you can stand to wade through it :hehe:
Date Dimension (this is only adding dates at the moment and needs to remove old ones as we have a 24 month window either side of start of current year, that all our reporting is done on)
ALTER PROCEDURE [dbo].[usp_Create_DIM_Date]-- # of years to generate
AS
BEGIN
DECLARE
@RunTime datetime,
@today datetime,
@startDate datetime,
@stopDate datetime,
@curDate datetime,
@curQuarter tinyint,
@curSemester tinyint,
@curWeekNo smallint,
@curWeekName char(2),
@WeekYear smallint,-- The weeknumberyear ()
@WorkWeekDayNo tinyint,
@WorkWeekDayName as char(1),
@yearSpan smallint
SET @yearSpan = 4
SET @RunTime = GETDATE()
SET @today = '1/1/' + CAST(DATEPART(year,GETDATE()) AS char(4)) -- Start of current year
SET @startDate = SELECT MAX(FullDateAlternateKey) FROM DIM_Date
SET @stopDate = DATEADD(year, ROUND(@yearSpan / 2, 0),@today)-- Halve the year range and add to start of year
SET @curDate = @startDate
WHILE @curDate <= @stopDate
BEGIN
-- SET @timekey = @timekey + 1
SET @curQuarter = DATEPART(quarter, @curDate)
IF @curQuarter < 3
SET @curSemester = 1
ELSE
SET @curSemester = 2
IF DATEPART(dw, @curDate) = 1
BEGIN
SET @WorkWeekDayNo = 7
SET @WorkWeekDayName = '7'
END
ELSE
BEGIN
SET @WorkWeekDayNo = DATEPART(dw, @curDate) - 1
SET @WorkWeekDayName = CAST(@WorkWeekDayNo AS char(1))
END
---------------------------------------------------------------
-- Everything within the above dividers adapted from DateToWeek procedure used in Access
-- Calculates the weeknumber
SET @curWeekNo = 1 + CAST(CAST(@curDate - CAST('05-Jan-' + CAST(DATEPART(year, @curDate + 4 - DATEPART(dw, @curDate + 6 )) AS char(4)) AS datetime) + DATEPART(dw, CAST('03-Jan-' + CAST(DATEPART(year, @curDate + 4 - DATEPART(dw, @curDate + 6)) AS char(4)) AS datetime)) AS smallint)/7 AS smallint)
-- Adds leading 0 to weeknumbers less than 10
IF @curWeekNo < 10
SET @curWeekName = '0' + CAST(@curWeekNo AS char(1))
ELSE
SET @curWeekName = CAST(@curWeekNo AS char(2))
-- Calculates the correct week year if necesarry
-- If weekyear is one year ahead
IF (DATEPART(month,@curDate) = 12 AND @curWeekNo = 1)
SET @WeekYear = DATEPART(year,@curDate) + 1
-- If weekyear is one year after
ELSE
BEGIN
IF (DATEPART(month,@curDate) = 1 AND (@curWeekNo = 52
Or @curWeekNo = 53))
SET @WeekYear = DATEPART(year,@curDate) - 1
-- Same year
ELSE
SET @WeekYear = DATEPART(year,@curDate)
END
---------------------------------------------------------------
INSERT INTO[dbo].[DIM_Date]
( [FullDateAlternateKey]
, [DayNoOfWeek]
, [DayNoOfWorkWeek]
, [DayNoOfWeekChar]
, [DayNoOfWorkWeekChar]
, [DayNoOfMonth]
, [DayNoOfYear]
, [DayNameOfWeek]
, [ShortDayName]
, [WeekNoOfYear]
, [WeekNameOfYear]
, [MonthNoOfYear]
, [MonthNameOfYear]
, [ShortMonthName]
, [CalendarQuarterNo]
, [CalendarSemesterNo]
, [CalendarYearNo]
, [CalendarYearName]
, [WeekYearNo]
, [WeekYearName]
, [MondashYear]
, [MonthspaceYear]
, [MonYear]
, [MonYr]
, [Date_Added]
, [Date_Changed] )
VALUES (
@curDate-- FullDateAlternateKey
,DATEPART(dw, @curDate)-- DayNoOfWeek
,@WorkWeekDayNo-- DayNoOfWorkWeek
,CAST(DATEPART(dw, @curDate) AS char(1))-- DayNoOfWeekChar
,@WorkWeekDayName-- DayNoOfWorkWeekChar
,DATEPART(d, @curDate)-- DayNoOfMonth
,DATEPART(dy, @curDate)-- DayNoOfYear
,DATENAME(dw, @curDate)-- DayNameOfWeek
,LEFT(DATENAME(dw, @curDate),3)-- ShortDayName
,@curWeekNo-- WeekNoOfYear
,@curWeekName-- WeeknameOfYear
,DATEPART(m, @curDate)-- MonthNoOfYear
,DATENAME(month, @curDate)-- MonthNameOfYear
,LEFT(DATENAME(month, @curDate),3)-- ShortMonthName
,@curQuarter-- CalendarQuarterNo
,@curSemester-- CalendarSemesterNo
,DATEPART(yyyy, @curDate)-- CalendarYearNo
,DATENAME(yyyy, @curDate)-- CalendarYearName
,@WeekYear-- WeekYearNo
,CAST(@WeekYear AS char(4))-- WeekYearName
,LEFT(DATENAME(month, @curDate),3) + '-' + DATENAME(yyyy, @curDate)-- MondashYear
,DATENAME(month, @curDate) + ' ' + DATENAME(yyyy, @curDate)-- MondashYear
,LEFT(DATENAME(month, @curDate),3) + DATENAME(yyyy, @curDate)-- MonYear
,LEFT(DATENAME(month, @curDate),3) + RIGHT(DATENAME(yyyy, @curDate),2)-- MonthYr
,@RunTime-- Date Added
,@RunTime)-- Date Changed
SET @curDate = DATEADD(day,1,@curDate)
END
END
Sample standard dimension
CREATE PROCEDURE [dbo].[usp_Maintain_DIM_Ticket_SCIM]
AS
-- =============================================
-- Remove this code from live versiom
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[Closing SCIM]') AND parent_object_id = OBJECT_ID(N'[dbo].[FACT_Ticket]'))
ALTER TABLE [dbo].[FACT_Ticket] DROP CONSTRAINT [Closing SCIM]
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[Original SCIM]') AND parent_object_id = OBJECT_ID(N'[dbo].[FACT_Ticket]'))
ALTER TABLE [dbo].[FACT_Ticket] DROP CONSTRAINT [Original SCIM]
Truncate table dbo.DIM_Ticket_SCIM
ALTER TABLE [dbo].[FACT_Ticket] WITH NOCHECK ADD CONSTRAINT [Original SCIM] FOREIGN KEY([FKey_Ticket_Original_SCIM_Id])
REFERENCES [dbo].[DIM_Ticket_SCIM] ([Pkey_SCIM_Id])
ALTER TABLE [dbo].[FACT_Ticket] NOCHECK CONSTRAINT [Original SCIM]
ALTER TABLE [dbo].[FACT_Ticket] WITH NOCHECK ADD CONSTRAINT [Closing SCIM] FOREIGN KEY([FKey_Ticket_Closing_SCIM_Id])
REFERENCES [dbo].[DIM_Ticket_SCIM] ([Pkey_SCIM_Id])
ALTER TABLE [dbo].[FACT_Ticket] NOCHECK CONSTRAINT [Closing SCIM]
-- =============================================
-- Add a record with null values if the table is being populated for the first time
IF (SELECT Count(PKey_SCIM_Id) FROM DIM_Ticket_SCIM) = 0
BEGIN
INSERT INTO [DIM_Ticket_SCIM]
(Date_added, Date_changed)
SELECT GETDATE() AS Added, GETDATE() AS Changed
END
DECLARE @runtime datetime
SET @runtime = getdate()
INSERT INTO [DIM_Ticket_SCIM]
(SCIM,
SCIM_System,
SCIM_Component,
SCIM_Item,
SCIM_Module,
Date_added,
Date_changed)
SELECT DISTINCT
GoBI_Staging.dbo.Incident.scim_system +
GoBI_Staging.dbo.Incident.scim_comp +
GoBI_Staging.dbo.Incident.scim_item +
GoBI_Staging.dbo.Incident.scim_module,
GoBI_Staging.dbo.Incident.scim_system,
GoBI_Staging.dbo.Incident.scim_comp,
GoBI_Staging.dbo.Incident.scim_item,
GoBI_Staging.dbo.Incident.scim_module,
@runtime AS Added,
@runtime AS Changed
FROM GoBI_Staging.dbo.Incident LEFT OUTER JOIN
dbo.DIM_Ticket_SCIM
ONGoBI_Staging.dbo.Incident.scim_system = DIM_Ticket_SCIM.SCIM_System AND
GoBI_Staging.dbo.Incident.scim_comp = DIM_Ticket_SCIM.SCIM_Component AND
GoBI_Staging.dbo.Incident.scim_item = DIM_Ticket_SCIM.SCIM_Item AND
GoBI_Staging.dbo.Incident.scim_module = DIM_Ticket_SCIM.SCIM_Module
WHERE (DIM_Ticket_SCIM.PKey_SCIM_Id IS NULL )
ORDER BY GoBI_Staging.dbo.Incident.scim_system +
GoBI_Staging.dbo.Incident.scim_comp +
GoBI_Staging.dbo.Incident.scim_item +
GoBI_Staging.dbo.Incident.scim_module
SET @runtime = getdate()
INSERT INTO [DIM_Ticket_SCIM]
(SCIM,
SCIM_System,
SCIM_Component,
SCIM_Item,
SCIM_Module,
Date_added,
Date_changed)
SELECT DISTINCT
GoBI_Staging.dbo.Incident.original_scim_system +
GoBI_Staging.dbo.Incident.original_scim_component +
GoBI_Staging.dbo.Incident.original_scim_item +
GoBI_Staging.dbo.Incident.original_scim_module,
GoBI_Staging.dbo.Incident.original_scim_system,
GoBI_Staging.dbo.Incident.original_scim_component,
GoBI_Staging.dbo.Incident.original_scim_item,
GoBI_Staging.dbo.Incident.original_scim_module,
@runtime AS Added,
@runtime AS Changed
FROM GoBI_Staging.dbo.Incident LEFT OUTER JOIN
dbo.DIM_Ticket_SCIM
ONGoBI_Staging.dbo.Incident.original_scim_system = DIM_Ticket_SCIM.SCIM_System AND
GoBI_Staging.dbo.Incident.original_scim_component = DIM_Ticket_SCIM.SCIM_Component AND
GoBI_Staging.dbo.Incident.original_scim_item = DIM_Ticket_SCIM.SCIM_Item AND
GoBI_Staging.dbo.Incident.original_scim_module = DIM_Ticket_SCIM.SCIM_Module
WHERE (DIM_Ticket_SCIM.PKey_SCIM_Id IS NULL )
ORDER BY GoBI_Staging.dbo.Incident.original_scim_system +
GoBI_Staging.dbo.Incident.original_scim_component +
GoBI_Staging.dbo.Incident.original_scim_item +
GoBI_Staging.dbo.Incident.original_scim_module
FACT Table (I lifted this code from another website and tweaked it to fit my cube - if you're not crying yet, you will after this!)
CREATE PROCEDURE [dbo].[usp_Maintain_FACT_Ticket]
AS
SELECT * INTO #fact_ticket_temp FROM dbo.FACT_Ticket WHERE 1 = 0
--Populate the temporary table
SET IDENTITY_INSERT #fact_ticket_temp ON
DECLARE @runtime datetime
SET @runtime = getdate()
INSERT INTO [#fact_ticket_temp]
(PKey_Ticket_Id,
FKey_Ticket_Id,
FKey_Ticket_Resolution_Date_Id,
FKey_Ticket_Resolution_Date_GMT_Id,
FKey_Ticket_Open_Date_Id,
FKey_Ticket_Open_Date_GMT_Id,
FKey_Ticket_Reopen_Date_Id,
FKey_Ticket_Reopen_Date_GMT_Id,
FKey_Ticket_Close_Date_Id,
FKey_Ticket_Close_Date_GMT_Id,
FKey_Ticket_Acknowledge_Date_GMT_Id,
FKey_Ticket_Accountable_BU_Id,
FKey_Ticket_Accountable_LoB_Id,
FKey_Ticket_Accountable_Region_Id,
FKey_Ticket_AssignmentQ_Id,
FKey_Ticket_Asset_Name_Id,
FKey_Ticket_Asset_SubType_Id,
FKey_Ticket_Asset_Type_Id,
FKey_Ticket_Asset_Manufacturer_Id,
FKey_Ticket_Asset_Infrastructure_Location_Id,
FKey_Ticket_Client_Name_Id,
FKey_Ticket_Client_City_Id,
FKey_Ticket_Closing_SCIM_Id,
FKey_Ticket_Contact_Location_Id,
FKey_Ticket_Country_Id,
FKey_Ticket_Host_Name_Id,
FKey_Ticket_Impacted_Location_Id,
FKey_Ticket_LoB_Name_Id,
FKey_Ticket_Original_AssignmentQ_Id,
FKey_Ticket_Original_Priority_Id,
FKey_Ticket_Original_SCIM_Id,
FKey_Ticket_Priority_Severity_Id,
FKey_Ticket_Problem_Type_Id,
FKey_Ticket_Resolution_Code_Id,
FKey_Ticket_Resolver_Group_Id,
FKey_Ticket_Resolver_Stdid_Id,
FKey_Ticket_Resolver_Name_Id,
FKey_Ticket_Resolving_LoB_Id,
FKey_Ticket_Status_Id,
FKey_Ticket_SubProblem_Type_Id,
Impact_Duration_Mins,
Outage_Duration_Mins,
Date_Added,
Date_changed)
SELECTISNULL(f0.PKey_Ticket_Id, 0),
ISNULL(ti.PKey_Ticket_Id, 1),
ISNULL(d1.TimeKey, 1),
ISNULL(d2.TimeKey, 1),
ISNULL(d3.TimeKey, 1),
ISNULL(d4.TimeKey, 1),
ISNULL(d5.TimeKey, 1),
ISNULL(d6.TimeKey, 1),
ISNULL(d7.TimeKey, 1),
ISNULL(d8.TimeKey, 1),
ISNULL(d9.TimeKey, 1),
ISNULL(bu.PKey_Accountable_BU_Id, 1),
ISNULL(lb.PKey_LoB_Id, 1),
ISNULL(r0.PKey_Region_Id, 1),
ISNULL(q0.PKey_AssignmentQ_Id, 1),
ISNULL(a0.PKey_Asset_Name_Id, 1),
ISNULL(a1.PKey_Asset_SubType_Id, 1),
ISNULL(a2.PKey_Asset_Type_Id, 1),
ISNULL(a3.PKey_Asset_Manufacturer_Id, 1),
ISNULL(a4.PKey_Asset_Infrastructure_Location_Id, 1),
ISNULL(cn.PK_Client_Name_Id, 1),
ISNULL(cc.PK_Client_City_Id, 1),
ISNULL(cs.Pkey_SCIM_Id, 1),
ISNULL(l0.PKey_Location_Id, 1),
ISNULL(cy.PKey_Country_Id, 1),
ISNULL(hn.PKey_Host_Name_Id, 1),
ISNULL(l1.PKey_Location_Id, 1),
ISNULL(ln.PK_LoB_Name_Id, 1),
ISNULL(q1.PKey_AssignmentQ_Id, 1),
ISNULL(op.PKey_Priority_Severity_Id, 1),
ISNULL(os.Pkey_SCIM_Id, 1),
ISNULL(ps.PKey_Priority_Severity_Id, 1),
ISNULL(pt.PKey_Problem_Type_Id, 1),
ISNULL(rc.PKey_Resolution_Code_Id, 1),
ISNULL(q2.PKey_AssignmentQ_Id, 1),
ISNULL(s0.PKey_StdId_Id, 1),
ISNULL(p0.PKey_Person_Id, 1),
ISNULL(rl.PKey_LoB_Id, 1),
ISNULL(st.PKey_Status_Id, 1),
ISNULL(sp.PKey_SubProblem_Type_Id, 1),
i0.jp_impact_duration_min,
i0.jp_outage_duration_min,
@runtime,
@runtime
FROM GoBI_Staging.dbo.IncidentAS i0 LEFT OUTER JOIN
DIM_TicketAS ti ON i0.ticket_nbr = ti.Ticket_Number LEFT OUTER JOIN
DIM_DateAS d1 ON i0.WORK_resolver_resolution_dt = d1.FullDateAlternateKey LEFT OUTER JOIN
DIM_DateAS d2 ON i0.WORK_resolver_resolution_dt_gmt = d2.FullDateAlternateKey LEFT OUTER JOIN
DIM_DateAS d3 ON i0.WORK_open_dt = d3.FullDateAlternateKey LEFT OUTER JOIN
DIM_DateAS d4 ON i0.WORK_open_dt_gmt = d4.FullDateAlternateKey LEFT OUTER JOIN
DIM_DateAS d5 ON i0.WORK_reopen_dt = d5.FullDateAlternateKey LEFT OUTER JOIN
DIM_DateAS d6 ON i0.WORK_reopen_dt_gmt = d6.FullDateAlternateKey LEFT OUTER JOIN
DIM_DateAS d7 ON i0.WORK_closed_dt = d7.FullDateAlternateKey LEFT OUTER JOIN
DIM_DateAS d8 ON i0.WORK_closed_dt_gmt = d8.FullDateAlternateKey LEFT OUTER JOIN
DIM_DateAS d9 ON i0.WORK_acknowledge_dt_gmt = d9.FullDateAlternateKey LEFT OUTER JOIN
DIM_Ticket_Accountable_BUAS bu ON i0.jp_accountable_bu = bu.Accountable_BU LEFT OUTER JOIN
DIM_Ticket_LoBAS lb ON i0.jp_acct_lob = lb.LoB LEFT OUTER JOIN
DIM_Ticket_RegionAS r0 ON i0.b1_accountable_region = r0.Region LEFT OUTER JOIN
DIM_Ticket_Assignment_QueueAS q0 ON i0.assignment_grp = q0.AssignmentQ LEFT OUTER JOIN
DIM_Ticket_Asset_NameAS a0 ON i0.asset_name = a0.AssetName LEFT OUTER JOIN
DIM_Ticket_Asset_SubTypeAS a1 ON i0.Asset_SubType = a1.Asset_SubType LEFT OUTER JOIN
DIM_Ticket_Asset_TypeAS a2 ON i0.asset_type = a2.Asset_Type LEFT OUTER JOIN
DIM_Ticket_Asset_ManufacturerAS a3 ON i0.manufacturer = a3.Asset_Manufacturer LEFT OUTER JOIN
DIM_Ticket_Asset_Infrastructure_LocationAS a4 ON i0.jp_location_infrastructure = a4.Asset_Infrastructure_Location LEFT OUTER JOIN
DIM_Ticket_Client_NameAS cn ON i0.client_name = cn.Client_Name LEFT OUTER JOIN
DIM_Ticket_Client_CityAS cc ON i0.client_city = cc.Client_City LEFT OUTER JOIN
DIM_Ticket_SCIMAS cs ON (i0.scim_system = cs.SCIM_System AND
i0.scim_comp = cs.SCIM_Component AND
i0.scim_item = cs.SCIM_Item AND
i0.scim_module = cs.SCIM_Module) LEFT OUTER JOIN
DIM_Ticket_LocationAS l0 ON i0.contact_location = l0.Location LEFT OUTER JOIN
DIM_Ticket_CountryAS cy ON i0.country = cy.Country LEFT OUTER JOIN
DIM_Ticket_Host_NameAS hn ON i0.hostname = hn.Host_Name LEFT OUTER JOIN
DIM_Ticket_LocationAS l1 ON i0.jp_impacted_location = l1.Location LEFT OUTER JOIN
DIM_Ticket_LoB_NameAS ln ON (i0.lob_name = ln.LOB_Name AND
i0.lob_code = ln.LOB_Code) LEFT OUTER JOIN
DIM_Ticket_Assignment_QueueAS q1 ON i0.original_group = q1.AssignmentQ LEFT OUTER JOIN
DIM_Ticket_Priority_SeverityAS op ON i0.original_priority = op.Priority_Severity LEFT OUTER JOIN
DIM_Ticket_SCIMAS os ON (i0.original_scim_system = os.SCIM_System AND
i0.original_scim_component = os.SCIM_Component AND
i0.original_scim_item = os.SCIM_Item AND
i0.original_scim_module = os.SCIM_Module) LEFT OUTER JOIN
DIM_Ticket_Priority_SeverityAS ps ON i0.priority_severity = ps.Priority_Severity LEFT OUTER JOIN
DIM_Ticket_Problem_TypeAS pt ON i0.problem_type = pt.Problem_Type LEFT OUTER JOIN
DIM_Ticket_Resolution_CodeAS rc ON i0.resolution_code = rc.Resolution_Code LEFT OUTER JOIN
DIM_Ticket_Assignment_QueueAS q2 ON i0.resolver_group = q2.AssignmentQ LEFT OUTER JOIN
DIM_StdIdAS s0 ON i0.resolver_stdid = s0.StdID LEFT OUTER JOIN
DIM_PersonAS p0 ON i0.resolver_name = p0.Name LEFT OUTER JOIN
DIM_Ticket_LoBAS rl ON i0.lob = rl.LoB LEFT OUTER JOIN
DIM_Ticket_StatusAS st ON i0.status = st.Status LEFT OUTER JOIN
DIM_Ticket_SubProblem_TypeAS sp ON i0.b1_subproblem_type = sp.SubProblem_Type LEFT OUTER JOIN
FACT_TicketAS f0 ON f0.PKey_Ticket_Id = ti.PKey_Ticket_Id
--update the fact table with changed data
UPDATE f
SETf.FKey_Ticket_Id= t.FKey_Ticket_Id,
f.FKey_Ticket_Resolution_Date_Id= t.FKey_Ticket_Resolution_Date_Id,
f.FKey_Ticket_Resolution_Date_GMT_Id= t.FKey_Ticket_Resolution_Date_GMT_Id,
f.FKey_Ticket_Open_Date_Id= t.FKey_Ticket_Open_Date_Id,
f.FKey_Ticket_Open_Date_GMT_Id= t.FKey_Ticket_Open_Date_GMT_Id,
f.FKey_Ticket_Reopen_Date_Id= t.FKey_Ticket_Reopen_Date_Id,
f.FKey_Ticket_Reopen_Date_GMT_Id= t.FKey_Ticket_Reopen_Date_GMT_Id,
f.FKey_Ticket_Close_Date_Id= t.FKey_Ticket_Close_Date_Id,
f.FKey_Ticket_Close_Date_GMT_Id= t.FKey_Ticket_Close_Date_GMT_Id,
f.FKey_Ticket_Acknowledge_Date_GMT_Id= t.FKey_Ticket_Acknowledge_Date_GMT_Id,
f.FKey_Ticket_Accountable_BU_Id= t.FKey_Ticket_Accountable_BU_Id,
f.FKey_Ticket_Accountable_LoB_Id= t.FKey_Ticket_Accountable_LoB_Id,
f.FKey_Ticket_Accountable_Region_Id= t.FKey_Ticket_Accountable_Region_Id,
f.FKey_Ticket_AssignmentQ_Id= t.FKey_Ticket_AssignmentQ_Id,
f.FKey_Ticket_Asset_Name_Id= t.FKey_Ticket_Asset_Name_Id,
f.FKey_Ticket_Asset_SubType_Id= t.FKey_Ticket_Asset_SubType_Id,
f.FKey_Ticket_Asset_Type_Id= t.FKey_Ticket_Asset_Type_Id,
f.FKey_Ticket_Asset_Manufacturer_Id= t.FKey_Ticket_Asset_Manufacturer_Id,
f.FKey_Ticket_Asset_Infrastructure_Location_Id= t.FKey_Ticket_Asset_Infrastructure_Location_Id,
f.FKey_Ticket_Client_Name_Id= t.FKey_Ticket_Client_Name_Id,
f.FKey_Ticket_Client_City_Id= t.FKey_Ticket_Client_City_Id,
f.FKey_Ticket_Closing_SCIM_Id= t.FKey_Ticket_Closing_SCIM_Id,
f.FKey_Ticket_Contact_Location_Id= t.FKey_Ticket_Contact_Location_Id,
f.FKey_Ticket_Country_Id= t.FKey_Ticket_Country_Id,
f.FKey_Ticket_Host_Name_Id= t.FKey_Ticket_Host_Name_Id,
f.FKey_Ticket_Impacted_Location_Id= t.FKey_Ticket_Impacted_Location_Id,
f.FKey_Ticket_LoB_Name_Id= t.FKey_Ticket_LoB_Name_Id,
f.FKey_Ticket_Original_AssignmentQ_Id= t.FKey_Ticket_Original_AssignmentQ_Id,
f.FKey_Ticket_Original_Priority_Id= t.FKey_Ticket_Original_Priority_Id,
f.FKey_Ticket_Original_SCIM_Id= t.FKey_Ticket_Original_SCIM_Id,
f.FKey_Ticket_Priority_Severity_Id= t.FKey_Ticket_Priority_Severity_Id,
f.FKey_Ticket_Problem_Type_Id= t.FKey_Ticket_Problem_Type_Id,
f.FKey_Ticket_Resolution_Code_Id= t.FKey_Ticket_Resolution_Code_Id,
f.FKey_Ticket_Resolver_Group_Id= t.FKey_Ticket_Resolver_Group_Id,
f.FKey_Ticket_Resolver_Stdid_Id= t.FKey_Ticket_Resolver_Stdid_Id,
f.FKey_Ticket_Resolver_Name_Id= t.FKey_Ticket_Resolver_Name_Id,
f.FKey_Ticket_Resolving_LoB_Id= t.FKey_Ticket_Resolving_LoB_Id,
f.FKey_Ticket_Status_Id= t.FKey_Ticket_Status_Id,
f.FKey_Ticket_SubProblem_Type_Id= t.FKey_Ticket_SubProblem_Type_Id,
f.Impact_Duration_Mins= t.Impact_Duration_Mins,
f.Outage_Duration_Mins= t.Outage_Duration_Mins,
f.Date_changed= t.Date_changed
FROM dbo.FACT_Ticket f
INNER JOIN #fact_ticket_temp t ON f.PKey_Ticket_Id = t.PKey_Ticket_Id
WHERE t.PKey_Ticket_Id 0 AND
(f.FKey_Ticket_Id t.FKey_Ticket_Id OR
f.FKey_Ticket_Resolution_Date_Id t.FKey_Ticket_Resolution_Date_Id OR
f.FKey_Ticket_Resolution_Date_GMT_Id t.FKey_Ticket_Resolution_Date_GMT_Id OR
f.FKey_Ticket_Open_Date_Id t.FKey_Ticket_Open_Date_Id OR
f.FKey_Ticket_Open_Date_GMT_Id t.FKey_Ticket_Open_Date_GMT_Id OR
f.FKey_Ticket_Reopen_Date_Id t.FKey_Ticket_Reopen_Date_Id OR
f.FKey_Ticket_Reopen_Date_GMT_Id t.FKey_Ticket_Reopen_Date_GMT_Id OR
f.FKey_Ticket_Close_Date_Id t.FKey_Ticket_Close_Date_Id OR
f.FKey_Ticket_Close_Date_GMT_Id t.FKey_Ticket_Close_Date_GMT_Id OR
f.FKey_Ticket_Acknowledge_Date_GMT_Id t.FKey_Ticket_Acknowledge_Date_GMT_Id OR
f.FKey_Ticket_Accountable_BU_Id t.FKey_Ticket_Accountable_BU_Id OR
f.FKey_Ticket_Accountable_LoB_Id t.FKey_Ticket_Accountable_LoB_Id OR
f.FKey_Ticket_Accountable_Region_Id t.FKey_Ticket_Accountable_Region_Id OR
f.FKey_Ticket_AssignmentQ_Id t.FKey_Ticket_AssignmentQ_Id OR
f.FKey_Ticket_Asset_Name_Id t.FKey_Ticket_Asset_Name_Id OR
f.FKey_Ticket_Asset_SubType_Id t.FKey_Ticket_Asset_SubType_Id OR
f.FKey_Ticket_Asset_Type_Id t.FKey_Ticket_Asset_Type_Id OR
f.FKey_Ticket_Asset_Manufacturer_Id t.FKey_Ticket_Asset_Manufacturer_Id OR
f.FKey_Ticket_Asset_Infrastructure_Location_Id t.FKey_Ticket_Asset_Infrastructure_Location_Id OR
f.FKey_Ticket_Client_Name_Id t.FKey_Ticket_Client_Name_Id OR
f.FKey_Ticket_Client_City_Id t.FKey_Ticket_Client_City_Id OR
f.FKey_Ticket_Closing_SCIM_Id t.FKey_Ticket_Closing_SCIM_Id OR
f.FKey_Ticket_Contact_Location_Id t.FKey_Ticket_Contact_Location_Id OR
f.FKey_Ticket_Country_Id t.FKey_Ticket_Country_Id OR
f.FKey_Ticket_Host_Name_Id t.FKey_Ticket_Host_Name_Id OR
f.FKey_Ticket_Impacted_Location_Id t.FKey_Ticket_Impacted_Location_Id OR
f.FKey_Ticket_LoB_Name_Id t.FKey_Ticket_LoB_Name_Id OR
f.FKey_Ticket_Original_AssignmentQ_Id t.FKey_Ticket_Original_AssignmentQ_Id OR
f.FKey_Ticket_Original_Priority_Id t.FKey_Ticket_Original_Priority_Id OR
f.FKey_Ticket_Original_SCIM_Id t.FKey_Ticket_Original_SCIM_Id OR
f.FKey_Ticket_Priority_Severity_Id t.FKey_Ticket_Priority_Severity_Id OR
f.FKey_Ticket_Problem_Type_Id t.FKey_Ticket_Problem_Type_Id OR
f.FKey_Ticket_Resolution_Code_Id t.FKey_Ticket_Resolution_Code_Id OR
f.FKey_Ticket_Resolver_Group_Id t.FKey_Ticket_Resolver_Group_Id OR
f.FKey_Ticket_Resolver_Stdid_Id t.FKey_Ticket_Resolver_Stdid_Id OR
f.FKey_Ticket_Resolver_Name_Id t.FKey_Ticket_Resolver_Name_Id OR
f.FKey_Ticket_Resolving_LoB_Id t.FKey_Ticket_Resolving_LoB_Id OR
f.FKey_Ticket_Status_Id t.FKey_Ticket_Status_Id OR
f.FKey_Ticket_SubProblem_Type_Id t.FKey_Ticket_SubProblem_Type_Id OR
f.Impact_Duration_Mins t.Impact_Duration_Mins OR
f.Outage_Duration_Mins t.Outage_Duration_Mins)
--add new data to the fact table
INSERT INTO dbo.FACT_Ticket (
FKey_Ticket_Id,
FKey_Ticket_Resolution_Date_Id,
FKey_Ticket_Resolution_Date_GMT_Id,
FKey_Ticket_Open_Date_Id,
FKey_Ticket_Open_Date_GMT_Id,
FKey_Ticket_Reopen_Date_Id,
FKey_Ticket_Reopen_Date_GMT_Id,
FKey_Ticket_Close_Date_Id,
FKey_Ticket_Close_Date_GMT_Id,
FKey_Ticket_Acknowledge_Date_GMT_Id,
FKey_Ticket_Accountable_BU_Id,
FKey_Ticket_Accountable_LoB_Id,
FKey_Ticket_Accountable_Region_Id,
FKey_Ticket_AssignmentQ_Id,
FKey_Ticket_Asset_Name_Id,
FKey_Ticket_Asset_SubType_Id,
FKey_Ticket_Asset_Type_Id,
FKey_Ticket_Asset_Manufacturer_Id,
FKey_Ticket_Asset_Infrastructure_Location_Id,
FKey_Ticket_Client_Name_Id,
FKey_Ticket_Client_City_Id,
FKey_Ticket_Closing_SCIM_Id,
FKey_Ticket_Contact_Location_Id,
FKey_Ticket_Country_Id,
FKey_Ticket_Host_Name_Id,
FKey_Ticket_Impacted_Location_Id,
FKey_Ticket_LoB_Name_Id,
FKey_Ticket_Original_AssignmentQ_Id,
FKey_Ticket_Original_Priority_Id,
FKey_Ticket_Original_SCIM_Id,
FKey_Ticket_Priority_Severity_Id,
FKey_Ticket_Problem_Type_Id,
FKey_Ticket_Resolution_Code_Id,
FKey_Ticket_Resolver_Group_Id,
FKey_Ticket_Resolver_Stdid_Id,
FKey_Ticket_Resolver_Name_Id,
FKey_Ticket_Resolving_LoB_Id,
FKey_Ticket_Status_Id,
FKey_Ticket_SubProblem_Type_Id,
Impact_Duration_Mins,
Outage_Duration_Mins,
Date_Added,
Date_changed )
SELECT FKey_Ticket_Id,
FKey_Ticket_Resolution_Date_Id,
FKey_Ticket_Resolution_Date_GMT_Id,
FKey_Ticket_Open_Date_Id,
FKey_Ticket_Open_Date_GMT_Id,
FKey_Ticket_Reopen_Date_Id,
FKey_Ticket_Reopen_Date_GMT_Id,
FKey_Ticket_Close_Date_Id,
FKey_Ticket_Close_Date_GMT_Id,
FKey_Ticket_Acknowledge_Date_GMT_Id,
FKey_Ticket_Accountable_BU_Id,
FKey_Ticket_Accountable_LoB_Id,
FKey_Ticket_Accountable_Region_Id,
FKey_Ticket_AssignmentQ_Id,
FKey_Ticket_Asset_Name_Id,
FKey_Ticket_Asset_SubType_Id,
FKey_Ticket_Asset_Type_Id,
FKey_Ticket_Asset_Manufacturer_Id,
FKey_Ticket_Asset_Infrastructure_Location_Id,
FKey_Ticket_Client_Name_Id,
FKey_Ticket_Client_City_Id,
FKey_Ticket_Closing_SCIM_Id,
FKey_Ticket_Contact_Location_Id,
FKey_Ticket_Country_Id,
FKey_Ticket_Host_Name_Id,
FKey_Ticket_Impacted_Location_Id,
FKey_Ticket_LoB_Name_Id,
FKey_Ticket_Original_AssignmentQ_Id,
FKey_Ticket_Original_Priority_Id,
FKey_Ticket_Original_SCIM_Id,
FKey_Ticket_Priority_Severity_Id,
FKey_Ticket_Problem_Type_Id,
FKey_Ticket_Resolution_Code_Id,
FKey_Ticket_Resolver_Group_Id,
FKey_Ticket_Resolver_Stdid_Id,
FKey_Ticket_Resolver_Name_Id,
FKey_Ticket_Resolving_LoB_Id,
FKey_Ticket_Status_Id,
FKey_Ticket_SubProblem_Type_Id,
Impact_Duration_Mins,
Outage_Duration_Mins,
Date_Added,
Date_changed
FROM #fact_ticket_temp
WHERE PKey_Ticket_Id = 0
Drop table #fact_ticket_temp
Sorry! But you did ask ...
April 24, 2009 at 9:25 am
sooo glad i asked.... :hehe:
ok, on the date dim, you *could* get AS to create you a server based time dimension, where you provide some info thru the wizard and it generates the dim for you (ie no source data). My personal preference has always been to maintain a time table and use this for dims, but to each his own. In many ways, AS will give you the same 'create me a time dim with these attributes' as Transformer does, but i agree, clicking and dragging a field prescribed as a datetime and just 'having it happen' is a nice feature.
Ok, on the dims and facts. Unless I'm missing something, all of this work is required to generate your dims and facts regardless of whether you're using transformer or AS.
Actually, on re-reading your other posts, i think i see what you're saying. In Cognos, you brought in *just* the Problem table, and then created dims and measures directly from it, rather than having (say) a table per dim and a central fact table. Ok, so yes, this is possible in AS also but is potentially a performance problem for both environments. It's been a while since i sat thru a process operation for Transformer, but I *think* it will read (ie full table scan) your entire fact (let's call it source) table for every dimension you've created, and then read it again for the facts/measures. Also, from memory, best practices for Transformer indicates that you should use multiple data sources where possible (in fact, a lifetime ago when i used to train this, the 'intro' course was all single datasource, and then the 2 day advanced moved to multiple data sources. I'm guessing that you haven't used the window yet that let's you see which measures are related to which dimensions and at which level. It's color coded, and is basically rendered useless if you're not using multiple data sources, but i digress ;)).
Last thing i'd say (re: the fact build) is have you heard of SSIS? It would be another learning curve, and yes, Cognos has it's own ETL also, but keeping on the MSft track, where you're doing a squillion outer joins to effectively look up your FK value for the dim, you could use SSIS and in the dataflow use a lookup task, which can store data in memory (read as nice and fast 😉 ). It would also give you a full graphical interface for the maintenance of these load tasks, plus strong scheduling etc. Just a thought....
HTH,
Steve.
April 24, 2009 at 9:43 am
Thanks for all your input, Steve. That's the kind of thing I've been hoping for. I work for a very large multi-national and I'm sitting alone in the UK with my self-taught SQL Server stuff. The learning curve is massive when there is no-one else to bang heads with.
Having created that cube in SSAS I felt I could build subsequent ones much more quickly and cut and paste code from existing Store Procs. As a lifelong programmer (PLAN, Cobol, Dbase, Visual Basic - anything but SQL) I was happy with that level of control. Yes, I have done SSIS. I still find it fiddly and confusing but, again, it's the learning curve.
Cognos arrived on my plate from the US fairly recently. I saw a 90 minute demo of cube building 2 weeks ago, and then someone sent me Transformer to install this Wednesday. I leapt in while the demo was still fairly fresh.
The support for Cognos in the rest of the team will mean I have to do things that way. In all honesty, if you saw the level of reporting and the speed at which it is required, I think that will be the best solution for most reports. My suspicion is that something that high-level may be limited when it comes to more complex or higher volume reports. In this case I believe the Cognos reporting tools could connect to an SSAS cube instead.
Your input has been brilliant for helping me form opinions and moving me on with my understanding. Thank you very much
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply