Inventory Cube

How have other tackled the Inventory Cube? I am thinking
along the lines of
these two strategies
1. 1 and only one snap shot at a time that we decide
accurately reflects
realistic inventory levels. Meaning not necessarily at
the end of the month
when they could be low.
or
2. Take 4 snapshots a month and average those 4 together
giving me my
inventory snap shot for the month. Does anyone have other
ideas? I'm
interested in hearing how you have solved this interesting
situation.
-mikeHi Michael,
I noticed that the issue was posted in the following groups
microsoft.public.sqlserver.datawarehouse and
microsoft.public.sqlserver.olap. We have added a reply to you at
http://support.microsoft.com/newsgr...&NewsGroup=micr
osoft.public.sqlserver.olap
If you have follow up questions, please post there and we will work with
you. Thanks.
Regards.
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.sql

Invalid character in a report

Hi!

Trying to generate a report (using WebForm ReportViewer, from dynamically created RDL report and SQL Server 2005 OLAP cube), if a database field contains control characters (code < 0x20), Reporting Services generate following message:

' ', hexadecimal value 0x02, is an invalid character. Line 1, position 2376.

Is it possible to ignore that? I don't care if browser shows an octopus, the report must work.

Thanks, Andrei.

Could you publish RDL (or e-mail it to me)?

thanks!

|||

Lev,

I've just emailed RDL and other details to you.

I could reproduce the problem using sample AdventureWorksDW and OLAP (standard edition).

Create an OLAP table report, put e.g. Model Name in one of columns. The report works fine. Now change e.g. ModelName for one of products, to include control character(s), e.g.:

update DimProduct
set ModelName = 'Mountain-100 ' + char(31) + char(2) + ' AB'
where ProductAlternateKey = 'BK-M82S-38'

Reprocess Product dimension.

Refresh the report. Once Montain-100 model is about to be displayed on the page you should get following message:

hexadecimal value 0x1F, is an invalid character. Line 1, position 2385.

I've just noticed that after the change applied even OLAP browser of SQL Server Management Studio generates the same error if only the ModelName is about to be displayed.

So it may be Analysis Service's problem indeed.

I've tried with non-OLAP reports in Reporting Services, and they work fine, displaying square placeholders.

|||That is known AS issue. Certain control characters cannot be transmitted from server to client.

Invalid character in a report

Hi!

Trying to generate a report (using WebForm ReportViewer, from dynamically created RDL report and SQL Server 2005 OLAP cube), if a database field contains control characters (code < 0x20), Reporting Services generate following message:

' ', hexadecimal value 0x02, is an invalid character. Line 1, position 2376.

Is it possible to ignore that? I don't care if browser shows an octopus, the report must work.

Thanks, Andrei.

Could you publish RDL (or e-mail it to me)?

thanks!

|||

Lev,

I've just emailed RDL and other details to you.

I could reproduce the problem using sample AdventureWorksDW and OLAP (standard edition).

Create an OLAP table report, put e.g. Model Name in one of columns. The report works fine. Now change e.g. ModelName for one of products, to include control character(s), e.g.:

update DimProduct
set ModelName = 'Mountain-100 ' + char(31) + char(2) + ' AB'
where ProductAlternateKey = 'BK-M82S-38'

Reprocess Product dimension.

Refresh the report. Once Montain-100 model is about to be displayed on the page you should get following message:

hexadecimal value 0x1F, is an invalid character. Line 1, position 2385.

I've just noticed that after the change applied even OLAP browser of SQL Server Management Studio generates the same error if only the ModelName is about to be displayed.

So it may be Analysis Service's problem indeed.

I've tried with non-OLAP reports in Reporting Services, and they work fine, displaying square placeholders.

|||That is known AS issue. Certain control characters cannot be transmitted from server to client.

INTERSECT MDX Query - Reg

Hi Everyone,

We are facing some problem in the cube particularly in INTERSECT function.

Here are the details.

Dimension Tables:

DimTime 200601

200602

DimProduct 01

02

DimUser 101

102

103

FactTables:

FactPlayer

Time Product User

200601 01 101

200601 02 101

200601 01 102

Transact SQL Query:

Select Count(*) from

(

Select userid from FactPlayer where productId = 01

INTERSECT

Select userid from FactPlayer where productId= 02

)

PlayerCount

We want the same result from MDX query. Can you please guide us how to do it by using INTERSECT.

Expecting your valuable reply.

Regards

Vijay

Hi Vijay,

You could solve this using the Intersect function, but you don't need to. Here's an example from Adventure Works showing all the Customers who bought products from two different subcategories (mountain bikes and caps):

select {[Measures].[Internet Sales Amount]} on 0,
nonempty(
nonempty(
[Customer].[Customer].[Customer].members,
([Measures].[Internet Sales Amount], [Product].[Subcategory].&[1])
)
, ([Measures].[Internet Sales Amount],[Product].[Subcategory].&[19])
)
on 1
from [Adventure Works]

What it's doing is using the nonempty function to return a list of Customers who bought products in subcategory 1, and then using another nonempty function to filter that list by those who bought products from subcategory 19. This, I think, will be more efficient than using the Intersect function although for the record here's the same query rewritten to use Intersect:

select {[Measures].[Internet Sales Amount]} on 0,
intersect(
nonempty(
[Customer].[Customer].[Customer].members,
([Measures].[Internet Sales Amount], [Product].[Subcategory].&[1])
)
,nonempty(
[Customer].[Customer].[Customer].members,
([Measures].[Internet Sales Amount],[Product].[Subcategory].&[19])
)
)
on 1
from [Adventure Works]

HTH,

Chris

|||

Hi Chris,

Thank you very much. It is working perfectly.

Vijay

|||

Hi Everyone,

We are facing some problem in the cube particularly in INTERSECT function.

Here are the details.

Dimension Tables:

DimTime 200601

200602

DimProduct 01

02

03

DimUser 101

102

FactTables:

FactPlayer

Time Product User

200601 01 101

200601 02 101

200601 01 102

200601 03 101

Transact SQL Query:

Select Count(*) from

(

Select user from FactPlayer where productId = 01

INTERSECT

Select user from FactPlayer where productId= 02

INTERSECT

Select user from FactPlayer where productId= 03

)

PlayerCount

RESULT: 1 (UserId: 101)

We want the same result from MDX query. Can you please guide us how to do it by using INTERSECT.

Expecting your valuable reply.

Regards

Vijay

|||

I found the solution below.

SELECT NON EMPTY{[Measures].[User ID Distinct Count]} ON COLUMNS,

INTERSECT

(

NONEMPTY

(

INTERSECT

(

NONEMPTY

(

[DIM USER].[DIM USER].CHILDREN,

([Dim Time].[TimeKey].&[200602],

[Measures].[User ID Distinct Count],

[DIM PRODUCT].[DIM PRODUCT].&[3])

),

NONEMPTY

(

[DIM USER].[DIM USER].CHILDREN,

([Dim Time].[TimeKey].&[200602],

[Measures].[User ID Distinct Count],

[DIM PRODUCT].[DIM PRODUCT].&[11])

)

)

),

NONEMPTY

(

[DIM USER].[DIM USER].CHILDREN,

([Dim Time].[TimeKey].&[200602],

[Measures].[User ID Distinct Count],

[DIM PRODUCT].[DIM PRODUCT].&[12])

)

)

ON ROWS

FROM [DSV KPI]

Please reply me if there any changes in the query.

Thank You

Vijay