NULL values in Cube for measures

I am facing one problem for cubes. Eventhough I have NULL for certain measures in cube it gets populated as 0 in Cube. Has anyone faced this kind of problem. How can I solve this

eg In Database
D1 D2 Measure1 Measure2
A B NULL 10
C D 10 NULL

Expected in Cube
D1 D2 Measure1 Measure2
A B 10
C D 10

Currently getting
D1 D2 Measure1 Measure2

A B 0 10
C D 10 0

If anyone can provide some tips on this issue it would be of great help to me.

Any tip will be greatly appreciated

Thanks,

This is the default behaviour of Analysis Services, however in Analysis Services 2005 you can set a measure's NullProcessing property (select the measure in the Cube Editor and then expand the Source property in the properties pane) to Preserve.

HTH,

Chris

|||

Hi Chris,

Thanks for reply. If I am not mistaken it means its not possible in AS2000. Can you suggest any workaround in AS2000 if you have come across any. It would be of great help

Regards,

SP

|||

I don't think there is a workaround. Why do you need these null values?

Chris

|||

Hi Chris,

I am using a tool for viewing cube which can hide row or column if it is entirely null

e.g Time

T1 T2 T3

Group Cap Measures

G1 C1 M1 NULL NULL NULL

M2 10 20 21

G2 C2 M1 32 23 24

M2 NULL NULL NULL

G3 C3 M1 12 13 12

M2 11 21 22

Now measure M1 is not valid for G1 C1 so its inserted as NULL in database

Similarly M2 is not valid for G2 C2 so its entered as NULL in database for G2 C2

For G3C3 both M1 and M2 has values and are valid. Similarly it goes on continuing for each and every group.

So now user wants to see in report only measures valid for that level. If I am able to keep NULL in database as NULL in cube then I can hide row entirely if its NULL and my report will look like

Time

T1 T2 T3

Group Cap Measures

G1 C1 M2 10 20 21

G2 C2 M1 32 23 24

G3 C3 M1 12 13 12

M2 11 21 22

Hope you have got what I am trying to explain. So I need to keep NULLS. Can this be done

Thanks

SP

|||

So, to be clear, some measures aren't relevant for certain members on your dimension? This sounds like a fact table modelling issue to me - M1 and M2 should be split into separate fact tables (avoiding the need for these null rows) which you then build separate cubes from. You can then use a virtual cube to join these two cubes together and get the result you're looking for, with nulls appearing for invalid combinations in the way you want.

Chris

|||

Hi Chris,

This solution sounds to be interesting to me. I think I will definitely try this but with this I will have to split my cube into more than 20 cubes and then use virtual cube to connect all these cubes. This will make my cube structure really complicated. But its definitely worth giving a try. What is your opinion about performance in such a case. Will I be getting performance as good as single cube?

Is there any other way worth giving a try for this issue

Thanks,

SP

|||

If you'll have 20 cubes then it will certainly make things complicated, but I doubt it will hurt performance - if anything it will probably perform better than putting everything in one fact table/cube.

Do you really have 20 different groups of measures with different dimensionality?

|||

Hi Chris,

I am afraid but I am really having more than 20 measures with different dimensionality.

Can you suggest something else?

Thanks,

|||

Well, if you really do, then yes it's going to be complicated having twenty cubes but it's the best approach to take in my opinion.

Out of interest, can you give us some details of what's in these twenty (potential) fact tables? That's a lot of different data types you must be integrating.

Chris

|||

Do you need the data type of facts that I am using?

Currently I am using facts which are all float except 4 which are of type string.

its basically related to operation of certain machinery and plants to get different info like inputs, outputs, capability etc. basically giving all info of these machines. So certain info is not valid at particular levels.

|||

No, I don't think the data types are all that relevant here. I was just asking to find out if you really did need twenty cubes, but it sounds like you do if you have twenty different machines/plants/etc. Although AS2005 handles this kind of problem much more elegantly, it's definitely possible to achieve the results you want with AS2K. Let me know if I can be any more help! Oh, and if you haven't already, check out the VALIDMEASURE MDX function, I have a feeling you'll be needing it soon...

Chris

|||

Hi Chris,

Thanks for your help. Atleast I could convey to user that how complex it will make the design for preserving the NULLS with this option.

You said Although AS2005 handles this kind of problem much more elegantly, it's definitely possible to achieve the results you want with AS2K.

So were you talking about any other possible ways in AS2k or its the same we discussed initially.

Ya definetly I will look into Validmeasure function and check if it can provide me some help.

Please let me know in future if you could find any other way out for my problem

Thanks a lot,

SP

|||

Sorry, just to be clear: putting data into separate fact tables is the only approach in AS2K that I can recommend. I'm sure you could probably make it work with your single fact table and some clever MDX, but it would be extremely complex and perform poorly.

Chris

NULL Returned for Populated Column

Here is a strange one:

The following partitioned table (partitioned for 31 days) with a single nonclustered, nonunique index is behaving improperly on a select. A standard select using the index returns all NULL values for altitude yet if I do a select for the TOP x rows ordered by reporttime desc for not null altitudes, I see the values stored in the column. There are over 5,000,000 rows in the table with a relatively even distribution over each partition (except partition 1 which is kept empty).

CREATE TABLE [dbo].[APRSTrack](
[CallsignSSID] [varchar](9) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
[ReportTime] [datetime] NOT NULL,
[Latitude] [float] NOT NULL,
[Longitude] [float] NOT NULL,
[Icon] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Course] [smallint] NULL,
[Speed] [int] NULL,
[Altitude] [int] NULL
) ON [onemonth]([ReportTime])

CREATE NONCLUSTERED INDEX [IX_APRSTrack] ON [dbo].[APRSTrack]
(
[CallsignSSID] ASC,
[ReportTime] ASC
)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90, ONLINE = OFF) ON [onemonth]([ReportTime])

The failing select statement is:

SELECT *
FROM [Ham Radio].[dbo].[APRSTrack]
where callsignssid = 'EA3ABN-9' and reporttime >= '2006-06-06'
order by reporttime

and the working select statement is:

select top 100 *
from APRSTrack
where (not (altitude is null)) and reporttime >= '2006-06-06'
order by reporttime desc

The execution plan for the first lookup is:

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.0" Build="9.00.2047.00" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="2.67004" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.00992778" StatementText="SELECT *&#xD;&#xA; FROM [Ham Radio].[dbo].[APRSTrack]&#xD;&#xA;where callsignssid = 'EA3ABN-9' and reporttime &gt;= '2006-06-06'&#xD;&#xA;order by reporttime" StatementType="SELECT">
<StatementSetOptions ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="false" />
<QueryPlan CachedPlanSize="60">
<RelOp AvgRowSize="54" EstimateCPU="1.11608E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2.67004" LogicalOp="Inner Join" NodeId="0" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00992778">
<OutputList>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="CallsignSSID" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="ReportTime" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Latitude" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Longitude" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Icon" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Course" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Speed" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Altitude" />
</OutputList>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Column="PtnIds1007" />
<ColumnReference Column="Bmk1000" />
</OuterReferences>
<PartitionId>
<ColumnReference Column="PtnIds1007" />
</PartitionId>
<RelOp AvgRowSize="38" EstimateCPU="2.67004E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2.67004" LogicalOp="Compute Scalar" NodeId="1" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0032852">
<OutputList>
<ColumnReference Column="Bmk1000" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="CallsignSSID" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="ReportTime" />
<ColumnReference Column="PtnIds1007" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="PtnIds1007" />
<ScalarOperator ScalarString="RangePartitionNew([Ham Radio].[dbo].[APRSTrack].[ReportTime],(1),'2006-05-07 00:00:00.000','2006-05-08 00:00:00.000','2006-05-09 00:00:00.000','2006-05-10 00:00:00.000','2006-05-11 00:00:00.000','2006-05-12 00:00:00.000','2006-05-13 00:00:00.000','2006-05-14 00:00:00.000','2006-05-15 00:00:00.000','2006-05-16 00:00:00.000','2006-05-17 00:00:00.000','2006-05-18 00:00:00.000','2006-05-19 00:00:00.000','2006-05-20 00:00:00.000','2006-05-21 00:00:00.000','2006-05-22 00:00:00.000','2006-05-23 00:00:00.000','2006-05-24 00:00:00.000','2006-05-25 00:00:00.000','2006-05-26 00:00:00.000','2006-05-27 00:00:00.000','2006-05-28 00:00:00.000','2006-05-29 00:00:00.000','2006-05-30 00:00:00.000','2006-05-31 00:00:00.000','2006-06-01 00:00:00.000','2006-06-02 00:00:00.000','2006-06-03 00:00:00.000','2006-06-04 00:00:00.000','2006-06-05 00:00:00.000','2006-06-06 00:00:00.000')">
<Intrinsic FunctionName="RangePartitionNew">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="ReportTime" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-07 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-08 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-09 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-10 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-11 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-12 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-13 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-14 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-15 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-16 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-17 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-18 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-19 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-20 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-21 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-22 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-23 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-24 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-25 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-26 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-27 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-28 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-29 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-30 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-31 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-06-01 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-06-02 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-06-03 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-06-04 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-06-05 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-06-06 00:00:00.000'" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="34" EstimateCPU="0.000159937" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2.67004" LogicalOp="Index Seek" NodeId="2" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.00328494">
<OutputList>
<ColumnReference Column="Bmk1000" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="CallsignSSID" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="ReportTime" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Bmk1000" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="CallsignSSID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="ReportTime" />
</DefinedValue>
</DefinedValues>
<Object Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Index="[IX_APRSTrack]" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="CallsignSSID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="'EA3ABN-9'">
<Const ConstValue="'EA3ABN-9'" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
<StartRange ScanType="GE">
<RangeColumns>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="ReportTime" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="'2006-06-06 00:00:00.000'">
<Const ConstValue="'2006-06-06 00:00:00.000'" />
</ScalarOperator>
</RangeExpressions>
</StartRange>
</SeekPredicate>
</SeekPredicates>
<PartitionId>
<ColumnReference Column="ConstExpr1012">
<ScalarOperator ScalarString="(32)">
<Const ConstValue="(32)" />
</ScalarOperator>
</ColumnReference>
</PartitionId>
</IndexScan>
</RelOp>
</ComputeScalar>
</RelOp>
<RelOp AvgRowSize="35" EstimateCPU="0.000251393" EstimateIO="0.003125" EstimateRebinds="1.67004" EstimateRewinds="0" EstimateRows="1" LogicalOp="RID Lookup" NodeId="7" Parallel="false" PhysicalOp="RID Lookup" EstimatedTotalSubtreeCost="0.00663141">
<OutputList>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Latitude" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Longitude" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Icon" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Course" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Speed" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Altitude" />
</OutputList>
<IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Latitude" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Longitude" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Icon" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Course" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Speed" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Altitude" />
</DefinedValue>
</DefinedValues>
<Object Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" TableReferenceId="-1" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Column="Bmk1000" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Bmk1000]">
<Identifier>
<ColumnReference Column="Bmk1000" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
</SeekPredicates>
<PartitionId>
<ColumnReference Column="PtnIds1007" />
</PartitionId>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<ParameterList>
<ColumnReference Column="@.2" ParameterCompiledValue="'2006-06-06'" />
<ColumnReference Column="@.1" ParameterCompiledValue="'EA3ABN-9'" />
</ParameterList>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>

Any ideas?

Pete Loveall
AME Corp.

This is definitely an issue with the nonclustered index. I changed the index to a clustered index and everything works fine. In fact, the table originally had a clustered index just on reporttime and IX_APRSTrack was a nonclustered index. When I dropped the reporttime clustered index, all entries added after that point were improperly displayed in any query using the nonclustered index. Once I changed the nonclustered index to a clustered index, every row displays properly now including those that did not display properly previously.

SQL 2005 query has some definite issues with nonclustered indexes. Some of those issues may be related to partioned indexes on partitioned tables, but the nonclustered seeks are definitely wrong. The indexes and databases check just fine using CHECKDB. I also notice that doing a search using a nonclustered index which should be confined to one or two partitions is actually done across all partitions (see the execution plan above). The same SELECT done against a clustered index restricts the lookup to the relavent partitions.

This table has a high rate of insertions which make the clustered index undesireable. However, if that is what must be done until Microsoft fixes the query generator, we'll limp along.

Microsoft, are you listening?

Pete Loveall
AME Corp.

null parameter

In the report I created, there is a drop down used as a parameter, i.e. Sector.
This drop down gets populated with data. It also has <Select a Value> in there.
When no item is selected, i.e. only <Select a Value> is shown in the drop down list, when the view Report button is clicked, the message says:
Please select a value for the parameter 'Sector'
The view Report used a stored procedure which allows null for this Sector parameter.
The SP does work fine in the sql server query analyser because it returns data with and without the sector parameter.
Any thoughts please?
Thanks

Did you check off the property to "Allow null value" on the parameter. This may help you.|||

If I uncheck that then I will have to select an item from the list.

I would like to have the option to NOT select an item from the list so that when View Report is clicked then all data is returned.

Thanks

|||

If a parameter is Nullable, meaning "Allow null value" is checked, RS will not prohibit the parameter from having a value of NULL. However, if the parameter has a valid values list, NULL must also appear in that list. RS will not insert NULL as a valid value into the list you provide.

In your scenario, you will need to explicitly include NULL in the values returned by your parameter data set. If you are using the SQL data extension, one way of doing this is as follows:

Code Block

(select
[Label],
[Value]
from [ParameterValues])
union
(select
'None',
NULL)

Where [Label], [Value], and [ParameterValues] are your column and table names.

|||and what if one use a connection of type Microsoft SQL Server Analysis Serves?
If I already have an almost finished report, with the dataset already setted, how can I change it (producing the less side effects possible) in order to have null in the valid values?
thanks in advance!