Analysis :: How Time Dimension Aggregate Parent Child In MDX

May 21, 2015

I have a parent child dimension. a time dimension i have year.

Assume,

ParentID ChildID sales Year

171 171 10 2014
171 172 200 2014
171 173 300 2014
171 172 44 2015

if I pass 2014 and 2015 in sub select  171 data is not coming in result. i i pass only 2014 in sub select i get value of only 2014. if I pass 2015 in sub select i didn't get any value.                                                                         

View 2 Replies


ADVERTISEMENT

Analysis :: Count Function Taking More Time To Get Count From Parent Child Dimension?

May 25, 2015

below data,

Countery
parentid
CustomerSkId
sales

A
29097
29097
10

A
29465
29465
30

A
30492
30492
40

[code]....
 
Output

Countery
parentCount

A
8

B
3

c
3

in my count function,my code look like,

 set buyerset as exists(dimcustomer.leval02.allmembers,custoertypeisRetailers,"Sales")
set saleset(buyerset)
set custdimensionfilter as {custdimensionmemb1,custdimensionmemb2,custdimensionmemb3,custdimensionmemb4}
set finalset as exists(salest,custdimensionfilter,"Sales")
Set ProdIP as dimproduct.dimproduct.prod1
set Othersset as (cyears,ProdIP)
(exists(([FINALSET],Othersset,dimension2.dimension2.item3),[DimCustomerBuyer].[ParentPostalCode].currentmember, "factsales")).count

it will take 12 to 15 min to execute.

View 3 Replies View Related

Analysis :: Parent Child Dimension Not Displaying Name Value

Apr 16, 2015

I have a parent Child attribute in my dimension.  It show the correct ID value in the reports but when I change the value from the NameColumn to be something else it still only shows the ID value. I have an identical case in another dimension and it is working correctly.  However this dimension for some reason does not seem to work and I believe they are built the same way I must be missing something.  I have tried to show the OrgNodeID and tried to display the OrgNodename but neither displays all it displays is the intdimParentOrgNodeID.My dimension table looks something like this

intdimOrgNodeID int Key (surreget key)
intOrgNodeID int (Actual ID)
intDimParentOrgNodeID
intOrgNodeName

[code]....

View 4 Replies View Related

Parent-Child-Dimension In SQL Srv 2005 Analysis Services

Jul 20, 2005

Hi experts,having a parent-child-table with the columns child_id, child_name, parent_idin SQL Server 2005 I just cannot create a parent-child dimension in BI DevStudio. Can anyone give me some hints? The Dim Build wizard doesn't createthe hierarchies, manually setting "parent" property to parent_id and "key"to child_id as well as dragging and dropping the stuff into the hierachyfield haven't just led to success. I also tried to right-click bothparent_id and child_id to create a member property. It just never workedout.Any help would be greatly appreciated.Kind regards,Joerg

View 1 Replies View Related

Analysis :: Include And Exclude Children In A Single Hierarchy In Parent Child Dimension In MDX

Apr 8, 2015

Include children and exclude children in a single  hierarchy in parent child dimension in mdx

*12-parent
 **20-parent
 - 9-parent
       --250-child1
       --210-child2
        --240-child3
aggregation of 12-parent only
aggregation of 20-parent only
aggregation of  9 with children

View 3 Replies View Related

Report Against Parent-child Referenced Dimension

Feb 20, 2008

Hello,

I want to create a report in SSRS against a cube in SSAS 2005.

I have a Sales fact table, a Products dimension which is related directly to the fact table, and a Categories dimension which is related to the fact through Products (referenced dimension).

Fact --> Products --> Categories

In the Categories dimension i have also implemented a parent-child hierarchy. Only leaf category members are joined to Products.

Is it possible to create a report, drilling down from parent categories to child categories to products?

I 've already implememted the categories parent-child report but when i add the Products dimension in the flattened rowset, the Products seem to be joined to non-leaf category members.

Thanks.

View 4 Replies View Related

Report Based On Parent-child Dimension

Feb 19, 2008

Hello,

I want to create a report based on an AdventureWordsAS parent-child dimension.

I created a new report item.
In the Data tab i created a new dataset using AnalysisServicesAS data source.
I dragged and dropped into the designer [Organization].[Organizations] and [Measures].[Financial Reporting].[Amount] from the metadata pane.

How can i configure a table control in the Layout tab, to create a drilldown report?
How do i set the Group On and Parent Group fields if i have only one attribute?

Thanks in advance

View 1 Replies View Related

Creating Report Based On Parent-child Dimension

Nov 17, 2005

Hi

View 43 Replies View Related

Aggregation Issue With Parent Child Dimension When Not Using Primary Key

Mar 10, 2006

When creating a parent child dimension I am not using the primary key of the underlying table. I define the key when I create the dimension. The parent/child relationship works fine but my measure aggregation does not work.

The dimension has a regular relation type to the measure and the primary key from the underlying dimension table is used in the relationship to join in the measure. Since I have not used the primary key in the dimension and have defined my own key the define relationship page warns me that I have selected a non-key granularity attribute and I must directly or indirectly relate all other attributes to it. When I attempt to relate the attributes on the dimension structure tab I receive errors that I have created an attribute loop.

Any ideas?

View 3 Replies View Related

Excel Export Report With Parent Child Dimension OLAP

Feb 6, 2008


Hi,

I have a problem with a reporting services 2005 report.
The data source is OLEDB for OLAP 9.


The report contains all the members of a parent child dimension. An example of the implementation is defined in the post following the forum msdn:
Http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=138549&SiteID=1.


The report works fine in web. The problem is when the report is exported as Excel, the groups disappeared and the entire dimension is ragged down.


Normally toggled groups in reporting services are exported to Excel with the appearance '+' or '-' on the left side of the sheet. This feature is very useful to hide / show lines.

It seems that with the implementation specified in the msdn post above,
It is not possible to export correctly the report in Excel.

So my questions are:

Has someone already encountered this problem?
Are there solutions with a different implementation?

Thanks,

Gurvan

View 2 Replies View Related

Analysis :: Best Way To Add Additional Parent Child Attribute Values

Apr 16, 2015

I have a parent Child attribute in my dimension.  I am currently displaying the correct ID value as the business wants.  So now they can see the rollup of the ID(intOrgNodeID ) values.They would also like to see the same rollup of the Name (vcharOrgNodeName)  for this ID.However they do not want it concatenated.  They want to be able to see them separate.You cannot create two parent child attibutes in one dimension so not sure if there is some simple trick to make this work? It seems like there should be some simple trick for this. 

My dimension table looks something like this
intdimOrgNodeID int Key (surreget key)
intOrgNodeID int (Actual ID)
intDimParentOrgNodeID
vcharOrgNodeName
In the Propertys I have set this below.
KeyColumns  = tbldimOrgNode.intDimParentOrgNodeID
NameColumn = tbldimOrgNode.intOrgNodeID

View 8 Replies View Related

Analysis :: If Parent Is NULL But Child Have Transaction Will Not Come In MDX Query

Oct 15, 2015

I have parent child dimension,

for customer id-101
---------H1-p  Sales Value-50
---c1-c  Sales Value-100
---c2-c Sales Value-200

If I will see H1 Value then it show =350

But when my head office is not exists for a customer but Child is exist i want to see data of child Aggregate value.

Example

for Customer ID-102
NO --H1 it is null
----c1 c sales Value -200
---c2 c Sales Value-50

When I want to see value of 102 at H1 leval it show me 250 . even H1 is  null.

View 3 Replies View Related

Analysis :: Average Calculation On Leaf And Aggregate Parent Level And Above Using Averaged Value?

Sep 14, 2015

At the 2nd level, the calculated measure will only be correct if it is Averaged. And at the 1st level, the calculated measure will only be correct to take these Average value from 2nd level and Sum then up together. 

Level 1 Level 2
A    
X1
X2
=================
(Avg of X1 + X2)  
B
Y1
Y2
Y3
=================
(Avg of Y1 + Y2 + Y3)
===================================================
(Sum of (Avg of X1 + X2)  + (Avg of Y1 + Y2 + Y3) )

Currently, Instead of summing all the 3 averaged value, it is averaging against all the items like,

(Avg of X1 + X2 + Y1 + Y2 + Y3)

My MDX currently looks something like that 

[Measure].[Value] / [Dimension].[Attribute.count] -> so i can get the avarage at the 2nd level but it doesn't require on the 1st level but retaining this value.

How can i do an average on the leaf level and using these average values to sum at parent level?

View 4 Replies View Related

Transact SQL :: Parent / Child Tables - Pivot Child Data To Parent Row

May 19, 2015

Given the sample data and query below, I would like to know if it is possible to have the outcome be a single row, with the ChildTypeId, c.StartDate, c.EndDate being contained in the parent row.  So, the outcome I'm hoping for based on the data below for ParentId = 1 would be:

1 2015-01-01 2015-12-31 AA 2015-01-01 2015-03-31 BB 2016-01-01 2016-03-31 CC 2017-01-01 2017-03-31 DD 2017-01-01 2017-03-31

declare @parent table (Id int not null primary key, StartDate date, EndDate date)
declare @child table (Id int not null primary key, ParentId int not null, ChildTypeId char(2) not null, StartDate date, EndDate date)
insert @parent select 1, '1/1/2015', '12/31/2015'
insert @child select 1, 1, 'AA', '1/1/2015', '3/31/2015'

[Code] .....

View 6 Replies View Related

Analysis :: SSAS Time Period Dimension Aggregation

Jul 27, 2015

I have a monthly time period dimension representing average number of students for each month. At the yearly aggregate level I don't want it to sum up the avg number of students from every month because that number is incorrect. I would like it to use the number of students from the most recent month as a roll up. Is that possible to configure in SSAS?

View 2 Replies View Related

Analysis :: How To Change The Member Names Of Time Dimension

Oct 19, 2015

<SQL Server 2008 R2>

I created a Time table using BIS. I found that the default naming of time members is too long and redundant.

For example, the wizard generated "Fiscal Calendar 2015", "Fiscal Quarter 1, 2015", etc. However, shorter expression like "FY2015", "FQ1 2015", etc would be enough for me. 

Is it possible to change the default naming rule, or does SSAS works correctly if I update the Time table values using SQL?

View 2 Replies View Related

Analysis :: Add Dimension To Cube Dimension Without Any Relation In Dimension Usage

Oct 26, 2015

When i add a dimension to the cube dimension without any relation in my dimension usage to any measure group my units are going down.However when i remove the dimension from the cube am getting the correct values.

View 4 Replies View Related

Analysis :: MDX - Aggregate Measure For Specific Time Range / Filtering And Print Only Current Month

Aug 6, 2015

At the following MDX code , I want to get the aggregate of measure only for  members that are also in the specified last time (like in examp 01/06/2015) . I tried existing and exists, but without any lack.

WITH MEMBER A AS
(b)+(C)
MEMBER [Measures].[Aggregate] AS
Aggregate(DAYTIME].[Month].&[2013-01-01T00:00:00]:[DAYTIME].[Month].&[2015-06-01T00:00:00],
([Measures].[D])

[Code] ....

View 13 Replies View Related

Analysis :: Change Dimension Storage Type As Real-time Rolap But Error Occurs?

Jul 7, 2015

I Create a measure group and two dimensions using  [AdventureWorksDW2012], I try to change one dimension's storage mode with setting property proactive caching as Real-Time ROlap. There is no any warning message when deploying and processing, but error occurs when I query in sql server analysis services, see below for the error messages and the screen capture.

Error occurred retrieving child nodes: the current operation was cancelled because another operation in the transaction failed.

View 2 Replies View Related

SQL 2012 :: Group By Parent With One Child And Multiple Child Information?

Jul 25, 2014

Basically i have three Tables

Request ID Parent ID Account Name Addresss
1452 1254789 Wendy's Atlanta Georgia
1453 1254789 Wendy's Norcross Georgia
1456 1254789 Waffle House Atlanta Georgia

Bid_ID Bid_Type Bid_Volume Bid_V Bid_D Bid_E Request_ID Parent ID
45897 Incentive 10 N/A N/A N/A 1452 1254789
45898 Incentive 10 N/A N/A N/A 1453 1254789
45899 Incentive 10 N/A N/A N/A 1456 1254789

Bid_Number Bid_Name Request_ID Parent ID
Q789456 Wendy'Off 1452 1254789
Q789457 Wendy'Reba 1452 1254789
Q789456 Wendy'Off 1453 1254789
Q789457 Wendy'Reba 1453 1254789
Q789456 Wendy'Off 1456 1254789

I want the Result

Parent ID Bid_Type Bid_Volume Bid_V Bid_D Bid_E AutoGeneratedCol
1254789 Incentive 10 N/A N/A N/A 1
1254789 Incentive 10 N/A N/A N/A 2
Bid Number AutoGeneratedCol_Link
Q789456 1
Q789457 1
Q789456 2
Request ID AutoGeneratedCol_Link
1452 1
1453 1
1456 2

View 1 Replies View Related

Transact SQL :: To Get Parent / Child / Grand Child Row On Various Order?

Jun 26, 2015

I have a table with below kind of data,

DECLARE @TBL TABLE (ItemId INT IDENTITY(1,1), ItemName NVARCHAR(20), ItemDate DATE, ParentItemName NVARCHAR(20), ItemOrder INT, ReportId INT)
INSERT INTO @TBL (ItemName, ItemDate, ParentItemName, ItemOrder, ReportId)
VALUES ('Plan', '2015-06-01', NULL, 1, 20),('Design', '2015-06-01', NULL, 2, 20),('Test', '2015-06-20', NULL, 3, 20),('Complete', '2015-06-30', NULL, 4, 20),
('Design child A', '2015-06-02', 'Design', 1, 20), ('Design child B', '2015-06-01', 'Design', 2, 20),
('Test child A', '2015-06-10', 'Test', 1, 20), ('Test child B', '2015-06-09', 'Test', 2, 20), ('Test child C', '2015-06-08', 'Test', 3, 20),
('Test grand child A', '2015-06-08', 'Test child B', 1, 20), ('Test grand child B', '2015-06-08', 'Test child B', 2, 20)
select * from @TBL

Here I want,

1. to display all parent with ORDER BY ItemOrder (no need to sort by ItemDate)
2. display all child row right after their parent (ORDER BY ItemOrder if ItemDate are same, else ORDER BY ItemDate)
3. display all grand child row right after their parent (ORDER BY ItemOrder if ItemDate are same, else ORDER BY ItemDate)

Looking for below output ...

View 3 Replies View Related

Filtering Out 1 Time Dimension Based On The Selection In Another Time Dimension

May 19, 2008

Hi!

Need some help building a query that does the following :

I have 2 Time Dimensions ; Time (Transdate) and ClosedDate (ClosedDate)

In my report/query, if [Time].CurrentMember = [Time].[YMD].[YMD].[2006].[200610].[20061031] I want to FILTER out all ClosedDate < [ClosedDate].[YMD].[YMD].[2006].[200610].[20061031]

Both Time Dimensions are Year -> Month -> Day and have the same Members.

I have every option available, using calculated Members and/or Measures to do this.

The report I'm creating is Aging of Receivables : Balance / 30 days / 60 days / etc.. But for the Aging, I need to filter like explained above.

Appreciate all help!

Regards,
Stian Bakke

View 3 Replies View Related

Analysis :: Calculated Member From Aggregate And Non-aggregate Factors

Sep 22, 2015

I have 2 Dimensions in SSAS (see below end), I want to create a calculated member, named

This_Year_Billable_Objective_Count, with its formula = BillableLastYear*(100+ BillableObjective)/100.

The first factor,  BillableLastYear is a number, aggregated (sum) from child units.

The second factor,  BillableObjective is a percent number (for example 28 means 28%), it's not aggregate. It's an dependent value for each unit.

How can I calculate This_Year_Billable_Objective_Count for each unit?

\ able 1
SELECT [UnitKey]
      ,[UnitID]
      ,[UnitName]
      ,[Unit2Name]
      ,[Unit3Name]
      ,[Unit4Name]

[Code] .....

View 6 Replies View Related

Bcp In Child Before Parent

May 19, 2004

Huh?

I've got good RI data...BUT..a developer loaded the tables in alpha table order....

Such that the child loaded BEFORE the parent....

Huh?

Got a test being set up now to mess with the child file to add a key that doesn't exist in the parent...

But Why is this allowed?

In DB2 you can specify

LOAD DATA REPLACE NO CHECK....

On the load card...you then need to run a check after to verify the data...

Is that what's going on? Is there such a utility in SQL Server to run a check post load?

I'm confused....

Any comments appreciated.

Thanks



Brett

8-)

View 2 Replies View Related

Insert Into Parent/child

Feb 25, 2008

hi,
i have two tables i want the identity value of the parent table to be inserted into the chile table
here is my code,but i don't know why it isn't working !
protected void Button1_Click(object sender, EventArgs e)    {        string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;        string pcontent = TextBox1.Text;        string data = TextBox2.Text;        addtopic(pcontent,connectionString);        addfile(data, connectionString);                    }    public void addtopic(string subject,string connstring)    {         using (SqlConnection connection = new SqlConnection(connstring))        {                        SqlCommand command = new SqlCommand("INSERT INTO parent" + "(content)" +              "Values(@content)", connection);            command.Parameters.Add("@content", SqlDbType.Text).Value = subject;            connection.Open();            command.ExecuteNonQuery();                    }    }    public void addchild(string name, string connstring)    {                using (SqlConnection connection = new SqlConnection(connstring))        {Guid id = Guid.NewGuid();           SqlCommand commandd = new SqlCommand("INSERT INTO child" + "(parentid,data,uniqueid)" +              "Values(@@IDENTITY,@data,@uid)", connection);            commandd.Parameters.Add("@data", SqlDbType.NVarChar, 50).Value = name;            commandd.Parameters.Add("@uid", SqlDbType.UniqueIdentifier).Value = id;           
 
thanks in advance :)
           
            connection.Open();            commandd.ExecuteNonQuery();        }
    }

View 2 Replies View Related

Parent Child Transversing

Jul 16, 2005

I have a parent/child relationship in a relational database broken out like this:
Table Name: categories[category_id]         int            (primary_key NOT NULL),[category_name]       varchar(50),[parent_fk]           int
The parent references the category_id in the same table to create the parent/child relationships. I can get all the bottom level categories by doing this:
select category_id, category, parent_fk from categories where  category_id not in (  select parent_fk from categories)
Each bottom-level category has a count attached to it.
The problem I have is getting the counts rolled up for each parent of the bottom level. A parent could/will have multiple bottom-level categories (and counts).
My sql is a little weak, could you help me out?  I can utilize everying in SQL 2000 (stored proc, UDF, anything).
Thanks!

View 3 Replies View Related

Getting A Tree Using Parent-child

Sep 27, 2013

Here is the table - Company with fields:

