missing data

[#1]

hello Friends

i have a fact table which its name is Sales/returns with some dimension tables like date, product, ....

i have recorded my sales data in it. after sometime my boss asked me to make some reports about reaching sales goal for some of days. so i made an excel file which is maintaining the goals for some of dates. for making a relation between this file and my main warehouse i used the same name for date fields of the excel as the date fields of my date dimension.

well, now the problem is that in my reports none of the information which is for the dates that are not contained in excel file are shown in none of my reports.

any one can help me?

thanks

mana

this is my load code:

LOAD y as [d Year], 

          m as [d Month], 

          d as [d Day],

          [Goal]

FROM

(biff, embedded labels, table is Sheet1$);

OLEDB CONNECT TO [Provider=MSOLAP.3;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MSHProject;Data Source=.;Location=.];

LOAD

  "[Measures].[F Sr Sales Quantity]" as [F Sr Sales Quantity],

  "[Measures].[F Sr Gross Sales Amount]" as [F Sr Gross Sales Amount],

  "[Measures].[F Sr Net Sales Amount]" as [F Sr Net Sales Amount],

  "[Measures].[F Sr Sales Weight]" as [F Sr Sales Weight],

  "[Measures].[F Sr Factor Row]" as [F Sr Factor Row],

  "[Measures].[F Sr Sales Quantity According Second Unit]" as [F Sr Sales Quantity According Second Unit],

  "[Branch Dim].[b Address].[b Address].[MEMBER_CAPTION]" as [b Address],

  "[Branch Dim].[b City].[b City].[MEMBER_CAPTION]" as [b City],

  "[Branch Dim].[b Name].[b Name].[MEMBER_CAPTION]" as [b Name],

  "[Branch Dim].[Bprovince].[Bprovince].[MEMBER_CAPTION]" as [Bprovince],

  "[Branch Dim].[Id].[Id].[MEMBER_CAPTION]" as [Id Branch],

  "[Date Dim].[d Day].[d Day].[MEMBER_CAPTION]" as [d Day],

  "[Date Dim].[d Finance Year].[d Finance Year].[MEMBER_CAPTION]" as [d Finance Year],

  "[Date Dim].[d Gregorian Full Date].[d Gregorian Full Date].[MEMBER_CAPTION]" as [d Gregorian Full Date],

  "[Date Dim].[d Month].[d Month].[MEMBER_CAPTION]" as [d Month],

  "[Date Dim].[d Month Name].[d Month Name].[MEMBER_CAPTION]" as [d Month Name],

  "[Date Dim].[d Num Of Day In This Year].[d Num Of Day In This Year].[MEMBER_CAPTION]" as [d Num Of Day In This Year],

  "[Date Dim].[d Season].[d Season].[MEMBER_CAPTION]" as [d Season],

  "[Date Dim].[d Season Name].[d Season Name].[MEMBER_CAPTION]" as [d Season Name],

  "[Date Dim].[d Test Date].[d Test Date].[MEMBER_CAPTION]" as [d Test Date],

  "[Date Dim].[d Test Date Day].[d Test Date Day].[MEMBER_CAPTION]" as [d Test Date Day],

  "[Date Dim].[d Test Date Month].[d Test Date Month].[MEMBER_CAPTION]" as [d Test Date Month],

  "[Date Dim].[d Test Date Year].[d Test Date Year].[MEMBER_CAPTION]" as [d Test Date Year],

  "[Date Dim].[d Today Shamsi].[d Today Shamsi].[MEMBER_CAPTION]" as [d Today Shamsi],

  "[Date Dim].[d Today Shamsi Day].[d Today Shamsi Day].[MEMBER_CAPTION]" as [d Today Shamsi Day],

  "[Date Dim].[d Today Shamsi Month].[d Today Shamsi Month].[MEMBER_CAPTION]" as [d Today Shamsi Month],

  "[Date Dim].[d Today Shamsi Year].[d Today Shamsi Year].[MEMBER_CAPTION]" as [d Today Shamsi Year],

  "[Date Dim].[d Total Num Of Days Of Month].[d Total Num Of Days Of Month].[MEMBER_CAPTION]" as [d Total Num Of Days Of Month],

  "[Date Dim].[d Week Day Name].[d Week Day Name].[MEMBER_CAPTION]" as [d Week Day Name],

  "[Date Dim].[d Year].[d Year].[MEMBER_CAPTION]" as [d Year],

  "[Date Dim].[Full Date].[Full Date].[MEMBER_CAPTION]" as [Full Date],

  "[Date Dim].[Id].[Id].[MEMBER_CAPTION]" as [Id 2],

  "[Distribution Dim].[d Branch].[d Branch].[MEMBER_CAPTION]" as [d Branch],

  "[Factor Specification Dim].[Fs Factor Inventory Name].[Fs Factor Inventory Name].[MEMBER_CAPTION]" as [Fs Factor Inventory Name],

  "[Factor Specification Dim].[Fs Factor Type].[Fs Factor Type].[MEMBER_CAPTION]" as [Fs Factor Type],

  "[Factor Specification Dim].[Id].[Id].[MEMBER_CAPTION]" as [Id Factor],

  "[Moshtari Dim].[c Category].[c Category].[MEMBER_CAPTION]" as [c Category],

  "[Moshtari Dim].[c City].[c City].[MEMBER_CAPTION]" as [c City],

  "[Moshtari Dim].[c Mantaghe].[c Mantaghe].[MEMBER_CAPTION]" as [c Mantaghe],

  "[Moshtari Dim].[c Name].[c Name].[MEMBER_CAPTION]" as [c Name],

  "[Moshtari Dim].[c Path].[c Path].[MEMBER_CAPTION]" as [c Path],

  "[Moshtari Dim].[c Posit].[c Posit].[MEMBER_CAPTION]" as [c Posit],

  "[Moshtari Dim].[c Province].[c Province].[MEMBER_CAPTION]" as [c Province],

  "[Moshtari Dim].[c Site].[c Site].[MEMBER_CAPTION]" as [c Site],

  "[Moshtari Dim].[Id].[Id].[MEMBER_CAPTION]" as [Id Moshtari],

  "[Product Dim].[Id].[Id].[MEMBER_CAPTION]" as [Id 2 3 4 5],

  "[Product Dim].[p Category].[p Category].[MEMBER_CAPTION]" as [p Category],

  "[Product Dim].[p Product Group].[p Product Group].[MEMBER_CAPTION]" as [p Product Group],

  "[Product Dim].[p Product Name].[p Product Name].[MEMBER_CAPTION]" as [p Product Name],

  "[Product Dim].[p Second Div First Unit].[p Second Div First Unit].[MEMBER_CAPTION]" as [p Second Div First Unit],

  "[Sales Team Dim].[Id].[Id].[MEMBER_CAPTION]" as [Id Visitor],

  "[Sales Team Dim].[St Visitor].[St Visitor].[MEMBER_CAPTION]" as [St Visitor];

SELECT

{

  [Measures].[F Sr Sales Quantity],

  [Measures].[F Sr Gross Sales Amount],

  [Measures].[F Sr Net Sales Amount],

  [Measures].[F Sr Sales Weight],

  [Measures].[F Sr Factor Row],

  [Measures].[F Sr Sales Quantity According Second Unit]

}

DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS , NON EMPTY

CrossJoin(

  {[Branch Dim].[b Address].Children},

  {[Branch Dim].[b City].Children},

  {[Branch Dim].[b Name].Children},

  {[Branch Dim].[Bprovince].Children},

  {[Branch Dim].[Id].Children},

  {[Date Dim].[d Day].Children},

  {[Date Dim].[d Finance Year].Children},

  {[Date Dim].[d Gregorian Full Date].Children},

  {[Date Dim].[d Month].Children},

  {[Date Dim].[d Month Name].Children},

  {[Date Dim].[d Num Of Day In This Year].Children},

  {[Date Dim].[d Season].Children},

  {[Date Dim].[d Season Name].Children},

  {[Date Dim].[d Test Date].Children},

  {[Date Dim].[d Test Date Day].Children},

  {[Date Dim].[d Test Date Month].Children},

  {[Date Dim].[d Test Date Year].Children},

  {[Date Dim].[d Today Shamsi].Children},

  {[Date Dim].[d Today Shamsi Day].Children},

  {[Date Dim].[d Today Shamsi Month].Children},

  {[Date Dim].[d Today Shamsi Year].Children},

  {[Date Dim].[d Total Num Of Days Of Month].Children},

  {[Date Dim].[d Week Day Name].Children},

  {[Date Dim].[d Year].Children},

  {[Date Dim].[Full Date].Children},

  {[Date Dim].[Id].Children},

  {[Distribution Dim].[d Branch].Children},

  {[Factor Specification Dim].[Fs Factor Inventory Name].Children},

  {[Factor Specification Dim].[Fs Factor Type].Children},

  {[Factor Specification Dim].[Id].Children},

  {[Moshtari Dim].[c Category].Children},

  {[Moshtari Dim].[c City].Children},

  {[Moshtari Dim].[c Mantaghe].Children},

  {[Moshtari Dim].[c Name].Children},

  {[Moshtari Dim].[c Path].Children},

  {[Moshtari Dim].[c Posit].Children},

  {[Moshtari Dim].[c Province].Children},

  {[Moshtari Dim].[c Site].Children},

  {[Moshtari Dim].[Id].Children},

  {[Product Dim].[Id].Children},

  {[Product Dim].[p Category].Children},

  {[Product Dim].[p Product Group].Children},

  {[Product Dim].[p Product Name].Children},

  {[Product Dim].[p Second Div First Unit].Children},

  {[Sales Team Dim].[Id].Children},

  {[Sales Team Dim].[St Visitor].Children})

ON ROWS

FROM [Darya Warehouse MSH]

;

[#2]

well i think by myself its because of bad relation field selection. i mean i think i should change the field with the primary key of date dimension table, right?

[vgutkovsky · Accepted solution]

If your chart/table is attempting to analyze actual-to-goal, then for dates where goal is missing, the expression would evaluate to zero or null. By default, that is suppressed in charts. You can un-suppress it via the Presentation tab of the chart properties.

You also have a synthetic key between your tables that is caused by having 3 fields with an identical name. Try to make a single date key, or investigate alternate data structure (such as concatenation onto a single Fact table and the addition of a calendar dimension).

Regards,

Vlad

[#4]

thanks Vlad.

helped a lot 🙂