MDX hierarchy problem: current

Showing posts with label current. Show all posts

Monday, March 26, 2012

Membership Timeline Spanning

Span example:
--M--
--Rx--
Needs to b converted to this:
--M--|--M & Rx--|--Rx--
/* What current base data displays
MEM_ID COV_BEGIN_DT COV_END_DT MED_BEN_IND PHRM_BEN_IND
-- -- -- -- --
27 20050101 20050412 Y N
27 20050201 20050813 N Y
27 20050603 99991231 Y N
*/
/* Should be converted as this?
MEM_ID COV_BEGIN_DT COV_END_DT MED_BEN_IND PHRM_BEN_IND
-- -- -- -- --
27 20050101 20050131 Y N
27 20050201 20050412 Y Y
27 20050413 20050602 N Y
27 20050603 20050813 Y Y
27 20050814 99991231 Y N
*/
DDL to create the table and then some sample data
CREATE TABLE [dbo].[ODS_BLK_Member] (
[BHI_HOME_PLAN_ID] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HOME_PLAN_PRODUCT_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[MEM_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CONS_MEM_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TRACEABILITY_FIELD] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[MEM_DOB] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MEM_ZIP] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MEM_COUNTRY] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MEM_COUNTY] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MEM_GENDER] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MEM_CONFIDENTIALITY_CDE] [varchar] (3) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[ACCOUNT] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GROUP] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SUBGROUP] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COV_BEGIN_DT] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COV_END_DT] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MEM_RTI] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HOME_PLAN_ID_SUB] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SUB_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ENRL_ELIG_ST] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MEM_MED_COB] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MEM_PHRM_COB] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DEDUCT_CAT] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MH_CD_BEN] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PHRM_BEN_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MH_CD_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MED_BEN_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HOSP_BEN_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BHI_CAT_FAC] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BHI_CAT_PRF] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PLN_CAT_FAC] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PLN_CAT_PRF] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FILE_IDENTIFIER] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
INSERT INTO ODS_BLK_Member (BHI_HOME_PLAN_ID, HOME_PLAN_PRODUCT_ID, MEM_ID,
CONS_MEM_ID, TRACEABILITY_FIELD, MEM_DOB, MEM_ZIP, MEM_COUNTRY, MEM_COUNTY,
MEM_GENDER, MEM_CONFIDENTIALITY_CDE, ACCOUNT, [GROUP], SUBGROUP,
COV_BEGIN_DT, COV_END_DT, MEM_RTI, HOME_PLAN_ID_SUB, SUB_ID, ENRL_ELIG_ST,
MEM_MED_COB, MEM_PHRM_COB, DEDUCT_CAT, MH_CD_BEN, PHRM_BEN_IND, MH_CD_IND,
MED_BEN_IND, HOSP_BEN_IND, BHI_CAT_FAC, BHI_CAT_PRF, PLN_CAT_FAC,
PLN_CAT_PRF, FILE_IDENTIFIER)
SELECT
'123','012001','1','1','ODS','19820706',
'19348','US','FIL','F','NON','1','1'
,'5','20060306','99991231','1','1','1','
A','B','B','1','B','Y','Y','Y','Y','
2001','2001','12','12','M'
UNION
SELECT
'123','012001','2','2','ODS','19861223',
'19348','US','FIL','M','NON','1','1'
,'5','20050101','20050912','1','1','2','
A','B','B','1','B','N','Y','Y','Y','
2001','2001','12','12','M'
UNION
SELECT
'123','012001','2','2','ODS','19861223',
'19348','US','FIL','M','NON','1','1'
,'5','20050310','20051120','1','1','2','
A','B','B','1','B','Y','Y','N','Y','
2001','2001','12','12','M'
UNION
SELECT
'123','012001','27','27','ODS','19471110
','79935','US','FIL','F','NON','3','
3','7','20050101','20050412','1','1','27
','A','B','B','1','B','N','Y','Y','Y
','2001','2001','12','12','M'
UNION
SELECT
'123','012001','27','27','ODS','19471110
','79935','US','FIL','F','NON','3','
3','7','20050201','20050813','1','1','27
','A','B','B','1','B','Y','Y','N','Y
','2001','2001','12','12','M'
UNION
SELECT
'123','012001','27','27','ODS','19471110
','79935','US','FIL','F','NON','3','
3','7','20050603','99991231','1','1','27
','A','B','B','1','B','N','Y','Y','Y
','2001','2001','12','12','M'
UNION
SELECT
'123','012001','4','4','ODS','19740314',
'19103','US','FIL','F','NON','2','2'
,'6','20060101','99991231','1','1','4','
A','B','B','1','B','N','Y','Y','Y','
2001','2001','12','12','M'
UNION
SELECT
'123','012001','4','4','ODS','19740314',
'19103','US','FIL','F','NON','2','2'
,'6','20060301','99991231','1','1','4','
A','B','B','1','B','Y','Y','N','Y','
2001','2001','12','12','M'
If Itzak is out there I would appreciate your magic.Forgot to mention this is a Medical and Pharmacy benefits table.
"FredG" wrote:

> Span example:
> --M--
> --Rx--
> Needs to b converted to this:
> --M--|--M & Rx--|--Rx--
> /* What current base data displays
> MEM_ID COV_BEGIN_DT COV_END_DT MED_BEN_IND PHRM_BEN_IND
> -- -- -- -- --
> 27 20050101 20050412 Y N
> 27 20050201 20050813 N Y
> 27 20050603 99991231 Y N
> */
>
> /* Should be converted as this?
> MEM_ID COV_BEGIN_DT COV_END_DT MED_BEN_IND PHRM_BEN_IND
> -- -- -- -- --
> 27 20050101 20050131 Y N
> 27 20050201 20050412 Y Y
> 27 20050413 20050602 N Y
> 27 20050603 20050813 Y Y
> 27 20050814 99991231 Y N
> */
> DDL to create the table and then some sample data
> CREATE TABLE [dbo].[ODS_BLK_Member] (
> [BHI_HOME_PLAN_ID] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [HOME_PLAN_PRODUCT_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [MEM_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CONS_MEM_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TRACEABILITY_FIELD] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [MEM_DOB] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_ZIP] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_COUNTRY] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_COUNTY] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_GENDER] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_CONFIDENTIALITY_CDE] [varchar] (3) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [ACCOUNT] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [GROUP] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SUBGROUP] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [COV_BEGIN_DT] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [COV_END_DT] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_RTI] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [HOME_PLAN_ID_SUB] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SUB_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ENRL_ELIG_ST] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_MED_COB] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_PHRM_COB] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [DEDUCT_CAT] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MH_CD_BEN] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PHRM_BEN_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MH_CD_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MED_BEN_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [HOSP_BEN_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [BHI_CAT_FAC] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [BHI_CAT_PRF] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PLN_CAT_FAC] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PLN_CAT_PRF] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [FILE_IDENTIFIER] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> INSERT INTO ODS_BLK_Member (BHI_HOME_PLAN_ID, HOME_PLAN_PRODUCT_ID, MEM_ID
,
> CONS_MEM_ID, TRACEABILITY_FIELD, MEM_DOB, MEM_ZIP, MEM_COUNTRY, MEM_COUNTY
,
> MEM_GENDER, MEM_CONFIDENTIALITY_CDE, ACCOUNT, [GROUP], SUBGROUP,
> COV_BEGIN_DT, COV_END_DT, MEM_RTI, HOME_PLAN_ID_SUB, SUB_ID, ENRL_ELIG_ST,
> MEM_MED_COB, MEM_PHRM_COB, DEDUCT_CAT, MH_CD_BEN, PHRM_BEN_IND, MH_CD_IND,
> MED_BEN_IND, HOSP_BEN_IND, BHI_CAT_FAC, BHI_CAT_PRF, PLN_CAT_FAC,
> PLN_CAT_PRF, FILE_IDENTIFIER)
> SELECT
> '123','012001','1','1','ODS','19820706',
'19348','US','FIL','F','NON','1','
1','5','20060306','99991231','1','1','1'
,'A','B','B','1','B','Y','Y','Y','Y'
,'2001','2001','12','12','M'
> UNION
> SELECT
> '123','012001','2','2','ODS','19861223',
'19348','US','FIL','M','NON','1','
1','5','20050101','20050912','1','1','2'
,'A','B','B','1','B','N','Y','Y','Y'
,'2001','2001','12','12','M'
> UNION
> SELECT
> '123','012001','2','2','ODS','19861223',
'19348','US','FIL','M','NON','1','
1','5','20050310','20051120','1','1','2'
,'A','B','B','1','B','Y','Y','N','Y'
,'2001','2001','12','12','M'
> UNION
> SELECT
> '123','012001','27','27','ODS','19471110
','79935','US','FIL','F','NON','3'
,'3','7','20050101','20050412','1','1','
27','A','B','B','1','B','N','Y','Y',
'Y','2001','2001','12','12','M'
> UNION
> SELECT
> '123','012001','27','27','ODS','19471110
','79935','US','FIL','F','NON','3'
,'3','7','20050201','20050813','1','1','
27','A','B','B','1','B','Y','Y','N',
'Y','2001','2001','12','12','M'
> UNION
> SELECT
> '123','012001','27','27','ODS','19471110
','79935','US','FIL','F','NON','3'
,'3','7','20050603','99991231','1','1','
27','A','B','B','1','B','N','Y','Y',
'Y','2001','2001','12','12','M'
> UNION
> SELECT
> '123','012001','4','4','ODS','19740314',
'19103','US','FIL','F','NON','2','
2','6','20060101','99991231','1','1','4'
,'A','B','B','1','B','N','Y','Y','Y'
,'2001','2001','12','12','M'
> UNION
> SELECT
> '123','012001','4','4','ODS','19740314',
'19103','US','FIL','F','NON','2','
2','6','20060301','99991231','1','1','4'
,'A','B','B','1','B','Y','Y','N','Y'
,'2001','2001','12','12','M'
> If Itzak is out there I would appreciate your magic.
>
>|||Fred,
This is an example for a task I'd probably end up using a cursor for. I
might be wrong, but experience with similar problems and intuition tells me
that set-based solutions using existing features in the product (both 2000
and 2005) are going to be more expensive than the cursor solution because of
excessive I/O. Using features from ANSI SQL:2003 that were not yet
implemented in SQL Server (particularly OVER clause with ORDER BY for
aggregations) there might be a set-based solution that would run faster than
the cursor. But that's for future versions of SQL Server... :-)
Anyhow, I'd declare a cursor based on the following query:
SELECT MEM_ID, COV_BEGIN_DT AS DT,
SUM(CASE WHEN MED_BEN_IND = 'Y' THEN +1 ELSE 0 END) AS MED_CHANGE,
SUM(CASE WHEN PHRM_BEN_IND = 'Y' THEN +1 ELSE 0 END) AS PHRM_CHANGE,
0 AS BEGIN_END
FROM ODS_BLK_Member
GROUP BY MEM_ID, COV_BEGIN_DT
UNION ALL
SELECT MEM_ID, COV_END_DT,
SUM(CASE WHEN MED_BEN_IND = 'Y' THEN -1 ELSE 0 END),
SUM(CASE WHEN PHRM_BEN_IND = 'Y' THEN -1 ELSE 0 END),
1
FROM ODS_BLK_Member
GROUP BY MEM_ID, COV_END_DT
ORDER BY MEM_ID, DT, BEGIN_END;
The idea is to produce a series of events in chronological order: events
that increase or decrease the count of benefit conditions.
Here's what the cursor's query produces:
MEM_ID DT MED_CHANGE PHRM_CHANGE BEGIN_END
-- -- -- -- --
1 20060306 1 1 0
1 99991231 -1 -1 1
2 20050101 1 0 0
2 20050310 0 1 0
2 20050912 -1 0 1
2 20051120 0 -1 1
27 20050101 1 0 0
27 20050201 0 1 0
27 20050412 -1 0 1
27 20050603 1 0 0
27 20050813 0 -1 1
27 99991231 -1 0 1
4 20060101 1 0 0
4 20060301 0 1 0
4 99991231 -1 -1 1
The cursor should scan the events in chronological order and produce a new
period whenever there's a change in the state of benefits.
I just scribbled the following code as an example of how such cursor code
might look like. But note that I didn't bother to test it thoroughly, and as
with most code snippets that contain more than 0 characters, this one most
probably has bugs. So please use this just to get the general idea of the
logic, but make sure you examine, understand, and test it thoroughly, and
make the required revisions before putting it into production.
SET NOCOUNT ON;
DECLARE
@.MEM_ID AS VARCHAR(22),
@.DT AS CHAR(8),
@.MED_CHANGE AS INT,
@.PHRM_CHANGE AS INT,
@.BEGIN_END AS INT,
@.COV_BEGIN_DT AS CHAR(8),
@.MED_CNT AS INT,
@.PHRM_CNT AS INT,
@.PRV_MEM_ID AS VARCHAR(22),
@.PRV_MED_CNT AS INT,
@.PRV_PHRM_CNT AS INT;
DECLARE @.Results TABLE
(
MEM_ID VARCHAR(22) NOT NULL,
COV_BEGIN_DT CHAR(8) NOT NULL,
COV_END_DT CHAR(8) NOT NULL,
MED_BEN_IND CHAR(1) NOT NULL,
PHRM_BEN_IND CHAR(1) NOT NULL
--PRIMARY KEY(MEM_ID, COV_BEGIN_DT)
);
DECLARE CEvents CURSOR FAST_FORWARD FOR
SELECT MEM_ID, COV_BEGIN_DT AS DT,
SUM(CASE WHEN MED_BEN_IND = 'Y' THEN +1 ELSE 0 END) AS MED_CHANGE,
SUM(CASE WHEN PHRM_BEN_IND = 'Y' THEN +1 ELSE 0 END) AS PHRM_CHANGE,
0 AS BEGIN_END
FROM ODS_BLK_Member
GROUP BY MEM_ID, COV_BEGIN_DT
UNION ALL
SELECT MEM_ID, COV_END_DT,
SUM(CASE WHEN MED_BEN_IND = 'Y' THEN -1 ELSE 0 END),
SUM(CASE WHEN PHRM_BEN_IND = 'Y' THEN -1 ELSE 0 END),
1
FROM ODS_BLK_Member
GROUP BY MEM_ID, COV_END_DT
ORDER BY MEM_ID, DT, BEGIN_END;
OPEN CEvents;
SELECT
@.PRV_MEM_ID = NULL,
@.PRV_MED_CNT = 0,
@.PRV_PHRM_CNT = 0;
FETCH NEXT FROM CEvents
INTO @.MEM_ID, @.DT, @.MED_CHANGE, @.PHRM_CHANGE, @.BEGIN_END;
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- New MEM_ID or new period; init variables
IF @.MEM_ID <> @.PRV_MEM_ID
OR @.PRV_MEM_ID IS NULL
OR @.PRV_MED_CNT + @.PRV_PHRM_CNT = 0
SELECT
@.MED_CNT = @.MED_CHANGE,
@.PHRM_CNT = @.PHRM_CHANGE,
@.COV_BEGIN_DT = @.DT;
ELSE
-- New period
BEGIN
SELECT
@.MED_CNT = @.MED_CNT + @.MED_CHANGE,
@.PHRM_CNT = @.PHRM_CNT + @.PHRM_CHANGE;
-- Change in MED or PHRM benefit state
-- means close of existing period and open of new one
IF SIGN(@.MED_CNT) <> SIGN(@.PRV_MED_CNT)
OR SIGN(@.PHRM_CNT) <> SIGN(@.PRV_PHRM_CNT)
BEGIN
INSERT INTO @.Results
(MEM_ID, COV_BEGIN_DT, COV_END_DT, MED_BEN_IND, PHRM_BEN_IND)
VALUES(
@.MEM_ID,
@.COV_BEGIN_DT,
CONVERT(VARCHAR(8), DATEADD(day, @.BEGIN_END-1, @.DT), 112),
CASE WHEN @.PRV_MED_CNT > 0 THEN 'Y' ELSE 'N' END,
CASE WHEN @.PRV_PHRM_CNT > 0 THEN 'Y' ELSE 'N' END);
IF @.MED_CNT + @.PHRM_CNT > 0
SET @.COV_BEGIN_DT =
CONVERT(VARCHAR(8), DATEADD(day, @.BEGIN_END, @.DT), 112);
END
END
SELECT
@.PRV_MEM_ID = @.MEM_ID,
@.PRV_MED_CNT = @.MED_CNT,
@.PRV_PHRM_CNT = @.PHRM_CNT
FETCH NEXT FROM CEvents
INTO @.MEM_ID, @.DT, @.MED_CHANGE, @.PHRM_CHANGE, @.BEGIN_END;
END
CLOSE CEvents;
DEALLOCATE CEvents;
SELECT * FROM @.Results;
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"FredG" <FredG@.discussions.microsoft.com> wrote in message
news:67C2CD4A-D517-418A-A11B-BE53773B831A@.microsoft.com...
> Span example:
> --M--
> --Rx--
> Needs to b converted to this:
> --M--|--M & Rx--|--Rx--
> /* What current base data displays
> MEM_ID COV_BEGIN_DT COV_END_DT MED_BEN_IND PHRM_BEN_IND
> -- -- -- -- --
> 27 20050101 20050412 Y N
> 27 20050201 20050813 N Y
> 27 20050603 99991231 Y N
> */
>
> /* Should be converted as this?
> MEM_ID COV_BEGIN_DT COV_END_DT MED_BEN_IND PHRM_BEN_IND
> -- -- -- -- --
> 27 20050101 20050131 Y N
> 27 20050201 20050412 Y Y
> 27 20050413 20050602 N Y
> 27 20050603 20050813 Y Y
> 27 20050814 99991231 Y N
> */
> DDL to create the table and then some sample data
> CREATE TABLE [dbo].[ODS_BLK_Member] (
> [BHI_HOME_PLAN_ID] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [HOME_PLAN_PRODUCT_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [MEM_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CONS_MEM_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TRACEABILITY_FIELD] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [MEM_DOB] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_ZIP] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_COUNTRY] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_COUNTY] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_GENDER] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_CONFIDENTIALITY_CDE] [varchar] (3) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [ACCOUNT] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [GROUP] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SUBGROUP] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [COV_BEGIN_DT] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [COV_END_DT] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_RTI] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [HOME_PLAN_ID_SUB] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [SUB_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ENRL_ELIG_ST] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_MED_COB] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_PHRM_COB] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [DEDUCT_CAT] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MH_CD_BEN] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PHRM_BEN_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MH_CD_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MED_BEN_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [HOSP_BEN_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [BHI_CAT_FAC] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [BHI_CAT_PRF] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PLN_CAT_FAC] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PLN_CAT_PRF] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [FILE_IDENTIFIER] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> INSERT INTO ODS_BLK_Member (BHI_HOME_PLAN_ID, HOME_PLAN_PRODUCT_ID,
> MEM_ID,
> CONS_MEM_ID, TRACEABILITY_FIELD, MEM_DOB, MEM_ZIP, MEM_COUNTRY,
> MEM_COUNTY,
> MEM_GENDER, MEM_CONFIDENTIALITY_CDE, ACCOUNT, [GROUP], SUBGROUP,
> COV_BEGIN_DT, COV_END_DT, MEM_RTI, HOME_PLAN_ID_SUB, SUB_ID, ENRL_ELIG_ST,
> MEM_MED_COB, MEM_PHRM_COB, DEDUCT_CAT, MH_CD_BEN, PHRM_BEN_IND, MH_CD_IND,
> MED_BEN_IND, HOSP_BEN_IND, BHI_CAT_FAC, BHI_CAT_PRF, PLN_CAT_FAC,
> PLN_CAT_PRF, FILE_IDENTIFIER)
> SELECT
> '123','012001','1','1','ODS','19820706',
'19348','US','FIL','F','NON','1','
1','5','20060306','99991231','1','1','1'
,'A','B','B','1','B','Y','Y','Y','Y'
,'2001','2001','12','12','M'
> UNION
> SELECT
> '123','012001','2','2','ODS','19861223',
'19348','US','FIL','M','NON','1','
1','5','20050101','20050912','1','1','2'
,'A','B','B','1','B','N','Y','Y','Y'
,'2001','2001','12','12','M'
> UNION
> SELECT
> '123','012001','2','2','ODS','19861223',
'19348','US','FIL','M','NON','1','
1','5','20050310','20051120','1','1','2'
,'A','B','B','1','B','Y','Y','N','Y'
,'2001','2001','12','12','M'
> UNION
> SELECT
> '123','012001','27','27','ODS','19471110
','79935','US','FIL','F','NON','3'
,'3','7','20050101','20050412','1','1','
27','A','B','B','1','B','N','Y','Y',
'Y','2001','2001','12','12','M'
> UNION
> SELECT
> '123','012001','27','27','ODS','19471110
','79935','US','FIL','F','NON','3'
,'3','7','20050201','20050813','1','1','
27','A','B','B','1','B','Y','Y','N',
'Y','2001','2001','12','12','M'
> UNION
> SELECT
> '123','012001','27','27','ODS','19471110
','79935','US','FIL','F','NON','3'
,'3','7','20050603','99991231','1','1','
27','A','B','B','1','B','N','Y','Y',
'Y','2001','2001','12','12','M'
> UNION
> SELECT
> '123','012001','4','4','ODS','19740314',
'19103','US','FIL','F','NON','2','
2','6','20060101','99991231','1','1','4'
,'A','B','B','1','B','N','Y','Y','Y'
,'2001','2001','12','12','M'
> UNION
> SELECT
> '123','012001','4','4','ODS','19740314',
'19103','US','FIL','F','NON','2','
2','6','20060301','99991231','1','1','4'
,'A','B','B','1','B','Y','Y','N','Y'
,'2001','2001','12','12','M'
> If Itzak is out there I would appreciate your magic.
>
>|||Hi Itzik, First, my applogizes for butchering your first name, I feel ashame
d.
Secondly, thank you very much for taking the time to respond.
When I first read that you have steered away from set based solution to
cursor based I was a suprised. All of your writings clearly state to use
cursors as a last resort. In any event, you are the MASTER of T-SQL and I
will adher to your suggestions.
The table in question contains 5 million rows, using a cursor may just take
a very long time. However, I will give it a go at it.
If you see Kalen Delaney and Andrew Kelly tell them I said hello.
Alfredo Giotti
"Itzik Ben-Gan" wrote:

> Fred,
> This is an example for a task I'd probably end up using a cursor for. I
> might be wrong, but experience with similar problems and intuition tells m
e
> that set-based solutions using existing features in the product (both 2000
> and 2005) are going to be more expensive than the cursor solution because
of
> excessive I/O. Using features from ANSI SQL:2003 that were not yet
> implemented in SQL Server (particularly OVER clause with ORDER BY for
> aggregations) there might be a set-based solution that would run faster th
an
> the cursor. But that's for future versions of SQL Server... :-)
> Anyhow, I'd declare a cursor based on the following query:
> SELECT MEM_ID, COV_BEGIN_DT AS DT,
> SUM(CASE WHEN MED_BEN_IND = 'Y' THEN +1 ELSE 0 END) AS MED_CHANGE,
> SUM(CASE WHEN PHRM_BEN_IND = 'Y' THEN +1 ELSE 0 END) AS PHRM_CHANGE,
> 0 AS BEGIN_END
> FROM ODS_BLK_Member
> GROUP BY MEM_ID, COV_BEGIN_DT
> UNION ALL
> SELECT MEM_ID, COV_END_DT,
> SUM(CASE WHEN MED_BEN_IND = 'Y' THEN -1 ELSE 0 END),
> SUM(CASE WHEN PHRM_BEN_IND = 'Y' THEN -1 ELSE 0 END),
> 1
> FROM ODS_BLK_Member
> GROUP BY MEM_ID, COV_END_DT
> ORDER BY MEM_ID, DT, BEGIN_END;
> The idea is to produce a series of events in chronological order: events
> that increase or decrease the count of benefit conditions.
> Here's what the cursor's query produces:
> MEM_ID DT MED_CHANGE PHRM_CHANGE BEGIN_END
> -- -- -- -- --
> 1 20060306 1 1 0
> 1 99991231 -1 -1 1
> 2 20050101 1 0 0
> 2 20050310 0 1 0
> 2 20050912 -1 0 1
> 2 20051120 0 -1 1
> 27 20050101 1 0 0
> 27 20050201 0 1 0
> 27 20050412 -1 0 1
> 27 20050603 1 0 0
> 27 20050813 0 -1 1
> 27 99991231 -1 0 1
> 4 20060101 1 0 0
> 4 20060301 0 1 0
> 4 99991231 -1 -1 1
> The cursor should scan the events in chronological order and produce a new
> period whenever there's a change in the state of benefits.
> I just scribbled the following code as an example of how such cursor code
> might look like. But note that I didn't bother to test it thoroughly, and
as
> with most code snippets that contain more than 0 characters, this one most
> probably has bugs. So please use this just to get the general idea of the
> logic, but make sure you examine, understand, and test it thoroughly, and
> make the required revisions before putting it into production.
> SET NOCOUNT ON;
> DECLARE
> @.MEM_ID AS VARCHAR(22),
> @.DT AS CHAR(8),
> @.MED_CHANGE AS INT,
> @.PHRM_CHANGE AS INT,
> @.BEGIN_END AS INT,
> @.COV_BEGIN_DT AS CHAR(8),
> @.MED_CNT AS INT,
> @.PHRM_CNT AS INT,
> @.PRV_MEM_ID AS VARCHAR(22),
> @.PRV_MED_CNT AS INT,
> @.PRV_PHRM_CNT AS INT;
> DECLARE @.Results TABLE
> (
> MEM_ID VARCHAR(22) NOT NULL,
> COV_BEGIN_DT CHAR(8) NOT NULL,
> COV_END_DT CHAR(8) NOT NULL,
> MED_BEN_IND CHAR(1) NOT NULL,
> PHRM_BEN_IND CHAR(1) NOT NULL
> --PRIMARY KEY(MEM_ID, COV_BEGIN_DT)
> );
> DECLARE CEvents CURSOR FAST_FORWARD FOR
> SELECT MEM_ID, COV_BEGIN_DT AS DT,
> SUM(CASE WHEN MED_BEN_IND = 'Y' THEN +1 ELSE 0 END) AS MED_CHANGE,
> SUM(CASE WHEN PHRM_BEN_IND = 'Y' THEN +1 ELSE 0 END) AS PHRM_CHANGE,
> 0 AS BEGIN_END
> FROM ODS_BLK_Member
> GROUP BY MEM_ID, COV_BEGIN_DT
> UNION ALL
> SELECT MEM_ID, COV_END_DT,
> SUM(CASE WHEN MED_BEN_IND = 'Y' THEN -1 ELSE 0 END),
> SUM(CASE WHEN PHRM_BEN_IND = 'Y' THEN -1 ELSE 0 END),
> 1
> FROM ODS_BLK_Member
> GROUP BY MEM_ID, COV_END_DT
> ORDER BY MEM_ID, DT, BEGIN_END;
> OPEN CEvents;
> SELECT
> @.PRV_MEM_ID = NULL,
> @.PRV_MED_CNT = 0,
> @.PRV_PHRM_CNT = 0;
> FETCH NEXT FROM CEvents
> INTO @.MEM_ID, @.DT, @.MED_CHANGE, @.PHRM_CHANGE, @.BEGIN_END;
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> -- New MEM_ID or new period; init variables
> IF @.MEM_ID <> @.PRV_MEM_ID
> OR @.PRV_MEM_ID IS NULL
> OR @.PRV_MED_CNT + @.PRV_PHRM_CNT = 0
> SELECT
> @.MED_CNT = @.MED_CHANGE,
> @.PHRM_CNT = @.PHRM_CHANGE,
> @.COV_BEGIN_DT = @.DT;
> ELSE
> -- New period
> BEGIN
> SELECT
> @.MED_CNT = @.MED_CNT + @.MED_CHANGE,
> @.PHRM_CNT = @.PHRM_CNT + @.PHRM_CHANGE;
> -- Change in MED or PHRM benefit state
> -- means close of existing period and open of new one
> IF SIGN(@.MED_CNT) <> SIGN(@.PRV_MED_CNT)
> OR SIGN(@.PHRM_CNT) <> SIGN(@.PRV_PHRM_CNT)
> BEGIN
> INSERT INTO @.Results
> (MEM_ID, COV_BEGIN_DT, COV_END_DT, MED_BEN_IND, PHRM_BEN_IND)
> VALUES(
> @.MEM_ID,
> @.COV_BEGIN_DT,
> CONVERT(VARCHAR(8), DATEADD(day, @.BEGIN_END-1, @.DT), 112),
> CASE WHEN @.PRV_MED_CNT > 0 THEN 'Y' ELSE 'N' END,
> CASE WHEN @.PRV_PHRM_CNT > 0 THEN 'Y' ELSE 'N' END);
> IF @.MED_CNT + @.PHRM_CNT > 0
> SET @.COV_BEGIN_DT =
> CONVERT(VARCHAR(8), DATEADD(day, @.BEGIN_END, @.DT), 112);
> END
> END
> SELECT
> @.PRV_MEM_ID = @.MEM_ID,
> @.PRV_MED_CNT = @.MED_CNT,
> @.PRV_PHRM_CNT = @.PHRM_CNT
> FETCH NEXT FROM CEvents
> INTO @.MEM_ID, @.DT, @.MED_CHANGE, @.PHRM_CHANGE, @.BEGIN_END;
> END
> CLOSE CEvents;
> DEALLOCATE CEvents;
> SELECT * FROM @.Results;
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> www.insidetsql.com
> Anything written in this message represents my view, my own view, and
> nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
>
> "FredG" <FredG@.discussions.microsoft.com> wrote in message
> news:67C2CD4A-D517-418A-A11B-BE53773B831A@.microsoft.com...
>
>|||Hi Fred,
No need to apologize. :-)
It's true that for the most part, set-based solutions are faster than
cursor-based ones. There's a lot of overhead involved with the
record-by-record manipulation of the cursor. However, there are types of
problems where using cursors, your code ends up incurring much less I/O than
the set-solution. Remember that a cursor can rely on sorted data while set
manipulation cannot.
Take running aggregates as an example; the set-based solutions have an
O(N^2) complexity since portions of the data need to be rescanned in order
to calculate the running aggregates. The cursor on the other hand performs a
single scan of the data.
In my previous reply I mentioned the ANSI OVER clause (with an ORDER BY
option). It is really brilliant, and I wonder if the designers of the
feature themselves knew how profound it is. I believe this option to be the
bridge between cursors and sets; sort of the holy grail of SQL. :-)
You can perform ordered calculations without forcing any particular order of
the output, and without involving the cursor overhead. The technology
already exists in the SQL Server 2005 engine; it's just that OVER + ORDER BY
with aggregates was not implemented yet, and this combination is where the
real power lies.
Anyhow, today, set-based solutions to some problems (including some
solutions to temporal problems) have complexities that end up being much
more expensive than cursor solutions.
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"FredG" <FredG@.discussions.microsoft.com> wrote in message
news:9228A601-9A06-43B8-8AA1-7CE4AA5B91C1@.microsoft.com...
> Hi Itzik, First, my applogizes for butchering your first name, I feel
> ashamed.
> Secondly, thank you very much for taking the time to respond.
> When I first read that you have steered away from set based solution to
> cursor based I was a suprised. All of your writings clearly state to use
> cursors as a last resort. In any event, you are the MASTER of T-SQL and I
> will adher to your suggestions.
> The table in question contains 5 million rows, using a cursor may just
> take
> a very long time. However, I will give it a go at it.
> If you see Kalen Delaney and Andrew Kelly tell them I said hello.
> Alfredo Giotti
> "Itzik Ben-Gan" wrote:
>|||"Itzik Ben-Gan" writes
>.
>In my previous reply I mentioned the ANSI OVER clause (with an ORDER BY
>option). It is really brilliant, and I wonder if the designers of the
>feature themselves knew how profound it is. I believe this option to be the
>bridge between cursors and sets; sort of the holy grail of SQL. :-)
To quote Bob Dylan:
'I would not feel so alone if everyone where getting stoned':)
Yes I agree with you in principal.The 'real' paradign shift has
little to do with the clr and everything to do with exploding
the perverted myth of the exclusivity of'set based' constructs.
The idea one can legitimately think in terms of rows without being
labelled an sql Jodus has arrived.But calling this windowing a
'profound' kind of insight and bestowing on the designers the aura
of 'brilliance' would be a mistake.It is at best an example of
'better late than never'.Calling this state of affairs profound
would surely overshadow the accountability that the commericial
database world should be held to.The fact that this mindset change
has taken almost 30 years should be seen as appalling.Neo-cons of
the industry had hijacked sense with sql creationism and marketing.
WMD was replaced with client/server and a tiered approach.A theory
was misapplied to a retrival mechanism and unapplied to a design
mechanism.An approach that vendors marketted that allowed them to
hide both their intellectual and creative shortcomings.Their db
failures made for the 'client'.And now the clr in the db has replaced
the client.And of course the dreaded cursor.This demanded regime
change and the field was bankrupted for 30 years.For this we are to
praise Ceasar?I think not.
It is interesting to look at the fanfare that vendors are using
to usher in this new paradign.In their documentation Oracle refers
to their analytic functions in windows as an example of
'data densification'.This phrase is supposed to illustrate the
flip side of the Group By.It was obviously borrowed from the idea
of pacification,right out of the Pentagon.This is the best they could
come up with?Any army of engineers berefit of language and concepts.
Not to be out done,MS in its highly touted BOL offers the next best
thing - absolutely Nothing!No explanations,no history no seqways.
The functions are thrown around like so much spaghetti on a wall.
If you write about concepts someone may quote you.MS needn't worry
now.Least I be accused of favortism,IBM was too busy pleasing its
shareholders to write anything intelligible.
Finally,to your point about MS leaving out a large chunk of analytic
material this was obviously not an oversight but just insurance
that anything done with sql-99 could most definitly be easily ported
to the competition.Less is more.Please!If they weren't sure of
what they were doing they could have at least looked at Oracle
which is probably about 8 years ahead.Or even looked at RAC to see what
you and I are really talking about :)
Interested readers maybe surprised that many of the ideas in sql
analytics can be found in the SAS (Statistical Analysis System) Data
Step...introduced about 20 years ago!Many of the Oracle extensions
(First/Last) can also be found here.MySql allows mixing of variables
and columns in a SELECT.Most of the analytics can be easily simulated
in a single SELECT.And of course little RAC, way ahead of its time:)
Some musing from:
www.rac4sql.net|||Here's another method, you'll need SQL Server 2005
and a calendar table as per
http://www.aspfaq.com/show.asp?id=2519
Ensure the calendar covers all dates in your data.
(Do not put '99991231' into the calendar!)
I don't know how the performance of this compares
to the cursor based solution already posted.
WITH Daily(MEM_ID,dt,MED_PHRM)
AS
(SELECT m.MEM_ID,
t.dt,
MAX(CASE m.MED_BEN_IND WHEN 'Y' THEN 1 ELSE 0 END) +
MAX(CASE m.PHRM_BEN_IND WHEN 'Y' THEN 2 ELSE 0 END)
FROM Calendar t
INNER JOIN ODS_BLK_Member m ON CAST(t.dt AS DATETIME) BETWEEN
CAST(m.COV_BEGIN_DT AS DATETIME)
AND
CAST(m.COV_END_DT AS DATETIME)
GROUP BY t.dt,m.MEM_ID),
RankedDaily(MEM_ID,dt,MED_PHRM,RankDiff)
AS
(SELECT MEM_ID,
dt,
MED_PHRM,
DATEADD(day,-RANK() OVER (PARTITION BY MEM_ID,MED_PHRM ORDER BY
dt),dt)
FROM Daily)
SELECT MEM_ID,
CONVERT(CHAR(8),MIN(dt),112) AS COV_BEGIN_DT,
CASE WHEN MAX(dt) = (SELECT MAX(dt) FROM Calendar)
THEN '99991231'
ELSE CONVERT(CHAR(8),MAX(dt),112)
END AS COV_END_DT,
CASE WHEN MED_PHRM % 2 <> 0 THEN 'Y' ELSE 'N' END AS
MED_BEN_IND,
CASE WHEN MED_PHRM / 2 <> 0 THEN 'Y' ELSE 'N' END AS
PHRM_BEN_IND
FROM RankedDaily
GROUP BY MEM_ID,MED_PHRM,RankDiff
ORDER BY 1,2;
You can create a simplified calendar using this
CREATE TABLE dbo.Calendar
(
dt SMALLDATETIME NOT NULL
PRIMARY KEY CLUSTERED
)
DECLARE @.dt SMALLDATETIME
SET @.dt = '20040101'
WHILE @.dt < '20080101'
BEGIN
INSERT dbo.Calendar(dt) SELECT @.dt
SET @.dt = @.dt + 1
END|||"Truly, you have A dizzying intellect." 8-)
Doesn't change the way I feel about OVER + ORDER BY, though.
Cheers,
--
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"Steve Dassin" <rac4sqlnospam@.net> wrote in message
news:u8%23pGNSXGHA.3936@.TK2MSFTNGP05.phx.gbl...
> "Itzik Ben-Gan" writes
> To quote Bob Dylan:
> 'I would not feel so alone if everyone where getting stoned':)
> Yes I agree with you in principal.The 'real' paradign shift has
> little to do with the clr and everything to do with exploding
> the perverted myth of the exclusivity of'set based' constructs.
> The idea one can legitimately think in terms of rows without being
> labelled an sql Jodus has arrived.But calling this windowing a
> 'profound' kind of insight and bestowing on the designers the aura
> of 'brilliance' would be a mistake.It is at best an example of
> 'better late than never'.Calling this state of affairs profound
> would surely overshadow the accountability that the commericial
> database world should be held to.The fact that this mindset change
> has taken almost 30 years should be seen as appalling.Neo-cons of
> the industry had hijacked sense with sql creationism and marketing.
> WMD was replaced with client/server and a tiered approach.A theory
> was misapplied to a retrival mechanism and unapplied to a design
> mechanism.An approach that vendors marketted that allowed them to
> hide both their intellectual and creative shortcomings.Their db
> failures made for the 'client'.And now the clr in the db has replaced
> the client.And of course the dreaded cursor.This demanded regime
> change and the field was bankrupted for 30 years.For this we are to
> praise Ceasar?I think not.
> It is interesting to look at the fanfare that vendors are using
> to usher in this new paradign.In their documentation Oracle refers
> to their analytic functions in windows as an example of
> 'data densification'.This phrase is supposed to illustrate the
> flip side of the Group By.It was obviously borrowed from the idea
> of pacification,right out of the Pentagon.This is the best they could
> come up with?Any army of engineers berefit of language and concepts.
> Not to be out done,MS in its highly touted BOL offers the next best
> thing - absolutely Nothing!No explanations,no history no seqways.
> The functions are thrown around like so much spaghetti on a wall.
> If you write about concepts someone may quote you.MS needn't worry
> now.Least I be accused of favortism,IBM was too busy pleasing its
> shareholders to write anything intelligible.
> Finally,to your point about MS leaving out a large chunk of analytic
> material this was obviously not an oversight but just insurance
> that anything done with sql-99 could most definitly be easily ported
> to the competition.Less is more.Please!If they weren't sure of
> what they were doing they could have at least looked at Oracle
> which is probably about 8 years ahead.Or even looked at RAC to see what
> you and I are really talking about :)
> Interested readers maybe surprised that many of the ideas in sql
> analytics can be found in the SAS (Statistical Analysis System) Data
> Step...introduced about 20 years ago!Many of the Oracle extensions
> (First/Last) can also be found here.MySql allows mixing of variables
> and columns in a SELECT.Most of the analytics can be easily simulated
> in a single SELECT.And of course little RAC, way ahead of its time:)
> Some musing from:
> www.rac4sql.net
>
>|||We really are of the same mind! :)
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:uhToaBfXGHA.1476@.TK2MSFTNGP03.phx.gbl...
> "Truly, you have A dizzying intellect." 8-)
> Doesn't change the way I feel about OVER + ORDER BY, though.
> Cheers,
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> www.insidetsql.com
> Anything written in this message represents my view, my own view, and
> nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
>
> "Steve Dassin" <rac4sqlnospam@.net> wrote in message
> news:u8%23pGNSXGHA.3936@.TK2MSFTNGP05.phx.gbl...
>

Member.FetchAllProperties Error

I get

"System.NotSupportedException: The method specified is not supported by the

current provider."

AdomdClient v8, VS 2003, SQL Server 2000

What's the cause of this and is there any way around it?

hello,

it looks like the error might be caused by an older version of msolap80 provider. I'd suggest to check on it, and if indeed - update.

hope this helps,

|||Hi Mary, I have msolap80 version 8.00.760. I can't find anything more recent than that.|||

hello Kevin,

i think 760 is not the latest one... did you install sp4?

i think you can try the PTS from here: http://www.microsoft.com/downloads/details.aspx?familyid=DF0BA5AA-B4BD-4705-AA0A-B477BA72A9CB&displaylang=en
(search for "Microsoft SQL Server 2000 PivotTable Services" section)

or here is the link to sql server sp4: http://www.microsoft.com/downloads/details.aspx?familyid=8E2DFC8D-C20E-4446-99A9-B7F0213F8BC5&displaylang=en

hope this helps,

|||Hi Mary,

Thanks. I applied SP4 and that did the trick. However, what I now find is that after calling FetchAllProperties I still get no member properties. Is there something that needs to be called or configured even earlier? Here's a code snippet.

For Each position As AdomdClient.Position In positions

Dim member As AdomdClient.Member = position.Members(0)

member.FetchAllProperties()

Dim properties As MemberPropertyCollection = member.MemberProperties

Debug.WriteLine(String.Format("No. of member properties = {0}", properties.Count))

For Each p As AdomdClient.MemberProperty In properties
Debug.WriteLine(String.Format("Property = {0}, {1}", p.Name, p.Value))
Next

Next|||

hello Kevin,

member.FetchAllProperties() call populates the member.Properties collection. and the member.MemberProperties collection should contain the properties explicitly requested in the mdx query (in “DIMENSION PROPERTIES” clause).

depending on whether you need those properties for all/many members or not, you can either request them in the query (and access them with .MemberProperties collection), or request properties for a specific(few) members afterwards by calling .FetchAllProperties() and accessing properties from the .Properties collection.

hope this helps,

|||Hi Mary. Thanks for this. It worked. We used the second technique because we need to be generic, i.e., we don't know in advance what user-defined properties will be available. But here's another question. Calling Member.Properties gives e.g.,

Property Name: MEMBER_KEY, Property Value: 13
Property Name: IS_PLACEHOLDERMEMBER, Property Value: False
Property Name: IS_DATAMEMBER, Property Value: False
...
Property Name: Allow Input, Property Value: 0

The last one here is one of our user-defined properties. The previous ones are the intrinsic properties. Is there a way of telling the difference? At the moment I've noticed that the intrinsic names are all upper case, while our UDPs are mixed case. As a first stab I'm just testing for all upper case and excluding. But of course this is not the most rigorous of methods!

Monday, March 19, 2012

MDX: Rolling YTD

Hi,
I have been searching for a solution in MDX to create rolling
Year-to-Date calculated members.
For example:
When the current member time is June 2006, I want the total sales YTD
of last year, that
means from January 2005 till June 2005.
Thanks for any hints for a solution
RS.
ps.
My starting point was:
sum({ytd()},[Measures].[Sales])
But this formula is the sum YTD for this year. I'm searching for a
formula for the sum YTD of last year.
I've tried every combination with sum, OpeningPeriod(), lag,
ParallelPeriod().RS wrote:
> Hi,
> I have been searching for a solution in MDX to create rolling
> Year-to-Date calculated members.
>
> For example:
> When the current member time is June 2006, I want the total sales YTD
> of last year, that
> means from January 2005 till June 2005.
>
> Thanks for any hints for a solution
>
> RS.
>
> ps.
> My starting point was:
>
> sum({ytd()},[Measures].[Sales])
>
> But this formula is the sum YTD for this year. I'm searching for a
> formula for the sum YTD of last year.
>
> I've tried every combination with sum, OpeningPeriod(), lag,
> ParallelPeriod().

> I datagrep is offer the solution for most of the problems
> so visit this for ur solutions
>http://www.datagrep.com

Friday, March 9, 2012

MDX question

Hi,

Here is my question.

For the last year special store sales amount, we only calculate sales amount based on special store lists in current selected date.

If a store in current date is not a special store then we do not include this store for last year sales amount (even if this store was special store in last year). This means we don't care about last year store status and we only identify the special store status of all stores according to the date we selected.

In the fact, I join the flag ( which represents a special store) and added one colum for special sales calcualtion. (flag * net sales). In the cube, Currnet speical sales are correct but I couldn't get correct last year specail sales amount.

Below is the calculation for last year specail store sales amount but the numbers do not match.

IIF(([Measures].[flag], [Date].[Fiscal Hierarchy].CurrentMember)=False,0,

(parallelperiod([Date].[Fiscal Hierarchy].[fiscal year],1),[Measures].[Special Sales]))

Please give me some comments.

Thanks in advance.

Boolean values as measures just don't work well. There probably would be a way to write the MDX calculation, but the performance would be really bad probably to the point of being unusable.

You could implement the flag as it's own dimension, but probably the best approach is to implement the store dimension as a slowly changing dimension. What you would be after is a type 2 changing dimension. If you are not aware of what this is, you are basically adding a new record to the store dimension when the value of the flag changes

Below is a simplified example, notice that "Store 1" has StoreID = 1 and StoreID = 3. It is often common to store effective dates against the records for slowly changing dimensions to make it easier to figure out which dimension record applies to a given fact record.

StoreID StoreName SpecialFlag

1 Store 1 False

2 Store 2 False

3 Store 1 True

Then when you insert facts you insert the StoreID that was effective for the date of the respective facts.

StoreID DateID SalesAmount

1 20060101 100

3 20070601 100

Using this technique you would be able to create a "Special Store" attribute in your store dimension and filter using that.

|||

Thanks for the reply. I think I didn't give you a right explanation.

I understand what you suggested but could you take a look at this one more time?

Below is the details how it looks like :

Selected date : 20070101

Last year date : 20060101

Sales Fact

ID Store_key Date Sales

1 store 1 20070101 100.00

2 store 2 20070101 150.00

3 store 3 20070101 100.00

4 store 1 20060101 30.00

5 store 2 20060101 30.00

6 store 3 20060101 30.00

There is an anther fact table which has a flag.

Special_Store Fact

Special_Store_key Store_key Date Key SS_flag

1 store 1 20070101 True

2 store 1 20060101 False

3 store 2 20070101 False

4 store 2 20060101 True

5 store 3 20070101 False

6 store 3 20060101 True

Now I joined this 2 Fact to add Specail Store Sales into the Sales Fact

ID Store_key Date Sales SS_flag SS_Sales (SS flag * Sales)

1 store 1 20070101 100.00 True 100.00

2 store 2 20070101 150.00 False 0.00

3 store 3 20070101 100.00 True 100.00

4 store 1 20060101 30.00 False 0.00

5 store 2 20060101 30.00 True 30.00

6 store 3 20060101 30.00 True 30.00

From this data,

we calulate SS sales amount and the total is 130.00 which is not a problem to calculate in the cube.

My problem is that for the last year SS sales amount with time calculation we developed inside the cube,

time calculation sums up all SS_Sales, so last year total is 60.00.

But from the selected date , we do not consider last year store status. We only care current Special Store and From this list we calculate last year value. So, in this data, we need to get 30 (excluding store 2 since store 2 is not special store in the selected date 20070101).

As you mentioned, if boolean value does not work properly and affects performance then how can I approach in this situation? Any Suggestions?

Please let me know.

Thanks.

|||

I am pretty sure that my original suggestion of not adding this flag to the fact table and implementing a slowly changing dimension would work. But possibly a simpler solution might be to insert NULL insead of 0.00 into the SS_Sales measure and then do something like the following, which basically checks if there is a value for [Special Sales] in the current period.

IIF(IsEmpty({([Measures].[Special Sales], [Date].[Fiscal Hierarchy].CurrentMember)}),NULL,

(parallelperiod([Date].[Fiscal Hierarchy].[fiscal year],1),[Measures].[Special Sales]))

The only problem with this is that, if you have a hierarchy over the stores so that they roll up into groups of some sort then you will get incorrect results at the higher levels. If any store in the group was "sepcial" last year the value for the entire group would be included

Which would mean that you might have to do something like the following to force this expression to be evaluated over individual store members.

SUM( EXISTING [Store].[Store].[Store].Members ,

IIF(IsEmpty({([Measures].[Special Sales], [Date].[Fiscal Hierarchy].CurrentMember)}),NULL,

(parallelperiod([Date].[Fiscal Hierarchy].[fiscal year],1),[Measures].[Special Sales]))

)|||

There seems to be some inconsistency in the sample data: Store 3, 20070101 is shown as a special sale in the joined fact table, but not in Special_Store_Fact - I assume that is just a typo?

Anyway, since SS_Flag is already a field in the joined fact table, another approach would to create a simple true/false dimension like [Special Flag]. Then the MDX expressions could be:

For current year [Special Sales]:

([Special Flag].[SL_Flag].[True], [Measures].[Sales])

And for previous year [Special Sales]:

Sum(NonEmpty([Store].[Store].[Store].Members,

([Special Flag].[SL_Flag].[True], [Measures].[Sales])),

(ParallelPeriod([Date].[Fiscal Hierarchy].[Fiscal Year], 1),

[Measures].[Sales]))

|||

That's probably a reasonable compromise and would be relatively easy to implement.

The only comment I would make is that modelling changing attributes in this matter should be the exception, not the rule. You would not want to take this to the extreme and end up with a cube that has lots of small dimensions as it increases the size of the aggregations and indexes which will reduce performance.

|||

Hi,

It's been a quite long time since you posted this.

Now, we have some changes regarding this special store's calculation.

Previously, we only select the current special store list, and for the last year value, we use current lists of special stores and if any store which was not special store in parallel period then we excluded it from the calculation.

I've tried this MDX into the cube and current year value works perfectly, but last year value doesn't work properly.

MDX for the last year :

Sum(NonEmpty([Store].[Store Hierarchy].currentmember,

([Special Flag].[SL_Flag].[True], [Measures].[Sales])),

(ParallelPeriod([Date].[Fiscal Hierarchy].[Fiscal Year], 1),

[Measures].[Sales]))

> This mdx calculate all the last year value that store became a special in last year. it can't get the current special store lists.

But now, whether store was not a spcial store in last year, if store became a special store in selected date ( current) then we sum up all store's sale amount into the last year value.

below is the MDX for the last year:

([Special Flag].[SL_Flag].[True],

(ParallelPeriod([Date].[Fiscal Hierarchy].[Fiscal Year], 1),

[Measures].[Sales]))

But when i browse the cube, it seems last year special flag is used for last year calculation. So, I can see all special store sales amount in last year. (not exclude store which is not a special store in current year)

I've tried to do different ways but still couldn't get any solutions yet.

It seems the MDX is something wrong.

If I want to use current special flag for the parallelperiod calculation how should I write MDX for that?

I think this logic makes sense to me but I don't know why it doesn't work.

I would appreciate if anybody can give me some comments.

Thanks.

MDX question

Hi,

Here is my question.

For the last year special store sales amount, we only calculate sales amount based on special store lists in current selected date.

Below is the calculation for last year specail store sales amount but the numbers do not match.

IIF(([Measures].[flag], [Date].[Fiscal Hierarchy].CurrentMember)=False,0,

(parallelperiod([Date].[Fiscal Hierarchy].[fiscal year],1),[Measures].[Special Sales]))

Please give me some comments.

Thanks in advance.

Boolean values as measures just don't work well. There probably would be a way to write the MDX calculation, but the performance would be really bad probably to the point of being unusable.

StoreID StoreName SpecialFlag

1 Store 1 False

2 Store 2 False

3 Store 1 True

Then when you insert facts you insert the StoreID that was effective for the date of the respective facts.

StoreID DateID SalesAmount

1 20060101 100

3 20070601 100

Using this technique you would be able to create a "Special Store" attribute in your store dimension and filter using that.

|||

Thanks for the reply. I think I didn't give you a right explanation.

I understand what you suggested but could you take a look at this one more time?

Below is the details how it looks like :

Selected date : 20070101

Last year date : 20060101

Sales Fact

ID Store_key Date Sales

1 store 1 20070101 100.00

2 store 2 20070101 150.00

3 store 3 20070101 100.00

4 store 1 20060101 30.00

5 store 2 20060101 30.00

6 store 3 20060101 30.00

There is an anther fact table which has a flag.

Special_Store Fact

Special_Store_key Store_key Date Key SS_flag

1 store 1 20070101 True

2 store 1 20060101 False

3 store 2 20070101 False

4 store 2 20060101 True

5 store 3 20070101 False

6 store 3 20060101 True

Now I joined this 2 Fact to add Specail Store Sales into the Sales Fact

ID Store_key Date Sales SS_flag SS_Sales (SS flag * Sales)

1 store 1 20070101 100.00 True 100.00

2 store 2 20070101 150.00 False 0.00

3 store 3 20070101 100.00 True 100.00

4 store 1 20060101 30.00 False 0.00

5 store 2 20060101 30.00 True 30.00

6 store 3 20060101 30.00 True 30.00

From this data,

we calulate SS sales amount and the total is 130.00 which is not a problem to calculate in the cube.

My problem is that for the last year SS sales amount with time calculation we developed inside the cube,

time calculation sums up all SS_Sales, so last year total is 60.00.

As you mentioned, if boolean value does not work properly and affects performance then how can I approach in this situation? Any Suggestions?

Please let me know.

Thanks.

|||

IIF(IsEmpty({([Measures].[Special Sales], [Date].[Fiscal Hierarchy].CurrentMember)}),NULL,

(parallelperiod([Date].[Fiscal Hierarchy].[fiscal year],1),[Measures].[Special Sales]))

Which would mean that you might have to do something like the following to force this expression to be evaluated over individual store members.

SUM( EXISTING [Store].[Store].[Store].Members ,

IIF(IsEmpty({([Measures].[Special Sales], [Date].[Fiscal Hierarchy].CurrentMember)}),NULL,

(parallelperiod([Date].[Fiscal Hierarchy].[fiscal year],1),[Measures].[Special Sales]))

)|||

There seems to be some inconsistency in the sample data: Store 3, 20070101 is shown as a special sale in the joined fact table, but not in Special_Store_Fact - I assume that is just a typo?

Anyway, since SS_Flag is already a field in the joined fact table, another approach would to create a simple true/false dimension like [Special Flag]. Then the MDX expressions could be:

For current year [Special Sales]:

([Special Flag].[SL_Flag].[True], [Measures].[Sales])

And for previous year [Special Sales]:

Sum(NonEmpty([Store].[Store].[Store].Members,

([Special Flag].[SL_Flag].[True], [Measures].[Sales])),

(ParallelPeriod([Date].[Fiscal Hierarchy].[Fiscal Year], 1),

[Measures].[Sales]))

|||

That's probably a reasonable compromise and would be relatively easy to implement.

|||

Hi,

It's been a quite long time since you posted this.

Now, we have some changes regarding this special store's calculation.

I've tried this MDX into the cube and current year value works perfectly, but last year value doesn't work properly.

MDX for the last year :

Sum(NonEmpty([Store].[Store Hierarchy].currentmember,

([Special Flag].[SL_Flag].[True], [Measures].[Sales])),

(ParallelPeriod([Date].[Fiscal Hierarchy].[Fiscal Year], 1),

[Measures].[Sales]))

> This mdx calculate all the last year value that store became a special in last year. it can't get the current special store lists.

But now, whether store was not a spcial store in last year, if store became a special store in selected date ( current) then we sum up all store's sale amount into the last year value.

below is the MDX for the last year:

([Special Flag].[SL_Flag].[True],

(ParallelPeriod([Date].[Fiscal Hierarchy].[Fiscal Year], 1),

[Measures].[Sales]))

I've tried to do different ways but still couldn't get any solutions yet.

It seems the MDX is something wrong.

If I want to use current special flag for the parallelperiod calculation how should I write MDX for that?

I think this logic makes sense to me but I don't know why it doesn't work.

I would appreciate if anybody can give me some comments.

Thanks.

MDX question

Hi,

Here is my question.

For the last year special store sales amount, we only calculate sales amount based on special store lists in current selected date.

Below is the calculation for last year specail store sales amount but the numbers do not match.

IIF(([Measures].[flag], [Date].[Fiscal Hierarchy].CurrentMember)=False,0,

(parallelperiod([Date].[Fiscal Hierarchy].[fiscal year],1),[Measures].[Special Sales]))

Please give me some comments.

Thanks in advance.

Boolean values as measures just don't work well. There probably would be a way to write the MDX calculation, but the performance would be really bad probably to the point of being unusable.

StoreID StoreName SpecialFlag

1 Store 1 False

2 Store 2 False

3 Store 1 True

Then when you insert facts you insert the StoreID that was effective for the date of the respective facts.

StoreID DateID SalesAmount

1 20060101 100

3 20070601 100

Using this technique you would be able to create a "Special Store" attribute in your store dimension and filter using that.

|||

Thanks for the reply. I think I didn't give you a right explanation.

I understand what you suggested but could you take a look at this one more time?

Below is the details how it looks like :

Selected date : 20070101

Last year date : 20060101

Sales Fact

ID Store_key Date Sales

1 store 1 20070101 100.00

2 store 2 20070101 150.00

3 store 3 20070101 100.00

4 store 1 20060101 30.00

5 store 2 20060101 30.00

6 store 3 20060101 30.00

There is an anther fact table which has a flag.

Special_Store Fact

Special_Store_key Store_key Date Key SS_flag

1 store 1 20070101 True

2 store 1 20060101 False

3 store 2 20070101 False

4 store 2 20060101 True

5 store 3 20070101 False

6 store 3 20060101 True

Now I joined this 2 Fact to add Specail Store Sales into the Sales Fact

ID Store_key Date Sales SS_flag SS_Sales (SS flag * Sales)

1 store 1 20070101 100.00 True 100.00

2 store 2 20070101 150.00 False 0.00

3 store 3 20070101 100.00 True 100.00

4 store 1 20060101 30.00 False 0.00

5 store 2 20060101 30.00 True 30.00

6 store 3 20060101 30.00 True 30.00

From this data,

we calulate SS sales amount and the total is 130.00 which is not a problem to calculate in the cube.

My problem is that for the last year SS sales amount with time calculation we developed inside the cube,

time calculation sums up all SS_Sales, so last year total is 60.00.

As you mentioned, if boolean value does not work properly and affects performance then how can I approach in this situation? Any Suggestions?

Please let me know.

Thanks.

|||

IIF(IsEmpty({([Measures].[Special Sales], [Date].[Fiscal Hierarchy].CurrentMember)}),NULL,

(parallelperiod([Date].[Fiscal Hierarchy].[fiscal year],1),[Measures].[Special Sales]))

Which would mean that you might have to do something like the following to force this expression to be evaluated over individual store members.

SUM( EXISTING [Store].[Store].[Store].Members ,

IIF(IsEmpty({([Measures].[Special Sales], [Date].[Fiscal Hierarchy].CurrentMember)}),NULL,

(parallelperiod([Date].[Fiscal Hierarchy].[fiscal year],1),[Measures].[Special Sales]))

)|||

There seems to be some inconsistency in the sample data: Store 3, 20070101 is shown as a special sale in the joined fact table, but not in Special_Store_Fact - I assume that is just a typo?

Anyway, since SS_Flag is already a field in the joined fact table, another approach would to create a simple true/false dimension like [Special Flag]. Then the MDX expressions could be:

For current year [Special Sales]:

([Special Flag].[SL_Flag].[True], [Measures].[Sales])

And for previous year [Special Sales]:

Sum(NonEmpty([Store].[Store].[Store].Members,

([Special Flag].[SL_Flag].[True], [Measures].[Sales])),

(ParallelPeriod([Date].[Fiscal Hierarchy].[Fiscal Year], 1),

[Measures].[Sales]))

|||

That's probably a reasonable compromise and would be relatively easy to implement.

|||

Hi,

It's been a quite long time since you posted this.

Now, we have some changes regarding this special store's calculation.

I've tried this MDX into the cube and current year value works perfectly, but last year value doesn't work properly.

MDX for the last year :

Sum(NonEmpty([Store].[Store Hierarchy].currentmember,

([Special Flag].[SL_Flag].[True], [Measures].[Sales])),

(ParallelPeriod([Date].[Fiscal Hierarchy].[Fiscal Year], 1),

[Measures].[Sales]))

> This mdx calculate all the last year value that store became a special in last year. it can't get the current special store lists.

But now, whether store was not a spcial store in last year, if store became a special store in selected date ( current) then we sum up all store's sale amount into the last year value.

below is the MDX for the last year:

([Special Flag].[SL_Flag].[True],

(ParallelPeriod([Date].[Fiscal Hierarchy].[Fiscal Year], 1),

[Measures].[Sales]))

I've tried to do different ways but still couldn't get any solutions yet.

It seems the MDX is something wrong.

If I want to use current special flag for the parallelperiod calculation how should I write MDX for that?

I think this logic makes sense to me but I don't know why it doesn't work.

I would appreciate if anybody can give me some comments.

Thanks.

Saturday, February 25, 2012

MDX query count of new customers with a new flag who purchased in this period and following peri

Hi all,

I would like some help with an MDX query that will give the following information - return the count of new customers for the current year that have a new customer flag set and then the count of those returning customers in the following years that have made purchases. Here is an example of the data I'm taking about -

CustomerID NewCustomer Year

1 1 2000

1 0 2001

2 1 2001

2 0 2002

1 0 2002

etc.

So, for the above I'm looking for something similar to the following -

Year 2000 New Customers = 1

Year 2001 New Customers = 1

Year 2001 Repeat Customers = 1

Year 2002 Repeat Customers = 2

Does this make sense and is it possible?

Thanks!

Brian

Please see this thread for the answer: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1225081&SiteID=1

Monday, February 20, 2012

MDX MTD Calculation for Given Day In Month

I'm trying to get a MTD calculation up to a given day in a month for current and previous months. This is for a Reporting Services report. The columns will be Total Calls and Total Calls Through {X} Days in Month. The rows will just have the months listed. I was able to do this in RS 2000 using the following dynamic mdx statement:

= "WITH MEMBER [Measures].[ParallelPeriod] AS " +

" 'Sum({[TimesDaily.Calendar].CurrentMember.[1]:[TimesDaily.Calendar].CurrentMember.[" & Now.Day.ToString() & "]}, " + " [Measures].[Calls])' " +

" SET Months AS " +

" 'LastPeriods(" + CStr(Parameters!monthsback.value) + " , Tail({[TimesDaily.Calendar].Monthname.Members}, 1).item(0))' " +

" select {[Measures].[Calls], [Measures].[ParallelPeriod]} on columns, " +

" NON EMPTY {Months} on rows " +

" from Calls " +

" Where(" + Parameters!px_workflows.value + ") "

I'm trying to reproduce this query in SSAS and RS 2005 and I can't seem to do it. My time dimension "[TimesDaily]" has one hierarchy, with the levels of [Yearname], [Monthname], and [Dayval]. I've tried variations of the following calculated member:

Sum(Descendants([TimesDaily].[Hierarchy].CurrentMember, [TimesDaily].[Hierarchy].[Monthname]), [Measures].[Calls])

This doesn't seem to be working, even without the filter for day. I need to be able to sum the values for a month, and be able to only take those days in the month that are prior to the current day. I.E., today's report would sum up the values for days 1-23 in each month listed in the report. Is there any way to do this using just CurrentMember? Any help would be greatly appreciated.

I actually figured out a way to do it. I first created a calculated member to get the current day:

[Measures].[CurrentDay] =

Tail({[TimesDaily].[Hierarchy].[Dayval].Members}, 1).item(0).MemberValue

Then I used topcount with the currentday member

[Measures].[Calls_MTD] =

Sum(TopCount(Descendants([TimesDaily].[Hierarchy].CurrentMember, 1), [Measures].[CurrentDay]), [Measures].[Calls])

I'm sure there's other ways to do this as well, like creating a Measure from the Time Dimension table and use the LastChild aggregation.

|||

If I read your requirmenst right, it's actually much simpler than this:

First, I'm going to assume you have the TimeDaily set up correctly and the corretc meta data for it. If your not sure how this should be done, you can tie yourself up in knots trying to figure it out.

Second, your members could easily be:

With

Measures.myMTD as Sum( MTD( [TimesDaily].[Hierarchy].[Dayval].CurrentMember ), [Measures].[Calls] )

Measures.myMTDprev as Sum( MTD( ParrallelPeriod( [TimesDaily].[Hierarchy].[Monthval], 1, [TimesDaily].[Hierarchy].[Dayval].CurrentMember )), [Measures].[Calls] )

MDX MTD Calculation for Given Day In Month

= "WITH MEMBER [Measures].[ParallelPeriod] AS " +

" 'Sum({[TimesDaily.Calendar].CurrentMember.[1]:[TimesDaily.Calendar].CurrentMember.[" & Now.Day.ToString()& "]}, " + " [Measures].[Calls])' " +

" SET Months AS " +

" 'LastPeriods(" + CStr(Parameters!monthsback.value)+ " , Tail({[TimesDaily.Calendar].Monthname.Members}, 1).item(0))' " +

" select {[Measures].[Calls], [Measures].[ParallelPeriod]} on columns, " +

" NON EMPTY {Months} on rows " +

" from Calls " +

" Where(" + Parameters!px_workflows.value+ ") "

Sum(Descendants([TimesDaily].[Hierarchy].CurrentMember, [TimesDaily].[Hierarchy].[Monthname]), [Measures].[Calls])

I actually figured out a way to do it. I first created a calculated member to get the current day:

[Measures].[CurrentDay] =

Tail({[TimesDaily].[Hierarchy].[Dayval].Members}, 1).item(0).MemberValue

Then I used topcount with the currentday member

[Measures].[Calls_MTD] =

Sum(TopCount(Descendants([TimesDaily].[Hierarchy].CurrentMember, 1), [Measures].[CurrentDay]), [Measures].[Calls])

I'm sure there's other ways to do this as well, like creating a Measure from the Time Dimension table and use the LastChild aggregation.

|||

If I read your requirmenst right, it's actually much simpler than this:

Second, your members could easily be:

With

Measures.myMTD as Sum( MTD( [TimesDaily].[Hierarchy].[Dayval].CurrentMember ), [Measures].[Calls] )

Measures.myMTDprev as Sum( MTD( ParrallelPeriod( [TimesDaily].[Hierarchy].[Monthval], 1, [TimesDaily].[Hierarchy].[Dayval].CurrentMember )), [Measures].[Calls] )

MDX Help.

This is my current MDX for a KPI in Performance Point:

(([Current Month].item(0), [Measures].[Revenue]) - ([Current Month].item(0).PrevMember, [Measures].[Revenue]))/ ([Current Month].item(0).PrevMember, [Measures].[Revenue])

I need to modify this to only include Customers of a of Type Dealer and Distributor in my cube.

[Customers].[Customers].[Customer Type].&[Dealer]

[Customers].[Customers].[Customer Type].&[Distributor]

[Customers].[Customers].[Customer Type].&[Direct] (do not include)

Wow...it seems like it should be so easy...but I cannot get the syntax.

Help!!!!

Linda

Hi Linda,

You could create a calculated member to agrgegate the 2 included customer types, like:

Create Member CurrentCube.[Customers].[Customers].[Dealer-Distributor] as

Aggregate({[Customers].[Customers].[Customer Type].&[Dealer],

[Customers].[Customers].[Customer Type].&[Distributor]} )

and another to compute Revenue Growth %, like:

Create Member CurrentCube.[Measures].[RevenueGrowth%] as

([Measures].[Revenue] - ([Date].[Month].PrevMember, [Measures].[Revenue]))

/ ([Date].[Month].PrevMember, [Measures].[Revenue])

Then the KPI could be:

([Current Month].item(0), [Customers].[Customers].[Dealer-Distributor], [Measures].[RevenueGrowth%])

MDX Help

Hello,

I have the following MDX Complication.

Goal - Return on the members UP TO the current day of the week. ie Today is Wednesday. I want to return all members Monday-Wednesday.

I currently have a Time.Test.[Current Day of Week] to find the current Day of Week tuple.

Tail(Filter([Time].test.[day In week].Members,NOT IsEmpty([Time].test.[day In week].CurrentMember)),1).Item(0)

Below is my [Previous Week] tuple. Is there a way to limit the member set to only include members up to the current day of the week?

Tail(Filter([Time].[Week].Members,NOT IsEmpty(Time.Week.CurrentMember)),1).Item(0).Lag(1)

Any help is much appreciated!

-Lawrence

If I understand your hierarchies correctly, you should be able to do something like the following to get a set of members from the start of the week to the last nonempty day of the week.

Code Snippet

PeriodsToDate(

[Time].[Week]

, Tail(Filter([Time].test.[day In week].Members,NOT IsEmpty([Time].test.[day In week].CurrentMember)),1).Item(0)

)

I am also wondering if the calc might not be a bit faster if you replaced the filter test with the NonEmpty function

Code Snippet

PeriodsToDate( [Time].[Week], Tail(NonEmpty([Time].test.[day In week].Members),1).Item(0))

It's not clear if you wanted to do the same sort of thing to the previous week calc, but you should be able to use similar logic

|||

Hello,

I am not sure why the below MDX always return Sunday (7) instead of the current "Day In Week"?

The hierarchy has three levels [Year], [Day In Week] & [Day]. [Day In Week] is not the lowest level, so wondering if that's causing it

[Day In Week] has members (1,2,3,4,5,6,7) and Day follows

TAIL([Time].[Test].[Day In Week].Members, 1).Item(0)

TAIL(NONEMPTY([Time].[Test].[Day In Week].Members, [Measures].[Checking Account]), 1).Item(0)

Goal - return the current Day In Week

ie [Current Day] =

TAIL( NONEMPTY([Time].[Time].[Day].Members, [Checking Account]), 4).Item(0)

ie [Current Day Of Week] =

Your help is much appreciated,

-Lawrence

|||

If your [Day In Week] attribute only has 7 members then if you have any Sunday with data then the Tail(NonEmpty()) expression will always return [Sunday].

Assuming that your definition of "current date" is the last date with data, then you will probably need to use a date attribute (one that has a member for each date) and find the last nonempty member in that attribute and then use the EXISTING operator to filter that member against the [Day In Week] attribute.

EXISTING Time.[Test].[Day In Week].Members, {[Time].[date].[Date].[<Current Date>]}

|||

My [Current Date] returns the last Day member that has data.

[Current Date] = TAIL( NONEMPTY([Time].[Time].[Day].Members, [Checking Account]), 1).Item(0)

You are saying to use this member to find the [Day In Week] member.

I tried the code below, and it returned the correct [Day In Week] member.

select EXISTING (Time.[Test].[Day In Week].Members, {TAIL( NONEMPTY([Time].[Time].[Day].Members, [Checking Account]), 1).Item(0)} ) on columns from gmd

So I used the following

[Current Day of Week] = EXISTING (Time.[Test].[Day In Week].Members, {TAIL( NONEMPTY([Time].[Time].[Day].Members, [Checking Account]), 1).Item(0)} )

However, I am getting an error (#Error) when I test [Current Day of Week]

select [time].[test].[current day of week] on columns from gmd

select ([Checking Account], [time].[test].[current day of week]) on columns from gmd

Thanks for your help again,

-Lawrence

|||

Sorry, my mistake. I got Existing and Exists() round the wrong way. Try using Exists().

PS. You should be able to hover over the #Error or double click on it in SSMS to see a more detailed error message. I'm guessing it would have been saying that the Existing operator requires a single set as input.

Monday, March 26, 2012

Membership Timeline Spanning

Member.FetchAllProperties Error

Monday, March 19, 2012

MDX: Rolling YTD

Friday, March 9, 2012

MDX question

MDX question

MDX question

Saturday, February 25, 2012

MDX query count of new customers with a new flag who purchased in this period and following peri

Monday, February 20, 2012

MDX MTD Calculation for Given Day In Month

MDX MTD Calculation for Given Day In Month

MDX Help.

MDX Help

MDX hierarchy problem

Blog Archive

About Me