CompanyID, ParentCompanyID (both integers)

Given a CompanyID - I want to get all the children for the Company.

I did similar procedures but somehow, could not get this to work.

View 4 Replies View Related

Parent Child Problem In Sql

Jul 1, 2007

I want to find all the child of a node in a tree . A child can have multiple parent i.e 2 can be place under multiple parent . The folling is the data:

ParentID ChildID
-------------------------
10 8
8 6
8 7
7 2
7 5
5 1
5 2
6 2

child of parent if input 10
8
7
6
2
2
5
1
(2) can be neglected

input 7
2
5
1
(2) can be neglected

input 8
7
2
5
(2) can be neglected


Plz help we to write the sql statements

Thanks

View 2 Replies View Related

Hierarchy – Parent And Child

Aug 2, 2007

I am designing a table to represent data in hierarchy structure, I use id and parent id to represent the data in hierarchy form:

Id | parent_id
---+-----------
1 | 0
2 | 0
3 | 0
4 | 1
5 | 1
6 | 4
7 | 4
8 | 7
9 | 7


This structure requires complicated queries (recursive call) to find out all the child of a root node, so I have added another field for the root id.
Is this a good relational database design ? kindly suggest.

Id | parent_id | root_id
---+-----------+---------
1 | 0 |
2 | 0 |
3 | 0 |
4 | 1 | 1
5 | 1 | 1
6 | 4 | 1
7 | 4 | 1
8 | 7 | 1
9 | 7 | 1
10 | 2 | 2
11 | 2 | 2
12 | 10 | 2
13 | 10 | 2

Rgds
Vijay

View 3 Replies View Related

Parent Child Tables

Jul 20, 2005

In our database we have a list of devices in a "Device" Table, eachhaving one or more IP's located in the "IP" Table linked through aforein key on the DeviceID Column.I would like to retrieve this information as SuchDeviceID IpAddress1 10.0.0.1, 10.0.0.2, 10.0.0.32 ...345etc.Is it possible to do that without using cursors? Through a query?

View 1 Replies View Related

Parent ID In A Slowly Changing Dimension

Dec 7, 2006

Hi There,

Just wondering if any of you implemented a (Kimball type 2) dimension structure, in which a ParentID column exists which points to a record from the same dimension table, using a SCD objects in SSIS. The ParentID column would have to be "Historical".

The challange here is that you would need to go through the table twice somehow, because if I would do a lookup of the parent record in the first run, I wouldn't be sure if I got the right parent record.

Thnx, Jeroen.

View 13 Replies View Related

Aggregate Data In Dimension Tables?

Jan 30, 2008

I'm still fairly new to cubes, so bear with me. I'm trying to figure out if I should include aggregate data (e.g. total employees per facility) in a dimension table or if I should use the finished cube to get the counts (MDX?). Thanks for any help that you can provide.

influent

View 1 Replies View Related

Selecting Parent / Child Relationships

Jun 28, 2004

Hello all, I'm having a real hard time trying to figure this one out. I'm trying to create a sql query that selects both the parent name and it's children, but it's got to loop through all the record sets to populate a drop down as an end result.

I think I thought this out correctly:
I have 2 tables

category
relationship

tbl category
cat_id //auto int
cat_name // varchar

relationship
r_id // auto int
parent_id // int
child_id // int

both the parent_id and child_id are associated with the cat_id
in my category table I could have
1cars // this is parent
2 audi
3 bmw
4 chevy

Table data example

r_id parent_id child_id
****************************
1 1 15
2 1 16
3 1 17
4 2 55
5 2 56
etc...
I want to select both the parent cat_name from category and also select the child cat_name where the parent_id = #

I can do it manaully like this
select cat_name, cat_id, parent_id , child_id from category, relationships where child_id = cat_id and parent_id = 1

what is the best way to loop through all the parent ids to find child category?
Could this be done in a stored procedure?

thanks in advance.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved