March 17, 2015 at 3:24 am
helo i have a problem to get the distinctcount:
My fact table:
Elem DateMov
------ ----------
Elem 1 20140113
Elem 2 20140215
Elem 3 20140520
Elem 1 20141011
for a range date {null:20140230} i need return 2 elems
for a range date {null:20140810} i need return 3 elems
for a range date {null:20141020} i need return 3 elems
I need make the count with the last movement of each elem in the rage of date
March 17, 2015 at 3:07 pm
Your last sentence seems to contradict the rest.
Could you please clarify...do you need to get a distinct count of elements (i.e. 2 for the first range you mentioned), or do you need to return the last element in the range (i.e. element 2 for the first range you mentioned)?
March 17, 2015 at 3:54 pm
Sorry, more information:
I have :
CREATE TABLE [dbo].[Fact](
[Id_Fact] [int] IDENTITY(1,1) NOT NULL,
[Id_Elem] [int] NULL,
[Id_Store] [int] NULL,
CONSTRAINT [PK_H_Movimientos] PRIMARY KEY CLUSTERED
(
[Id_Mov] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Dim_Store](
[Id_Store] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
CONSTRAINT [PK_Dim_Store] PRIMARY KEY CLUSTERED
(
[Id_Store] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Dim_Elem](
[Id_Elem] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
CONSTRAINT [PK_Dim_Elem] PRIMARY KEY CLUSTERED
(
[Id_Elem] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
A Fact table an two dimensions,
the data in dimensions is:
INSERT INTO [dbo].[Dim_Elem]
([Name])
VALUES
('Elem 1'),
('Elem 2'),
('Elem 3')
Go
INSERT INTO [dbo].[Dim_Store]
([Name])
VALUES
('AI'),
('AII'),
('AIII')
Go
insert into [dbo].[Fact]([Id_Elem], [Id_Store])
values
(1, 2),
(2, 1),
(1, NULL),
(3, 2),
(3, 3)
how to make this SQL Query:
select a.Id_Store, count(distinct a.Id_Elem)
from Fact a inner join (
select Id_Elem, max(Id_Fact) as Id_Fact from Fact
group by Id_Elem) x on
a.Id_Elem = x.Id_Elem and
a.Id_Fact = x.Id_Fact and
a.Id_Store is not null
group by a.Id_Store
in MDX Query
Thanks
March 18, 2015 at 10:56 am
sergio.eduardo.linares (3/17/2015)
Sorry, more information:how to make this SQL Query:
select a.Id_Store, count(distinct a.Id_Elem)
from Fact a inner join (
select Id_Elem, max(Id_Fact) as Id_Fact from Fact
group by Id_Elem) x on
a.Id_Elem = x.Id_Elem and
a.Id_Fact = x.Id_Fact and
a.Id_Store is not null
group by a.Id_Store
in MDX Query
Thanks
Still a little confused here Sergio. Your query will return a count of 1 in all cases, because of the distinct count. Could you please explain what your expected outcome is?
March 18, 2015 at 11:33 am
yes, that is an example,
this indicates that in the store_1 there is one element and in the Store_3 one element, but when insert new fact, by example, insert into [dbo].[Fact]([Id_Elem], [Id_Store])
values (2, 3), the result is two elements in the Store_3, very important, exclude the fact when Id_store is null
thanks
March 18, 2015 at 11:48 am
sergio.eduardo.linares (3/18/2015)
yes, that is an example,this indicates that in the store_1 there is one element and in the Store_3 one element, but when insert new fact, by example, insert into [dbo].[Fact]([Id_Elem], [Id_Store])
values (2, 3), the result is two elements in the Store_3, very important, exclude the fact when Id_store is null
thanks
ok...how about something like this:
with member [Measures].[Distinct_Element_Count]
as
(
distinctcount
(
{
descendants
(
[Element Dim].[Element].currentmember
, [Element Dim].[Element].[Element]
)
}
*
{
nonempty
(
descendants
(
[Store Dim].[Store].currentmember
, [Store Dim].[Store].[Store]
)
)
}
)
)
select{
[Measures].[Distinct_Element_Count]
} on 0
,{
[Store Dim].[Store].[Store]
} on 1
from[Cube]
March 18, 2015 at 12:18 pm
but i get Cero in all cases.
Store_1 -> 0
Store_3 -> 0
March 18, 2015 at 12:20 pm
sergio.eduardo.linares (3/18/2015)
but i get Cero in all cases.Store_1 -> 0
Store_3 -> 0
Without more detailed information it is pretty difficult to assist you more. Could you please provide our query and cube structure?
March 18, 2015 at 12:39 pm
The cube is based in the tables Fact, [Dim_Store] and [Dim_Elem]. [Fact] table is a measure group, the structure is very simple.
If you need more information please tell me.
Thanks
March 18, 2015 at 12:46 pm
sergio.eduardo.linares (3/18/2015)
The cube is based in the tables Fact, [Dim_Store] and [Dim_Elem]. [Fact] table is a measure group, the structure is very simple.If you need more information please tell me.
Thanks
Yes, please share your MDX query as well.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply