Saturday, April 2, 2022

Automated Processing of cubes and dimensions

Automated Processing of cubes and dimensions

Hi everyone,

I am looking for some advice on how to automate the creation and processing of partitions (by day) in SSAS 2005.  I am considering going with SSIS and AMO scripts, however I have found debugging them to be problematic, and very slow.  They also stop responding when the job is cancelled, and the service needs to be restarted.  I liked the simplistic setup of Sql Agent & XML/A script tasks, however I'm not too sure how they are performance-wise, and how they could incorporate variables & configuration settings.

Any suggestions or examples on how everyone is doing this in real life would be great.  Project REAL seems to use XML/A, with a vb.net script passing the XMLA in a string variable back to an AS processing task.  It sounds rather convoluted if you could just use AMO in the script itself.

Also, Is there a way to encapsulate XML/A, MDX, or AMO in a stored procedure? 

Connecting to MSAS 2005 from SSIS 2005 64 bit

Hi all:

Are there any known issues while creating connections from Integration Services in the 64-bit environment or am I missing something ?

Cheers.

 

SQL2005 AS & MBS Axapta

Error: The connection string to repository needs to be specified in the 9.0 server properties (see <DSO>...</DSO> section in msmdsrv.ini file from Analysis Services 9.0).
compatibility?

Need help In Decision Trees

Hello ,

I am doing some analysis on Video Rental System so find the recency,frequency factor of the customers.Basically about the behaviour of the customers.

I have three tables FactVideoRental , DemVideo , DemCustomer. So want to make decision tree to see the results.But i am not able to go to depth of trees only up level 2.

If someone did any similar project kindly share your thoughts or you can email me on alirazvi@gmail.com

 

Regards

ali

test - ignore

test

code for the KPIs

Hi All

can anybody please tell me how to code KPI  and deploy tht on reprts

thanks in advance

rajnish

 

Transactions in NS API (Found a way to do it)

As the topic says I found a way to give notification services API the ability to do transactions.

This is accomplished by using DTC transactions. Given that the API just boils down to some SQL call and SQL is DTC aware it will take advantage of the current transaction.

Thanks

Wayne Sepega

"Promoting" Fields to Attributes

Hi,

I'm currently working through the Project Real documents... In the Analysis Services Technical Drilldown http://www.microsoft.com/technet/prodtechnol/sql/2005/realastd.mspx they describe the process of how to "promote" a field to an attribute.

The technical description is not very interesting for me. What really scares are the "guidelines" when to promote a field to an attribute. They talk about fields "never to be used in analysis". Well, if the fields are "useless", why are they in the database? If they are useful why will they never be used for "analysis"? What does "analysis" mean?

The concept of the UDM defines the UDM as the single source of information for all reporting and analysis in an organization. So why (to come back to Project Real) is an EAN code something to be excluded from the UDM? Because you'll never do "analysis" on that? But I'm sure that an EAN code is essential for lots of reports. So I really don't understand why the EAN code should be excluded from the cube.

So where is the problem? Does't Analysis Services 2005 deliver what it promisses? Didn't I understand the UDM correctly? Are the recommendations from Project Real still based on a very "conservative" (SQL 2000 minded) thinking? Or is the UDM nothing more than "marketing"?

Error Deploying SSAS Cube

I'm trying to deploy Adventure Works DW Cube and I'm getting the following Error:

Error -1056833523 : File system error: Error opening file; \\?\C:\Program\Microsoft SQL Server\MSSQL.1\OLAP\Data\Adventure Works DW Standard Edition.2.db\Adventure Works DW.0.cub\Fact Internet Sales 1.19.det.xml is not a disk file or file is not accessible.

The file changes sometimes when I try again butthe error is the same

 

//matbac

 Problem solved:

It was the Norman anti-virus that caused the problem.

Selecting the Current Date or yesterday or previous Year, month, quarter or prevous whatever level in the time dimension ?

Hello

Does there exist an intelligent time dimension which indicates eg the current date (, year, quarter, month, day), or the previous date (, year, month, quarter, day) ?

I could make a processing-date dimension which is based on the GetDate function of TSQL (sqlserver) ! because we daily do processing !?

Actually, we try to solve this issue in scorecard, maybe scorecard is intelligent enough to select the current or previous date in our time-dimension ?!

Kind regards

Problems when Oracle running on same machine

I was deploying my Report Model in Visual Studio but got an error that it couldnt connect to my Report Server while it was running.

When i typed the address of my ReportServer I got an error, then when I typed the name of the localhost  got an Oracle screen. So then I decided to turn off all the Oracle things running on the machine. Then when I tried to deploy again it worked and I could go to my ReportServer without problems.


Reply:
I assume you aren't trying to build a Report Model on top of Oracle, it just happens to be running. The only thing I can think is that Oracle is taking over the HTTP port configured for Reporting Services.

------------------------------------
Reply:
I think this is the reason as well, I just wanted to point this out to other users that they should look at this when they cannot connect to their ReportServer when Oracle is running. Is there a way that I can give preference to Microsoft instead of Oracle ?

------------------------------------
Reply:
I'm not very familiar with Oracle's HTTP stack. You don't mention which version of Oracle you are using but 10g comes with a modified version of Apache called OHS. I think there are configurations where you can tell it to listen on a port not managed by IIS.

------------------------------------

Sometimes I Get Errors When Processing Cube

Sometimes I will get errors when processing my AS2005 cube.  After some time passes, it processes successfully. 

I usually process the cube when there is new data available in the database, like this morning when I processed the cube essentially minutes after being told that there was new data available.  Would this have anything to do with it?

What if a user was connected to the cube within Excel and was in the middle of a query or constructing a report when I processed the cube?  Would that cause a failure in processing?

Unfortunately, I don't recall the errors I see.  I just know that after some time, I can process without any issues.

Finally, what are some best practices for processing a cube to account for scenarios that could cause failures (e.g. user connected to cube)?

Dimension Access for Roles in AS 2005

When creating a role in AS 2005 and specifying dimenison access (on the Dimensions tab of the role definition in BI Dev Studio), why do some dimensions have the option of "None" as access level while others only have "Read" and "Read/Write"?

For example, in looking at the Adventure Works sample solution for AS 2005, if you add a role and then go to the Dimensions tab of the role definition, you'll see that Account, Department, Organization, and Scenario only have "Read" and "Read/Write" as access options.  All the others have those two as well as "None".

At first, I thought that it was because of some unique setting for those four dimensions.  But I haven't found anything just by browsing their definitions.  Then I thought it was perhaps due to some unique usage of those four dimensions within the cube design.  They are all used with the Financial Reporting measure group, but so is the Date dimension and it has "None" as an access option.

Anyone have any ideas?  Probably something simple that I'm just overlooking, but I'd like to know why I can't set any of these to "None".

Thanks!

Dave Fackler


Reply:
Did you ever find an answer to this?  I'm having the same issue. 

------------------------------------

SSAS Dimension Access

Is there anyway to deny access to an entire dimension in AS 2005?  If I create a role, and go to the dimension tab, the only available options in the "Access" column is "Read" and "Read/Write".

I'm using Adventure Works DW and the BI Development Studio.

 

 

SQL Server Service Broker Admin Tool for SQL Server 2005 RTM

Back in July I released the (then) latest version of SsbAdmin. For you who don'r know what it is; it's a tool which allows you to graphically administer SQL Server Service Broker.

I have now uploaded a new version which is compiled against the released versions of SQL Server 2005 and .NET 2.0.

You can download it from here: [0].

After download, un-zip to some directory and read the README.doc file.

[0]: http://staff.develop.com/nielsb/code/ssbadmin.zip


Reply:
Do you plan to give out the source as well? I could give a shot at adding Remus' import/export service listing scripts to that.

------------------------------------
Reply:
Hey Rushi,

I actually do have a half-working implementation of the import/export service listing. I'll send you the source for that when I'm back home (I don't have that source here).

Niels

------------------------------------

Interesting Behavior of Service Broker Queues

Hello All:

I've been experimenting with the new SQL Server Service Broker, and I think I've discovered some interesting behavior.  Service Broker relies on "Queues" to store messages that need to be processed.  Service Broker operates by sending a message from one Queue (the INITIATOR Quque) to another Queue (the TARGET Queue).  A Queue can have an "Activation Stored Procedure" associated with it.  This procedure is what actually processes the messages in the Queue. 

The first behavior I obeserved related to the setting of a Queue's RETENTION parameter.  The RETENTION parameter indicates whether or not the Queue will retain a copy of the messages it receives.  By default, the parameter's value is "OFF" (meaning it will not retain messages).  In the Activation Stored Procedure of my TARGET Queue, I used "sp_send_dbmail" to send an e-mail message.  I wanted to capture the "conversation_handle" (a uniqueidentifier that identifies a particular message) and include it in the body of the e-mail.  I was unable to capture it, because the Queue's RETENTION parameter was "OFF".  When I tried to capture the conversation_handle from the INITIATOR queue (whose RETENTION parameter was "ON"), I was successful.  The moral of the story is you apparently need to have RETENTION = "ON" if you need to capture information from a Queue.

The second behavior I observed relates to the setting of a Queue's MAX_QUEUE_READERS setting.  This setting allows you to automatically invoke multiple instances of the Activation Stored Procedure.  As messages come into the Queue, the Queue creates an additional instance of the Activation Stored Procedure up to the number you specified in the MAX_QUEUE_READERS setting.  This allows parallel processing of messages in the Queue.  There is also a programming technique called a "RECEIVE LOOP" which is used for processing messages.  In the RECEIVE LOOP, you have a parameter called WAITFOR which tells the Queue whether it should stay on constant alert for new messages or whether it should time out after a specified number of seconds.

If you have the Queue wait indefinitely (by not specifying a TIMEOUT value in the WAITFOR statement) and you have invoked multiple copies of the Activation Stored Procedure, the procedure will remain in memory indefinitely.  Therefore, if you make a change to the code of the Activation Stored Procedure, the change will NOT be reflected in the Activation Stored Procedure until you change the STATUS of the Queue.  I had changed my procedure so that it would not send an e-mail, but the e-mails kept coming.  The e-mails did not stop until I executed an ALTER QUEUE statement.  I ran "ALTER QUEUE queue_name WITH STATUS = OFF;" and then I ran "ALTER QUEUE queue_name WITH STATUS = ON;"  After that, the changes were reflected in the procedure.

Be aware of this behavior as you design your Queues.


Reply:

"The moral of the story is you apparently need to have RETENTION = ON if you need to capture information from a Queue."

I wrong about this.  The reason I wasn't capturing the information the first time around was because I didn't alter the queue to reset the STATUS.  I have since been able to turn off RETENTION while having the ability to capture information from the queue.


------------------------------------
Reply:

Ken, some necessary corrections:

- conversation_handle identifies conversations endpoints, not messages. Initiator and target have distinct conversation_handles. Conversation_id is identifies conversations and is shared by initiator and target. A message is unique by conversation_handle AND message sequence number.

- RETENTION has nothing to do with the capability to 'capture' conversation handles. To obtain the the conversation handle on the initiator side one must get it from the output of BEGIN DIALOG. To obtain the conversation handle on the target side one must put the conversation_handle into the RECEIVE projection list. RETENTION is totally unrelated to this, it is intended for helping application implement compensation logic on failed dialogs. RETENTION keeps a copy of sent and received messages for the duration of the conversation. These messages are visible for the SELECT statement, but not for the RECEIVE statement.

- any stored proc that has an infinite loop, once launched, will stay in memory until killed (explicitly or implicitly) or until server shutdown. This is not specific to activation, is general SQL Server behavior. The only difference is that when this happens by invocation from a user connection  it is usually killed implicitly, by that connection being closed or by the batch being aborted. Turning the queue OFF will cause the infinite WAITFOR(RECEIVE...) in the procedure to error and thus exit the RECEIVE loop. One could just as easily KILL the procedure (lookup the SPID in sys.dm_broker_activated_tasks). Needless to say, activated procedures with infinite loops are a very bad practice. It leads to unnecessary consumtion of server resources (threads at the very least) when there are not messages to receive.

- altering the code of an stored proc does not affect the exisiting running instances of that stored proc. This behavior is general in SQL Server, not particular to activated stored procs. Only new invocations of the stored proc will detect the change.

I hope this clarifies some of the behavior you observed.
HTH,

~ Remus


------------------------------------
Reply:

Thanks for the clarifications.  I wasn't thinking of the RECEIVE LOOP as an infinite loop, since you don't see the timer on the Query Editor window keep running, but it obviously is one.  I also wasn't clear about the circumstances under which a stored procedure stays in memory, so thanks for that information as well.

Now I want to show you a code sample to clarify what I am doing.  Is this what you mean by "put the conversation_handle into the RECEIVE projection list"?

<code>

USE ServiceBrokerTest

GO

/*

DROP PROCEDURE usp_FTPService

*/

ALTER PROCEDURE usp_FTPService

AS

BEGIN

DECLARE @conversationHandle UNIQUEIDENTIFIER

DECLARE @messageBody NVARCHAR(MAX)

DECLARE @messageTypeName SYSNAME

DECLARE @eMailBody NVARCHAR(MAX)

WHILE (1 = 1)

BEGIN

BEGIN TRANSACTION

WAITFOR (

RECEIVE @messageTypeName=message_type_name,

@conversationHandle=conversation_handle,

@messageBody=message_body

FROM [FTPQueue])

-- If a message was received, process it, else skip

IF (@@rowcount <= 0)

BREAK;

-- Process ObjectFTPStartRequest message

IF @messageTypeName = N'ObjectFTPStartRequest'

BEGIN

SET @eMailBody = 'FTP job for Object conversation ' + CAST(@conversationHandle AS NVARCHAR(MAX)) + ' completed'

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Ken',

@recipients = 'username@domain.com',

@subject = 'FTP Service Response (ASP)',

@body = @eMailBody;

SEND ON CONVERSATION @conversationHandle

MESSAGE TYPE [LookupRaceFTPStartResponse]

(N'<ObjectFTPStartResponse>

FTP job for Object completed

</ObjectFTPStartResponse>')

END CONVERSATION @conversationHandle

END

COMMIT

END

COMMIT

END

</code>

If that's what you meant, then that's what I was doing.  The reason I was having a problem was solely due to the fact that the first version of the procedure was still running in memory. 


------------------------------------
Reply:

To put the conversation_handle into the projection list it simply means to have the conversation_handle in the list of columns you RECEIVE. You are doing this fine.

The code you posted needs two changes:
- the WAITFOR(RECEIVE ...) must have a timeout. Typically, the value should be a few seconds. The ideea is that the procedure should linger a bit, in hope a new message arrives and it can be processed. Too short interval and the procedure is gonna miss new messages, exiting and being re-launched, which is a somewhat expensive operation. Too long and your procedure is going to consume server resources w/o doing anything.
- You must add a TOP(1) clause to the RECEIVE. Otherwise you will receive multiple messages, but only process the last one.

HTH,

~ Remus


------------------------------------
Reply:

I don't think it's proper to say the TIMEOUT is required since the T-SQL language does not require a TIMEOUT with the WAITFOR clause.  If it were required I would get a syntax or runtime error upon execution of the code.

As for whether it's a good idea to use a TIMEOUT, here's what Roger Wolter (Microsoft's Group Program Manager for SQL Server Service Broker) says in his book "The Rational Guide to SQL Server 2005 Service Broker" (Page 56):

"A WAITFOR with no timeout means the RECEIVE will wait for a message no matter how long it takes.  Waiting for a message to appear on the queue is generally more efficient than polling the queue by using the RECEIVE command periodically.  The main exception to this would be a low priority queue that is only polled for messages periodically when higher priority queues are empty."

As for the use of the TOP clause, I quote Roger again (again on Page 56):

"Retreiving all the messages available utilizes fewer server resources than multiple RECEIVE commands, so you should avoid the TOP clause whenever possible.  The main reason to use the TOP clause is in stored procedures where receiving one message at a time into TSQL variables simplifies programming, or when there's a chance that a RECEIVE command will retrieve more messages than you want to process in a single transaction."


------------------------------------
Reply:

Indeed, the TIMEOUT is not syntactically required. But no TIMEOUT in activated stored proc loop practicaly turns it into an infinite loop and you'll run into the problems you encountered.

Assignment in RECEIVE projection list (e.g. @conversation_handle = conversation_hanlde)  requires a TOP(1). It is true that using the TOP clause is less efficient, but otherwise the procedure gets significantly more complex. You'd have to RECEIVE INTO @tableVariable, open a cursor over the table variable and scan this cursor. That is what the "simplifies programming" part of he's quote reffers to. You either use a simple T-SQL program (i.e. @variables), but pay the penalty of the TOP clause, or you use a more complex T-SQL program (i.e. @tablevariable and cursor). Roger himself mentions this, see the two examples of RECEIVE on page 57.
Note that if 99% of your RECEIVEs would return only one message anyway (as is often the case), then the TOP is not expensive at all.

See the Receive Loop example on page 63, you'll see that Roger uses both a TOP(1) clause and a 5s TIMEOUT.

HTH,
~ Remus


------------------------------------

CREATE SUBCUBE or FILETR function

Hi All,
Some times it possible to have same result in MDX either by using CREATE SUBCUBE or by using FILETR function. Which method is better?

Thanx a ton for any advice :)

Rakesh

 

 

The tree hierarchy interface in cube browser

Hi,

  When browsing cube in SQL Server Management Studio, there is a tree hierarchy which lists measures and dimensions at left side.

  How could I embed this tree hierarchy interface to my application?

thanks,

New release of SqlClrProject

I thought I better post before this year ends. :-)

Anyway, you may know that I have developed a MSBUILD task dll for deployment of SQLCLR assemblies. I also created some templates for use from with Visual Studio 2005, the SQLCLRProject.

I have re-created this as an Add-In for VS, so you now have the ability to automatically deploy from inside of VS as well as debug! I have also created a GUI front-end for those who don't use VS which allows deploying of assemblies using this front-end (setting properties etc). So what exactly does all this do:

The SqlClrProject is a project type for Visual Studio 2005 for creation and deployment of assemblies to SQL Server 2005. It consists of templates for both Visual C# (C#) and Visual Basic (VB) with skeleton code for creation of CLR methods to be used as stored procedures, User Defined Functions (UDF's), triggers, User Defined Types (UDT's) and User Defined Aggregates (UDA's). Some of the features are:

  • Ability to deploy the assembly to SQL Server and create the SQL Server methods, all from inside the Visual Studio IDE.
  • Automatic creation of T-SQL deployment scripts.
  • Automatic creation of T-SQL DML scripts for testing of the created objects.
  • Ability to alter an assembly and only deploy newly added methods from the assembly.
  • Ability to create objects in a non default schema "schemaname.objectname".
  • Debug facilities from inside VS.

For you who have used the SQL Server Project project type from VS to do this, you may wonder what the differences are. Things that the SqlClrProject allows you to do, which the VS SQL Server project can't do:

  • Automatic creation of T-SQL deployment scripts.
  • Automatic creation of T-SQL DML scripts for testing of the created objects.
  • Ability to alter an assembly and only deploy newly added methods from the assembly.
  • Ability to create objects in a non default schema "schemaname.objectname".

Anyway, if you are interested you can download all needed from here: [0].

Un-zip the file and read the README.txt file in the root directory.

Comments are as usual very welcome!!!

Finally I wish you all a very Happy New Year!!!

Niels

[0]: http://staff.develop.com/nielsb/code/DeployPropAndAddIn.zip

Welcome everyone

We wanted to welcome you to this new forum and encourage you to post questions and/or post answers.

Reply:

Yo Janey,

Having fun with SQL Server 2005 and Express <not>! Pulled out most of my hair, now down to fingernails. Have the developers call me and I'll be happy to advise them on design and help threads.

Luv

Dad


------------------------------------
at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Setup is Split Across Multiple CDs

Setup is Split Across Multiple CDs Lately I've seen a bunch of people hitting installation errors that have to do with the fact th...