How to call the processing of a cube ?

I need to automatically process a cube. Do I have to use an Analysis Managment Object (AMO) ?

Otherwise, I know that it is possible to create a Sql Agent Job to run periodically, but I don't want to schedule this task. In fact, I need to "call" the processing of my cube, wich will run between other jobs. The "caller" is a Visual Basic 6 program from wich I am able to launch .exe files.

thanks

Hello. I am not sure if you are taking about SSSAS2000 or SSAS2005?

Anyway, you can make a dts-package in SQL Server 2000 or a SSIS-package in BI-Dev Studio that process the dimensions and the cubes.

These packages are possible to call by a VB-application but I think you must use VB6 for dts and VB.Net 2005 for SSIS(Integration services).

HTH

Thomas Ivarsson

|||

Hi. You can also use the command prompt utility "DTEXEC" from VB6 to call a package developed with SSIS if you have to. However, Thomas' suggestion of using VB .NET 2005 is best. Here's more information on the DTEXEC utility.

http://msdn2.microsoft.com/en-us/library/ms143706.aspx

Paul Goldy

|||

Here are a couple of articles I published with step-by-step setup for the DTS / SSIS scenarios involved:

For SSAS 2k (DTS):

http://www.databasejournal.com/features/mssql/article.php/3503201

For SSAS 2k5 (SSIS):

http://www.databasejournal.com/features/mssql/article.php/3584306

Good Luck!

Bill

William E. Pearson III
CPA, CMA, CIA, MCSE, MCDBA
Island Technologies Inc.
931 Monroe Drive
Suite 102-321
Atlanta, GA 30308

404.872.5972 Office
wep3@.islandtechnologies.com
wep3@.msas-architect.com

www.msas-architect.com
-- -- --

Publisher Sites:

http://www.databasejournal.com/article.php/1459531

http://www.sql-server-performance.com/bill_pearson.asp

http://www.informit.com/authors/bio.asp?a=862acd62-4662-49ae-879d-541c8b4d656f

http://www.2000trainers.com/section.aspx?sectionID=17

|||

Thanks ! What I need is in the first article link you posted, Bill. I will create a DTS package.

The problem is, I can't do the step #9 on page 3. I don't have the Analysis Services Processing Task ICON in the TASKS TOOLBAR ... ?

If that can helps, I work with : SQL Server Enterprise Manager version 8.0
Analysis Manager version 5
Visual Basic 6 (the program that calls the DTS package)

So, what do you think ?

PS - I found how to call the package with VB6 on this page :
http://www.sqlservercentral.com/columnists/bknight/executingpackagefromvisualbasic.asp

EDIT : I found on the Microsoft web site that the Analysis Processing task is available only when SQL Server 2000 Analysis Services is installed.
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/dtssql2k.mspx

How to calculate the percentage?

In my cube, I've a date dimension and a time dimension. I would like to know how can I calculate the percentage of order count for a specific time?

I can get this.

Hour11/6/0611/7/069am601010am801011am6020

But how can I get this.

Hour11/6/0611/7/06

9am30%25%

10am40%25%

11am30%50%

I would like to use the calculation feature in available in the cube. How can I do this? Thanks!

Depends on whether the percentage is always based on the time hierarchy, regardless of which query axis it lies on, or is based on whichever hierarchy is on rows - Axis(1). In the former case, something like:

Member [Measures].[OrderFractionByTime] as

'[Measures].[Order Count]/

([Measures].[Order Count], [TimeOfDay].Parent)',

FORMAT_STRING = "Percent"

|||Hi Deepak,

Thanks for your reply but there are something that I don't understand. In your code, you use [TimeOfDay].Parent in the member calculation. However, in my own cube, the date dimension and time dimension are 2 separate dimensions. So, I don't expect my TimeOfDay.Parent will get the expected set.

The following is my Date and Time dimensions structure.
DimDate - The Date dimension is populated by extracting data from my OLTP.
Year
Month
DayOfMonth
Date
DayNameOfWeek

DimTime - The Time dimension is generated by cross join all the available hour, minute and second. (No. of records: 24 x 60 x 60)
Hour
Minute
Second

P.S. I'm quite new to BI and datawarehouse. If my Time hierarchy is not correct or using best practice, please let me know so that I can improve it.

Regards,
Alex|||

Hi Alex,

Are you using AS 2000 or AS 2005 - if it's AS 2000, then something like:

Member [Measures].[OrderFractionByTime] as

'[Measures].[Order Count]/

([Measures].[Order Count], [DimTime].Parent)',

FORMAT_STRING = "Percent"

|||Hi Deepak,

I'm using AS 2005. The problem that I don't understand what [DimTime].Parent is pointing to. The Time dimension and Date dimension doesn't have any direct relationship in my cube. Is there any design fault?

Regards,
Alex|||

Alex,

With AS 2005, the hierarchy should also be specified with [DimTime], so [DimTime].[TimeHierarchy].Parent points to the parent of the current [DimTime] member. For example, the parent of the hour "01" will be [DimTime].[TimeHierarchy].[All].

http://msdn2.microsoft.com/en-us/library/ms145513.aspx

>>

SQL Server 2005 Books Online

Parent (MDX)

Updated: 17 July 2006

Returns the parent of a member.

...

>>

How to calculate the percentage?

In my cube, I've a date dimension and a time dimension. I would like to know how can I calculate the percentage of order count for a specific time?

I can get this.

Hour11/6/0611/7/069am601010am801011am6020

But how can I get this.

Hour11/6/0611/7/06

9am30%25%

10am40%25%

11am30%50%

I would like to use the calculation feature in available in the cube. How can I do this? Thanks!

Depends on whether the percentage is always based on the time hierarchy, regardless of which query axis it lies on, or is based on whichever hierarchy is on rows - Axis(1). In the former case, something like:

Member [Measures].[OrderFractionByTime] as

'[Measures].[Order Count]/

([Measures].[Order Count], [TimeOfDay].Parent)',

FORMAT_STRING = "Percent"

|||Hi Deepak,

Thanks for your reply but there are something that I don't understand. In your code, you use [TimeOfDay].Parent in the member calculation. However, in my own cube, the date dimension and time dimension are 2 separate dimensions. So, I don't expect my TimeOfDay.Parent will get the expected set.

The following is my Date and Time dimensions structure.
DimDate - The Date dimension is populated by extracting data from my OLTP.
Year
Month
DayOfMonth
Date
DayNameOfWeek

DimTime - The Time dimension is generated by cross join all the available hour, minute and second. (No. of records: 24 x 60 x 60)
Hour
Minute
Second

P.S. I'm quite new to BI and datawarehouse. If my Time hierarchy is not correct or using best practice, please let me know so that I can improve it.

Regards,
Alex|||

Hi Alex,

Are you using AS 2000 or AS 2005 - if it's AS 2000, then something like:

Member [Measures].[OrderFractionByTime] as

'[Measures].[Order Count]/

([Measures].[Order Count], [DimTime].Parent)',

FORMAT_STRING = "Percent"

|||Hi Deepak,

I'm using AS 2005. The problem that I don't understand what [DimTime].Parent is pointing to. The Time dimension and Date dimension doesn't have any direct relationship in my cube. Is there any design fault?

Regards,
Alex|||

Alex,

With AS 2005, the hierarchy should also be specified with [DimTime], so [DimTime].[TimeHierarchy].Parent points to the parent of the current [DimTime] member. For example, the parent of the hour "01" will be [DimTime].[TimeHierarchy].[All].

http://msdn2.microsoft.com/en-us/library/ms145513.aspx

>>

SQL Server 2005 Books Online

Parent (MDX)

Updated: 17 July 2006

Returns the parent of a member.

...

>>

How to caculate result throw time range using the cubes?

How can I calcualte the sales value from 1/15/2005 to 4/23/2005 Using Sales Cube?

Use calculated member with a sum function. The set in the sum function should be specified as time range between members of Time (or Date). You can easily do the range as "member1 : member2", like in this example:

select [Date].[Date].&[1] : [Date].[Date].&[271] on 0 from [Adventure Works]

how to build share dimension

in a project ,i built two cube,each cube need time dimension,i want the time dimension be a share dimension.how to do it?

What version of Analysis Services are you using?

In AS 2005 open your project in BI Dev Studio. Open your cube. Right click in the dimensions tab and select "Add cube dimensions".

Make sure you go into Dimension usage tab later and specify how your dimension is assosiated with the measure group.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

How to build AS model in report builder?

Is anyone can figure out that how many ways to use AS cube as the datasouce
of report builder?
One of the way that I knew is use SQL Server Management Studio to create AS
Model for report builder.
So, can I use report designer to create?
I'm so confused on report designer for create data source views when using
Cubes.
Data source view wizard just can see relational data source and can't select
AS cube?
Thanks for any advice!
AngiAS-based models can only be generated from Management Studio or Report
Manager
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Angi" <enchiw@.msn.com> wrote in message
news:O3B4yA9AGHA.608@.TK2MSFTNGP12.phx.gbl...
> Is anyone can figure out that how many ways to use AS cube as the
> datasouce of report builder?
> One of the way that I knew is use SQL Server Management Studio to create
> AS Model for report builder.
> So, can I use report designer to create?
> I'm so confused on report designer for create data source views when using
> Cubes.
> Data source view wizard just can see relational data source and can't
> select AS cube?
> Thanks for any advice!
> Angi
>

How to build a report from a cube in SSRS 2005

Hi, All

I am trying to create a report from analysis service cube but I am facing some problems.

While creating new dataset in report designer, MDX query designer is not displaying while giving Sql Server Analysis Service in Data source.

Please give the details of creating reports using cube.

When you look at the data source, what is it's type? It should be "Microsoft SQL Server Analysis Services" and not "OLEDB" if you want a multidimensional view of the data.

How to build a 10 best topcount...?

I'm beginer. I want to build a 10 best brands unit sales in (Foodmart database). where do I start?(From sales cube or shared dimension), pls experts told how to do it?

Use Topcount MDX function.

For example

SELECT TopCount
({[Geography].[Geography].[City].Members
*[Date].[Fiscal].[Fiscal Year].[FY 2003]}
, 5
, [Measures].[Reseller Sales Amount]
) ON 0,
[Product].[Product Categories].Bikes ON 1
FROM [Adventure Works]

See some more

http://msdn2.microsoft.com/en-us/library/ms144792.aspx

http://cwebbbi.spaces.live.com/PersonalSpace.aspx?_c11_blogpart_blogpart=blogview&_c=blogpart&partqs=amonth%3D5%26ayear%3D2005

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

How to build a 10 best topcount...?

I'm beginer. I want to build a 10 best brands unit sales in (Foodmart database). where do I start?(From sales cube or shared dimension), pls experts told how to do it?

Use Topcount MDX function.

For example

SELECT TopCount
({[Geography].[Geography].[City].Members
*[Date].[Fiscal].[Fiscal Year].[FY 2003]}
, 5
, [Measures].[Reseller Sales Amount]
) ON 0,
[Product].[Product Categories].Bikes ON 1
FROM [Adventure Works]

See some more

http://msdn2.microsoft.com/en-us/library/ms144792.aspx

http://cwebbbi.spaces.live.com/PersonalSpace.aspx?_c11_blogpart_blogpart=blogview&_c=blogpart&partqs=amonth%3D5%26ayear%3D2005

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

How to browse cube in Excel?

Hi, all here,

Could anyone tell me how can I browse cube data in Excel?

Thanks a lot in advance for any guidance and help.

There are two options I know of.

One is Microsofts Free giveaway Data Access Tool. Only works with Excel 2002 onwards I think, get it at http://www.microsoft.com/downloads/details.aspx?FamilyId=DAE82128-9F21-475D-88A4-4B6E6C069FF0&displaylang=en home page for product is at http://www.microsoft.com/office/solutions/accelerators/exceladdin/default.mspx

Another is XLCubed www.xlcubed.com which is similar but far more powerful and friendly. This works with Excel 2000 SP2 onwards

Most of the Big front end tools like Cognos and proclarity have Excel plugins but they tend to be fairly expensive. XL3 is about ￡300 a seat and they do 30 day trial versions. We had four or five trials in a row before we settled on it as a final solution.

The MS tool is useable but not for anything too complicated. Both are really easy to pick up if your Excel users know anything about pivot tables.

regards

Steve

|||Hi, Steve, thanks a lot.|||

Hi,

I have downloaded and installed the add-in. When I try to connect to the database I get an error with Initialization of the data source failed.

Can one one help me on this?

cheers

Josh

How to browse cube in Excel?

Hi, all here,

Could anyone tell me how can I browse cube data in Excel?

Thanks a lot in advance for any guidance and help.

There are two options I know of.

One is Microsofts Free giveaway Data Access Tool. Only works with Excel 2002 onwards I think, get it at http://www.microsoft.com/downloads/details.aspx?FamilyId=DAE82128-9F21-475D-88A4-4B6E6C069FF0&displaylang=en home page for product is at http://www.microsoft.com/office/solutions/accelerators/exceladdin/default.mspx

Another is XLCubed www.xlcubed.com which is similar but far more powerful and friendly. This works with Excel 2000 SP2 onwards

Most of the Big front end tools like Cognos and proclarity have Excel plugins but they tend to be fairly expensive. XL3 is about ￡300 a seat and they do 30 day trial versions. We had four or five trials in a row before we settled on it as a final solution.

The MS tool is useable but not for anything too complicated. Both are really easy to pick up if your Excel users know anything about pivot tables.

regards

Steve

|||Hi, Steve, thanks a lot.|||

Hi,

I have downloaded and installed the add-in. When I try to connect to the database I get an error with Initialization of the data source failed.

Can one one help me on this?

cheers

Josh

How to browse a cube from another project?

Hi, all here,

I have question about how can I browse cube from another project in my current project then?

You can use SQL Server Management Studio to broswe any cube on your server.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.