List all the Values from the DimProject

Question

List all the Values from the DimProject

vineet_dubey1975

SSCrazy

Points: 2306
More actions
March 29, 2012 at 5:17 am

#391903

Hi All ,
I have
factReadingDaily Table (id,ProjectID,ReadDate,Value)
DimProject Table (ID,ProjectName,Address,ProjectType)
i have made a regular relationship with factReadingDaily Table (DimProject.id = factReadingDaily.ProjectID)
DimProject
ID ProjectName Address ProjectType
1 ProjectName1 Address1 ProjectType1
2 ProjectName2 Address2 ProjectType2
3 ProjectName3 Address3 ProjectType3
4 ProjectName4 Address4 ProjectType4
5 ProjectName5 Address5 ProjectType5
factReadingDaily Table
id ProjectID ReadDate Value
1 1 10-10-2012 10
1 1 10-10-2012 11
1 1 10-10-2012 10
1 1 10-10-2012 10
1 1 10-10-2012 10
1 2 10-10-2012 10
1 2 10-10-2012 10
1 2 10-10-2012 10
1 2 10-10-2012 10
1 3 10-10-2012 10
1 3 10-10-2012 10
1 3 10-10-2012 10
1 3 10-10-2012 10
Now when i browse the cube then i get only project.id(1,2,3) only not the project 4,5
How can i List all the projects from projectdimensions what property/settings i have to do in order to list all the projects although for the project 4,5 i have no entries in the fact table.
~Vineet

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

depends on which tool you're using to browse. assuming excel, right click in the background of the pivot table, look for PivotTableOptions, go to the Display tab, then look for a property "show items with no data on rows", check this box.

Steve.

vineet_dubey1975 SSCrazy Points: 2306 More actions · Answer 2

vineet_dubey1975

SSCrazy

Points: 2306

March 30, 2012 at 12:21 am

#1466760

HI Steve

I have to use MDX Query Instead.

~Vineet

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 3

If you're hand coding the MDX, then make sure you aren't using NON EMPTY at the beginning of your Row axis statement.

Steve.

vineet_dubey1975 SSCrazy Points: 2306 More actions · Answer 4

Thanks Steve I am greatful to you !!!

For now i have below situation to poll the data from the cube...

(Fact)FactReadingTable ->(Parent)Project.ID->(Child)ProjectEquipment.ProjectID (ReferenceProjectID)

1 ) DimProject 2) DimProjectEquipment

IN the Dimension Usage TAB

Project.ID ----Regular RelationShip--> FactReadingTable.ProjectID (Materize)

DimProjectEquipment.ProjectID ----Reference RelationShip--> Project.ID (Materize)

Now I process the CUBE then it throws me exception attribute key ProjectID '4' can not be found.

Steve How i set this , That is quite natural that a child table may not have all the reference all the project of Parent.

Let me know if you need more information here..

Is this difficult to achive ... Please help me .. I am using MSBI Analysis Service Project.

DimProject (HDR)

ID ProjectName Address ProjectType

1 ProjectName1 Address1 ProjectType1

2 ProjectName2 Address2 ProjectType2

3 ProjectName3 Address3 ProjectType3

4 ProjectName4 Address4 ProjectType4

5 ProjectName5 Address5 ProjectType5

DimProjectEquipment (Child)

EquipmentID ProjectID EquipmentType

1 1 SMO

2 1 KLP

3 2 SMO

4 2 SMO

5 3 KLP

factReadingDaily Table

id ProjectID ReadDate Value

1 1 10-10-2012 10

1 1 10-10-2012 11

1 1 10-10-2012 10

1 2 10-10-2012 10

1 3 10-10-2012 10

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 5

based on the sample data and structure, not sure why you're not able to query what you need/want.

re: the processing error, have you processed (full) your dimensions first and then process the measure group/s (facts)?

Steve.

vineet_dubey1975 SSCrazy Points: 2306 More actions · Answer 6

Yes, i have full processed it , But No luck My Child Table is Not having HDR Record 4 ,5 ....

Am i missing something on relatioship ..?