Saturday, April 2, 2022

help on a business case related to status reporting

help on a business case related to status reporting

Hello,

I am a newbie in BI and would appreciate your help on the following case:

I have a customer dimension being a slowly changing dimension and I have fact Subscriptions storing the subscriptions of customers.

The customer dimension has 

- CustKey

- CustAltKey (Being the Business Key of SCD)

- CustName

The subscription Fact has:

- CustKey

- Inception Date  (linked to Time Dimension)

- Expiry Date (linked to Time Dimension)

- Subscription Amount (measure)

- Is Active (Named Calculation, True when subscription not expired yet)

1) I want to be able to report on Active v.s. Non Active Customers, where an active customer is someone who still has at least one subscription whose Expiry Date is > today's date. I was wondering if this can be done using a Named Calculation on the Customer Dimension, if yes how can I query from subscription fact in the named calculation expression? Or maybe it's the wrong way.

2) I also want to be able to categorize on this status of Customers all through the reports. Let's say I want to be able to find the number of customers I lost this year v.s. the number of customers I lost last year. I think here an MDX would be required. But I can't see how exactly to get the status according to the input years. Appreciate any guidance on this.

 

Thanks,

Grace

Budget and ForeCast

Hi All,

My tough cases are like that in my virtual cube.  I have

Fin_time, Bugdet_Version Dimension and Sales_Amount measure.

Fin_time has year, quarter and month dimensions. 

I have 14 Budget_version , one is actual data, the other 13 are budget data.  All 14 budget_version have a timeID property, like (200404, 200503, YYYYMM format).

Now I need to get a sales_Amount, when I choose a budget_version,  the number of sales_Amount before the timeid( in budget_version member property) is actual number, and after timeid is budget data.

Thanks

David X

 

 

Executing MDX queries from inside SQL Server Stored Procedures (SQL Server 2005)

 

Hello all,

Does anyone have any idea how to access a cube from stored procedures in SQL Server 2005?

My idea was to use SQLCLR and write a function in .NET that accessed the cube through ADOMD, but there are problems with that.

See the following code sample:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using Microsoft.AnalysisServices.AdomdClient;

public partial class UserDefinedFunctions
{

  [Microsoft.SqlServer.Server.SqlFunction]
  public static SqlString MDXRdr()
  {
    AdomdConnection conn = new AdomdConnection();
    conn.ConnectionString = @"Provider=SQLNCLI.1;Data Source=JOAHSE0\SQL2005;Integrated Security=SSPI;Initial Catalog=dbRenTAK";
   
    conn.Open(); 
    // Just output cube name
    string str = conn.Cubes[0].Name;
    conn.Close();

    return new SqlString(str);
  }

};

First, I wasn't able to add a reference to AdomdClient from Visual Studio. I then did add a reference manually in the project file and it compiles. But when I try to deploy, I get an error message that the "assembly adomdclient was not found in the SQL catalog".

Thanks in advance for any suggestions or hints!

Best Regards,

Johan Åhlén
Consultant, IFS

 


Reply:
Moving to the "Data Mining" Forum, which is better suited for this question.

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

Using Synonyms in query editor bug/feature

I am fairly new to SQL server but I have a lot of Access experience and so far I have been impressed with SQL Server Express (I am soft and I am using the management studio)  How ever I think I have found a feature in the express management studio that may be a bug

I have created a linked server to an Acces database which has been supplied by a client of ours using the following code

EXEC sp_addlinkedserver

@server = N'KEY',

@srvproduct = N'Access',

@provider = N'Microsoft.Jet.OLEDB.4.0',

@datasrc = N'C:\Data\DB.mdb'

GO

First comment this work but in the linked server there is no list of objects in the access data base (or any other linked server I have try) this seems to me to be a bug/very annoying feature.

I can now do queries from the database table of the form

SELECT TOP (10) ID FROM KEY...Tbl_1

However if I try to use the query editor the tables in the linked server aren't listed, this I would say is not a bug just a fact. 

However when I create a synonym of a table in the table in the linked server using

CREATE SYNONYM Syn_1 FOR KEY...Tbl_1

The synonym is created and can be used in queries as

SELECT TOP (10) ID FROM Tbl_1

The Synonym does not appear in the synonyms list in the query editor add tables list. I think that is a bug since surely thats what the synonyms list is for.

The use of a view would get round this I know but that doesn't seem as elegant as the synonyms method, this feature isn't going to stop me in my work since I can get round it.

 

 

 

 

 

 

 

Viewing the edited and original measure values in a write enabled cube

Hi all,

I have a write enabled SSAS cube.

I edited the values of a measure column.

Now when i am querying the cube i am seeing only the changed values

I like to see the original values of the measure column and the changed values simultaneously in a client application like excel pivot table

Is any special MDX query should be used to see the actual cube data

 

All Level Name - How do I change for each attribute?

It looks like if I set the AttributeAllMemberName property at the dimension level, all attributes will inherit this name.

If I have multiple attributes within a dimension, how do I change the 'All' level to read 'All <attribute name>' for each attribute?

 

Programmatically Add Logging to Package

This is a sample for adding logging to a package programmatically.

It's a simple process, but it took some time to find all the pieces required so I wanted to share it for others who can use this in the future to save them the time I spent it. 

 

        _Package.LoggingMode = DTSLoggingMode.Enabled

        Dim Log As LogProvider = _Package.LogProviders.Add("DTS.LogProviderEventLog.1")
        Log.Name = "Windows Event Log Logging Provider"
        Log.Description = "Logs Event info to Windows Event Log"
        _Package.LoggingOptions.SelectedLogProviders.Add(Log)

        Dim LoggingOptions As LoggingOptions = _Package.LoggingOptions
        LoggingOptions.EventFilterKind = DTSEventFilterKind.Inclusion

        Dim Events As String() = {"OnError", "OnTaskFailed", "OnWarning"}
        LoggingOptions.EventFilter = Events

        Dim EventColumnFilter As DTSEventColumnFilter = New DTSEventColumnFilter()
        EventColumnFilter.Computer = True
        EventColumnFilter.Operator = True
        EventColumnFilter.SourceName = True
        EventColumnFilter.SourceID = True
        EventColumnFilter.ExecutionID = True
        EventColumnFilter.MessageText = True
        EventColumnFilter.DataBytes = False

        LoggingOptions.SetColumnFilter("OnError", EventColumnFilter)

 

specified sort order in matrix column

i had a column that had data that needed to be sorted in a specified order.  (Crystal reports has a specified order choice for sort order)

i created a calculated field (on the data tab, then its available to choice from) for the column that i needed to specify the sort order.  if the data was less than and greater than i assigned numbers to it.  for example

1-5 assigned 1, 6-15 assigned 2, 16-30 assigned 3, etc.

then i went to the matrix column (edit group...) and used an expression

iif(calculated column = 1, "1-5", iif(calculated column = 2, "6-15", etc...))

i also added the same expression to the column.

this provided me with my specified sort order that i needed.

good luck, hope this helps you out.

chuck

Opening .sql text files and defaulting to the current connection.

In Sql2K's Query Analyzer, if I have an active query I can open an existing text file without having to specify a Server/User/Password.

In SQL Management Studio I can start a new query (CTRL-N) without having to choose a connection, but if I try opening a file I need to specify a Server/User/Password.

I know I can choose 'Remember Password' -- but it seems like a convenience was taken away.

How to avoid concurrent editing of cube by write back

I have a write enabled cube

Multiple users use it with a client application

But when 2 users try to edit the same cell simultaneously, is it possible to notify one user and allow the other user to edit it.

How can we handle such situations

 

 

Periodical appearence of the data in the cube

Hello!

I have a problem with my cube. The deal is that  I have a table that includes, sum of money that person has to pay, the startdate of the period and the period length in month:

Money     StartPeriod   Length

=======================

50            2006-03-01   3

70            2006-05-21   1

.....            ........                ...

35            2006-12-12    12

What I need is to show it in my cube so if the Length is 3 then this 50$ will appear in the cube every three month, e.g. the next time it appears is 2006-06-01, then 2006-09-01, then 2006-12-01.

Can anyone help me with this? Thank you somuch for any hints!

/NatashaKarlsson  

SQL Server 2005 SP1 Webchat: March 22nd 9:00 am PST

We are be hosting a public web chat discussion on March 22nd 9:00 – 10:00 am PST about the March CTP of SP1.  It is the customers' opportunity to engage with the dev team and provide feedback and ask questions. Strong participation from customers will act as a motivator for stronger participation by the dev team and will increase their interest in having these in the future, so we urge you to join and participate. 

 

For more information and sign up for this web chat go to: http://www.microsoft.com/technet/community/chats/default.mspx#06_0322_TN_SQL. 


Reply:

Would a transcript be available later on for us to review?

Thanks...


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

Problems with writeback and nonemptycrossjoin function in SSAS 2000

Hi,

I am trying to update SSAS 2000 cube data using our application, but I get errors when I attempt to retrieve data from cells affected by the write back Update Cube command.  Our application uses ADOMD.NET 8.0 to retrieve cell sets from the cube.  I only receive an error if the cube I'm writing back to contains MDX using the NONEMPTYCROSSJOIN function.

The following error is received in the ADOMDException when I execute a cell set query:

"Some data has been updated since you began executing this query. - An error
occurred during attempt to get a cell value"

However, if the updated cells are committed to the write back table, the error disappears during a subsequent query for the same cell set.  Is this a known issue?  Are there any remedies aside from upgrading to SSAS 2005?

Thanks!

SQL Express Family CTP Now Available

I'm happy to announce the release to public CTP of the following SQL Express Family products:

SQL Server 2005 Express Editions SP1
SQL Server 2005 Express Edition with Advanced Services
SQL Server 2005 Express Edition Toolkit
SQL Server 2005 Management Studio Express (included as part of Express Advanced & Express Toolkit)

You can download and install these CTPs from the CTP download page. Here is a brief description of each product:

SQL Server Express Edition with SP1
SQL Server 2005 Express Edition (SQL Server Express) is a powerful and reliable data management product that delivers rich features, data protection, and performance for embedded application clients, light Web applications, and local data stores. Designed for easy deployment and rapid prototyping, SQL Server Express is available at no cost, and you are free to redistribute it with applications. If you need more advanced database features, then SQL Server Express can be seamlessly upgraded to more sophisticated versions of SQL Server.

SQL Server Express Edition with Advanced Services
SQL Server 2005 Express Edition with Advanced Services is a new, free version of SQL Server Express that includes additional features for reporting and advanced text based searches. In addition to the features offered in SQL Server 2005 Express Edition, SQL Server Express with Advanced Services offers additional components that include SQL Server Management Studio Express (SSMSE), support for full-text catalogs, and support for viewing reports via report server. SQL Server Express Edition with Advanced Services also includes SP1.

SQL Server Express Edition Toolkit
SQL Server 2005 Express Edition Toolkit (SQL Server Express Toolkit) provides tools and resources to manage SQL Server Express and SQL Server Express Edition with Advanced Services. It also allows creating reports by using SQL Server 2005 Reporting Services (SSRS).

SQL Server Management Studio Express
SQL Server Management Studio Express (SSMSE) provides a graphical management tool for managing SQL Server 2005 Express Edition and SQL Server 2005 Express Edition with Advanced Services instances. SSMSE can also manage relational engine instances created by any edition of SQL Server 2005. SSMSE cannot manage Analysis Services, Integration Services, SQL Server 2005 Mobile Edition, Notification Services, Reporting Services, or SQL Server Agent.

 

Feel free to discuss these releases in the SQL Express Forum.

Report any issues you find in the MSDN Product Feedback Center

Regards,

Mike Wachal
SQL Express team


Reply:

Are there problems with installing these on top of SQL Server 2005 Express? I have:

Microsoft SQL Server Management Studio Express      9.00.1399.00
Microsoft Data Access Components (MDAC)      2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML      2.6 3.0 5.0 6.0
Microsoft Internet Explorer      6.0.2900.2180
Microsoft .NET Framework      2.0.50727.42
Operating System      5.1.2600

but I have already had significant time-wasting problems because I had the Beta Version of SQL Server 2005 installed previously - in the end I wiped my hard drive and re-loaded XP.

It is the Reporting Services I am after - good advice would be appreciated.

 

regards, David Wilson.


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

I would remove the SQL Server Management Studio Express prior to installing the Express Advanced SKU.  You'll also need to install IIS on your machine if you want to install Reporting Services.

-Jeffrey


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

Hi,

Any idea when the full version of SQL Server 2005 express edition is going to be released ?

 

Thank you


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

Haven't seen anyone else with install problems on the SP1, but I have tried to install 3 times. I meet ALL system requirements(although it gives warning about hardware which is wrong). It installs the majority of the product but when it gets to "SQL Server Database Services" it fails with the following:  "SQL Server Setup could not connect to the database service for server configuration. The error was:  [Microsoft][SQL Native Client][SQL Server]Login failed for user 'sa.' Refer to sever error logs....."

The second time I tried changing to SQL Server Authen. with sa and a blank password, but during the install it said I had to have Windows Authen. I changed logon service fro Network Service to Local System on the third attempt and it still failed. Can anyone think of what could be causing this?


------------------------------------
Reply:
I just tried an install and received the same error regarding the sa account.

------------------------------------
Reply:
I have experienced the same problem

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

Me too - on two different PCs.  Any workarounds welcome. 

To clarify, this occurs on the Advanced Services install with all options chosen.  The SP1 install has already succeeded.  I shall try varying the Advanced Services install options.


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

I have got Advanced Services installed now.  Re-reading the Readme file, I found I didn't need to install SP1 before Advanced Services - it is included.  I uninstalled the instance and ran Advanced Services from scratch, with all options selected (except Mgmt Studio Express, as I have the full SQL Server tools already).

So I don't know if the sa login problem was because of installing SP1 separately first, or if it is a problem with upgrading an instance.

The only other issue is that //localhost/ReportServer$SQLExpress complained that NT AUTHORITY\NET WORK SERVICE did not have a log in.  I added it manually and gave it the same permissions as NT AUTHORITY\SYSTEM on ReportServer$SQLExpress and ReportServer$SQLExpressTempDB and it seems fine now.

Hope this helps.

David


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

I'm new to SQL Express so be gentle...

I had the same "minimum hardware" and "sa" installation problem with SQL Advance. I tried several times but always failed with same complaint.

1) tried to upgrade to SQLEXPR_ADV and failed (three tries).

2) successful install of SQLEXPR_TOOLKIT components. ( same (incorrect) hardware and sa complaints)

3) successful upgrade to SQLEXPR SP1. (no complaints)

4) again failed upgrade to SQLEXPR_ADV (same complaints)

5) Followed David's suggestion and removed SQLEXPRESS instance. Installed SQLEXPR_ADV without complaint. Apparently the advance edition will install new but not upgrade previous.

6) Also noticed that other features (e.g. ssmsee, etc ) did not upgrade so had to uninstall then install those too.

HTH somebody

Sturdy


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

OLAP Error Provider ,high access with com+

Hi

In the company when I work, we have some problems when we have many acces to OLAP Services. We have pages in ASP, that rendering olap information, and we access to a SQL 2000 OLAP Server by  COM+ Components in vb 6.0 running in MTS Win 2003, and some times when the quantity of querys increases, a provider error is throwed.

we used this connection string:

 "Initial Catalog=XXXX ;Data Source=XXXX;Provider= MSOLAP

;Execution Location= 1

;Client Cache Size=25

;Large Level Threshold=1000

;Auto Synch Period=10000

;APP=XXXX

Also our components are in a trasaccion mode " Uses Transaccion", other important thing to note  when the errors ocurrs,  the dllhost.exe process increases considerably.

I dont know if this error is caused by OLAP or by the components.

Thank's

JRZ

Cannot create a connection to datasource on Preview pane

Hi All,

I've been fiddling for quite awhile with this one and imagining that perhaps this could help someone else having the same problem...

Using the SQL 2005 BI development tool to create a report over an Analysis Server 2000 cube I started having some intermitent connection issues.

The Data pane connects and runs all MDX statements properly (I can see the results of the query in it), but when I go to the Preview pane I get the following error:

An error has occurred during the local report processing
An error has occurred during the report processing
Cannot create connection to datasource '<my datasource name>'
A connection cannot be made. Ensure the server is running.
No connection could be made because the target machine refused it.

It doesn't run even in the server with admin rights.

The solution came by changing the type of the data connection.

From:
Type: Microsoft SQL Server Analysis Server
Connection String: Data Source=<my Analysis Server name>;Initial Catalog="my cube"

To:
Type: OLE DB
Connection String:
Provider=MSOLAP.2; Data Source=<my Analysis Server name>;Initial Catalog="my cube"

It works, which is the good thing, but it doesn't make much sense to me.

Any thoughts?

Dimension/Hierarchy problem

I have created a CM-1 calculated Measure.  It returns "last month's sales".  It is simply ( [Date].[Month].Prevmember , [Measures].[Sales] )

For various reasons, I have established both a regular and a fiscal calendar for the Time dimension.  After creating and populating it, I eliminated the Fiscal Month because that level is irrelevant.

Anyway, when I have month as my only attribute in the browser, everything works fine...I can see the current month's sales and I can see CM-1, at any point in time.

But, once I drag Fiscal Year into my browser (which I need to do), the CM-1 function stops working at the fringes of the Fiscal Year.  My fiscal year is February; in that month, CM-1 returns NULL, as if it cannot "see beyond" the edge of the fiscal year it now realizes that it belongs to.  That seems really odd, because I know for a fact that there are legitimate values prior to Feburary.  And as I said, if I just drag Fiscal Year back off of my grid, everything opens right up.

At this very moment, I'm pulling out what little hair I have...

Thanks for any help you might be able to provide...things to look at...?

SQL Server 2005 Service Pack 1 CTP March 2006 available

SQL Server 2005 Service Pack 1 - (CTP) March 2006.

http://www.microsoft.com/downloads/details.aspx?FamilyID=39ebef74-1c29-49fe-8221-c94f2b52f6c6&DisplayLang=en

SQL Server 2005 SP1 Webchat on March 22nd 9:00 am PST

We will be hosting a public web chat discussion: March 22nd 9:00 – 10:00 am PST about the March CTP of SP1.  It is the customers' opportunity to engage with the dev team and provide feedback and ask questions. Strong participation from customers will act as a motivator for stronger participation by the dev team and will increase their interest in having these in the future, so we urge you to join and participate. 

 

For more information and sign up go to: http://www.microsoft.com/technet/community/chats/default.mspx#06_0322_TN_SQL. 

Join the SQL Server 2005 SP1 Webchat: March 22nd 9:00 am PST

We will be hosting a public web chat discussion on March 22nd 9:00 – 10:00 am PST about the March CTP of SP1.  It is the customers' opportunity to engage with the dev team and provide feedback and ask questions. Strong participation from customers will act as a motivator for stronger participation by the dev team and will increase their interest in having these in the future.

 

For more information and sign up for this web chat go to: http://www.microsoft.com/technet/community/chats/default.mspx#06_0322_TN_SQL. 

at No comments:

Differences between metacube (informix) and SQL server analysis services

Differences between metacube (informix) and SQL server analysis services

I would like to know differences between metacube (informix) and SQL server analysis services

in terms of

1. What issues need to be considered when migrating from metacube to analysis services.

2. Features

3. Other improtant differences.

 

Thanks in advance C10

SSAS Restore of large cube (6.16gb) fails on 64bit Server

I have large cube (6.16gb) that I backed up to a compressed file of 1.43gb. I restored the backup to a new database on the same server and it failed to display measures from the largest measure group. The measures are displayed as #VALUE. All other dimension and measure groups are OK.

The largest measure group file is 4.4gb in the database ie. 1.fact.data file.

I remember the restriction of 2gb backup in SQL 2000.

Has anyone come across this while restoring a large cube in SSAS?

Thanks


Reply:

I tried an uncompressed backup and got the following error. It failed to backup in uncompressed mode.

File system error: The following error occurred while the '\\?\E:\SSAS\Data\Customer Analytics NL9.0.db\Cube1.0.cub\SALES EXPENSE FACT2.0.det\SALES EXPENSE FACT2.0.prt\1.fact.data' file was being copied to the 'E:\ssas\backups\Customer Analytics NL9.abf' file: .
 (Microsoft.AnalysisServices).

Anyone seen this error?


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

I partitioned the cube, then the backup and restore worked. The largest cube file was less than 2gb.

Is this a bug?

Can anyone from Microsoft respond to this?


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

SQL Server 2005 Developer ed.

I am trying to install 2005 on a new hard drive, running only Windows x64.  The processor is a AMD Athlon 64 3000+, 2.02GHz, with 1GB RAM.

When I click on setup.exe, I receive the following error message:

The image file c:\SQLDEV\setup.exe is valid, but for a machine type other than the current machine.

I chose the developer's edition as recommended by MS when installing on 64 bit Windows.  Any idea what's wrong?

Reply:
Which version do you have? There are 3 sets of SQL Server binaries right now;

IA-64 - Will not work on your OS
x64(Em64T/AMD64) - Best choice for your OS
x86 - Will only work in certain circumstances.

-Euan

------------------------------------
Reply:
If you don't have x64 install (folder name should be SQLDEVAMD64), then you can install the x86 version by navigating to c:\sqldev\setup\sqlsetup.exe file.

------------------------------------
Reply:
Hi,

I'm experiencing the same error with an AMD Athlon 3200+, 1GB RAM computer.
It runs on Win2003 x64 Ent.Ed.
I'm trying to install the  x64 version for Developer Ed.

I wonder whether you have solved your problem or not? I do not want to install the x86 version of the sql server.

By the way, the folder structure is not like /AMD ...
It is as \SQL Server x64\Servers

Thanks for your comments,

Eralper
http://www.kodyaz.com

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

Hello,

My boss just downloaded the SQL Server 2005 Enterprise Edition.  He has asked me to install it to our server....but I get the same message as previously mentioned...

 

Our server is Win 2003 enterprise edition 32 bit

the file name I am trying to install is en_sql_2005_ent_x64_cd1.iso...I have created a CD from this ISO image.

 

The server we are running is a Xeon 64bit 2.8Ghz

 

Any help would be greatly appreciated.  We too would like to run the 64 bit version of SQL server...

Forgive me if this is obvious...but do we have to be running 64bit windows before we can install the 64 bit SQL server?

 

Thanks,

Scott

 

 

 


------------------------------------
Reply:
yes, 64bit applications are for 64bit OS.

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

Yeah, the confusion for some is that the 32-bit version can run both on 32-bit machines and in the WoW for x64 machines.  But the reverse is not true.  The two 64-bit versions are intended only for their single architecture platform.

Thanks,
Samuel Lester (MSFT)


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

mdx query retrieving Sap dimension structure

hello everybody,

we're developing a sort of "navigator" for reporting data from SAP BW cubes.
We found, within our project constraints, xmla+mdx to be the most effective solution.

For the "parameter area" of the navigator we need to obtain dimension structure information, but can't do this via the discover method due to a limitation in the method itself (Sap natively manages "hystorical" dimensions, which isn't handled by the discover method).

The idea could be using the execute method, obtaining the infos by means of a regular mdx query.

I can't figure out what kind of query i should write, I'm interested in reconstructing the hierarchy implied by the dimension, so, theretically speaking, I want to obtain something like "member name", "member level".
The query should work in terms of dimensions, in general I can't hardcode members name in the query.

I would appreciate any help on this, we're stuck on this problem.
Hope I've explained the use case in sufficient detail.

thanks in advance

mauro


SQL Server 2005 Service Pack 1

SQL Server 2005 Service Pack 1 has now been released.

Download - http://www.microsoft.com/downloads/details.aspx?FamilyID=cb6c71ea-d649-47ff-9176-e7cac58fd4bc&DisplayLang=en

New features and improvements KB -http://support.microsoft.com/default.aspx/kb/916940

SSIS section below-

SQL Server 2005 Integration Services (SSIS)

• Usability of the Import/Export Wizard has been improved in multiple-table scenarios.
• The IDtsPipelineEnvironmentService service lets custom data flow components have programmatic access to the parent Data Flow task.
• Interoperability with Analysis Services has been improved. The DataReader source in SSIS supports the System.Object data type by converting columns that have this data type to the DT_NTEXT ssISnoversion data type. To change the data type to a type that is more appropriate for your data, you can add a Data Conversion transformation.
• Performance has been improved for many transformations, such as the Sort transformation.
• Designer usability has been improved. For example, you can now right-click Dataflow and then click Execute Task to execute only the Dataflow task. You do not have to switch to the control flow to execute only the Dataflow task.
• The Expression Builder dialog box now has a public and documented API. This increased access to expression-related objects will benefit task developers because expressions are important to the way that tasks are interrelated.
• We have added a registry-based policy for handling SSIS package signatures as a package is executed. For example, administrators can disable loading of unsigned packages and untrusted packages. Because these settings are in the registry, administrators can propagate these settings in the domain by using Microsoft Windows Group Policy policy settings.
• In the Advanced Editor dialog box, the Flat File source has the new property UseBinaryFormat. This property supports loading packed decimal data into the pipeline for processing by a script or by a custom transformation.
• In the Advanced Editor dialog box, the DataReader source has the new property CommandTimeout. You can use this property to modify the time-out period to allow for long-running operations.
• To create or to modify the property expressions of variables, you can now open the Expression Builder dialog box from the Properties window.
• You can now add annotations to precedence constraints.

SQL Server 2005 Service Pack 1 public (NON-CTP) is available

SQL Server Service 2005 Pack 1 is available here.

The list of new features and fixed was published here on KB Article 916940 .

Keep in mind to read the readme file first in order to avoid problems during the update or unexpected bahaviour.

Have Fun !

-Jens Suessmeyer.

Adventure Works.sln DEPLOY ERROR first time

After opening the Adventure Works.sln.

I build.

I deploy.

I get the following error ...

------ Build started: Project: Adventure Works DW, Configuration: Development ------

Started Building Analysis Services project: Incremental ....


 

Build complete -- 0 errors, 0 warnings

------ Deploy started: Project: Adventure Works DW, Configuration: Development ------

Performing an incremental deployment of the 'Adventure Works DW' database to the 'localhost' server.

Generating deployment script...

Add Database Adventure Works DW

Process Database Adventure Works DW

Done

Sending deployment script to the server...

Error -1056308996 : The attribute Destination Currency does not have any members.

Error -1056308996 : The attribute Parent Organization Key0 does not have any members.

Deploy complete -- 2 errors, 0 warnings

========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========

========== Deploy: 0 succeeded, 1 failed, 0 skipped ==========

 

What am I doing wrong?

What do I do next?

AIM

 


Reply:
The only thing I could think of is that maybe you are deploying the enterprise sample verison against a standard server....

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

I am using SQL Server  Enterprise 2005 180-Eval (and I am choosing the Enterprise folder and not the Standard folder).

My (MSSQLSERVER) Analysis Services is started.  Hmmm mm.  Maybe its the Data connection?  Maybe its not finding my AdventureWorksDW local database?  Maybe I have to define this and not using the default?  Maybe its the order.  I did build.  Next, I did deploy.  Hmmm mm, maybe I a missing something?

Any ideas?

AIMDBA

 


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

When I choose "Process" I get the following result. ...

 

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Parallel>
    <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <Object>
        <DatabaseID>Adventure Works DW</DatabaseID>
      </Object>
      <Type>ProcessFull</Type>
      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
    </Process>
  </Parallel>
</Batch>
 Processing Database 'Adventure Works DW' completed successfully.
  Start time: 4/18/2006 10:39:47 AM; End time: 4/18/2006 10:39:48 AM; Duration: 0:00:01
  Processing Dimension 'Destination Currency' completed successfully.
   Start time: 4/18/2006 10:39:47 AM; End time: 4/18/2006 10:39:48 AM; Duration: 0:00:01
   Processing Dimension Attribute '(All)' completed successfully.
    Start time: 4/18/2006 10:39:47 AM; End time: 4/18/2006 10:39:47 AM; Duration: 0:00:00
   Processing Dimension Attribute 'Destination Currency' completed successfully. 1 rows have been read.
    Start time: 4/18/2006 10:39:47 AM; End time: 4/18/2006 10:39:47 AM; Duration: 0:00:00
    SQL queries 1
     SELECT
  DISTINCT
 [DimDestinationCurrency].[CurrencyName] AS [DimDestinationCurrencyCurrencyName0_0]
  FROM
   (
  
SELECT     CurrencyKey, CurrencyAlternateKey, CurrencyName
FROM         DimCurrency
WHERE     (CurrencyKey IN
                          (SELECT DISTINCT CurrencyKey
                            FROM          FactCurrencyRate))
   )
   AS [DimDestinationCurrency]
   Processing Dimension Attribute 'Destination Currency Code' completed successfully. 1 rows have been read.
    Start time: 4/18/2006 10:39:47 AM; End time: 4/18/2006 10:39:47 AM; Duration: 0:00:00
    SQL queries 1
     SELECT
  DISTINCT
 [DimDestinationCurrency].[CurrencyKey] AS [DimDestinationCurrencyCurrencyKey0_0],[DimDestinationCurrency].[CurrencyAlternateKey] AS [DimDestinationCurrencyCurrencyAlternateKey0_1],[DimDestinationCurrency].[CurrencyName] AS [DimDestinationCurrencyCurrencyName0_2]
  FROM
   (
  
SELECT     CurrencyKey, CurrencyAlternateKey, CurrencyName
FROM         DimCurrency
WHERE     (CurrencyKey IN
                          (SELECT DISTINCT CurrencyKey
                            FROM          FactCurrencyRate))
   )
   AS [DimDestinationCurrency]
  Processing Dimension 'Organization' completed successfully.
   Start time: 4/18/2006 10:39:47 AM; End time: 4/18/2006 10:39:48 AM; Duration: 0:00:01
   Processing Dimension Attribute '(All)' completed successfully.
    Start time: 4/18/2006 10:39:47 AM; End time: 4/18/2006 10:39:47 AM; Duration: 0:00:00
   Processing Dimension Attribute 'Currency Code' completed successfully. 1 rows have been read.
    Start time: 4/18/2006 10:39:48 AM; End time: 4/18/2006 10:39:47 AM; Duration: 0:00:-01
    SQL queries 1
     SELECT
  DISTINCT
 [dbo_DimOrganization].[CurrencyKey] AS [dbo_DimOrganizationCurrencyKey0_0],[dbo_DimOrganization].[CurrencyAlternateKey] AS [dbo_DimOrganizationCurrencyAlternateKey0_1]
  FROM
   (
  
SELECT     o.OrganizationKey, o.ParentOrganizationKey, o.PercentageOfOwnership, o.OrganizationName, o.CurrencyKey, c.CurrencyAlternateKey
FROM         DimOrganization AS o INNER JOIN
                      DimCurrency AS c ON o.CurrencyKey = c.CurrencyKey
   )
   AS [dbo_DimOrganization]
   Processing Dimension Attribute 'Organization' completed successfully. 1 rows have been read.
    Start time: 4/18/2006 10:39:48 AM; End time: 4/18/2006 10:39:48 AM; Duration: 0:00:00
    SQL queries 1
     SELECT
  DISTINCT
 [dbo_DimOrganization].[OrganizationKey] AS [dbo_DimOrganizationOrganizationKey0_0],[dbo_DimOrganization].[OrganizationName] AS [dbo_DimOrganizationOrganizationName0_1],[dbo_DimOrganization].[PercentageOfOwnership] AS [dbo_DimOrganizationPercentageOfOwnership0_2],[dbo_DimOrganization].[CurrencyKey] AS [dbo_DimOrganizationCurrencyKey0_3],[dbo_DimOrganization].[ParentOrganizationKey] AS [dbo_DimOrganizationParentOrganizationKey0_4]
  FROM
   (
  
SELECT     o.OrganizationKey, o.ParentOrganizationKey, o.PercentageOfOwnership, o.OrganizationName, o.CurrencyKey, c.CurrencyAlternateKey
FROM         DimOrganization AS o INNER JOIN
                      DimCurrency AS c ON o.CurrencyKey = c.CurrencyKey
   )
   AS [dbo_DimOrganization]
   Processing Hierarchy 'Organizations'.
    Start time: 4/18/2006 10:39:48 AM; End time: 4/18/2006 10:39:48 AM; Duration: 0:00:00
  Processing Dimension 'Sales Reason' completed successfully.
   Start time: 4/18/2006 10:39:48 AM; End time: 4/18/2006 10:39:48 AM; Duration: 0:00:00
Errors and Warnings from Response
 The attribute Destination Currency does not have any members.

Any ideas?

Thanks.

AIMDBA


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

Fact to Dimension Across databases causes 'Invalid Object Name, 42S02' Error

I have a fact table with invalid keys.  Because of this, I am using SSIS to pull a copy of the fact table into my own database which operates in parallel with the production database.  I correct the keys and use the corrected version to build my fact table.  The dimension remains in the correct production database.  Therefore, I have:

Database A: Table 1 (Dimension)
Database B: Table 2 (Fact Table containing DimensionID, a foreign key to Table 1)

When I process this cube, the action fails with an invalid object name due to the referenced table being in a seperate database.

Is this intended by design? 
Can I force the cube to fully qualify the name so that it will reference objects across databases?

Thanks for any help.

Invalid snapshot-triggered data-driven subscription causes RS dump

If a data-driven subscription definition is invalid because it is trying to set a report parameter that affects the contents of the snapshot that the report is using, in a report that is rendered from a snapshot, SSRS responds with

"An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help For more information about this error navigate to the report server on the local server machine, or enable remote errors"

If I display the report itself, parameters that are used in the dataset query are grayed out and I can't change them. When I define a data-driven subsubscription, however, these parameters can be assigned, and if they are not left at "default," the above error results when the definition is saved. The subscription is not created.

The following detail is included in SQLDumprnnnn.log in the SSRS logfiles directory:

w3wp!library!9!04/14/2006-17:50:02:: i INFO: Call to GetSystemPermissions

w3wp!library!9!04/14/2006-17:50:02:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details., ;

Info: Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details. ---> System.ArgumentNullException: Key cannot be null.

Parameter name: key

at System.Collections.Hashtable.Insert(Object key, Object nvalue, Boolean add)

at System.Collections.Hashtable.Add(Object key, Object value)

at Microsoft.ReportingServices.ReportProcessing.ParameterInfoCollection.ValidateInputValues(ParamValues inputValues, Boolean isSnapshotExecution)

at Microsoft.ReportingServices.Library.SubscriptionManager.ValidateSubscriptionParameters(String reportName, ParameterValueOrFieldReference[] subscriptionParameters, JobType jobType)

at Microsoft.ReportingServices.Library.RSService._CreateSubscription(Guid id, String report, ExtensionSettings extensionSettings, String description, String eventType, String matchData, ParameterValueOrFieldReference[] parameters, DataRetrievalPlan dataSettings)

at Microsoft.ReportingServices.Library.RSService.CreateSubscription(Guid batchId, String report, ExtensionSettings extensionSettings, String description, String eventType, String matchData, ParameterValueOrFieldReference[] parameters, DataRetrievalPlan dataSettings)

--- End of inner exception stack trace ---

Please help me with a Query

Hi, 

I have two tables:

1. Encyclopedia (ComponentID, MajorAttribute,BodyPart) with primary(ComponentID)

2. Minor(ComponentID,MinorAttribute) with primary(ComponentID)

i am using Microsoft Analysis Services to generate the model using Encyclopedia as the case table and Minor as nested with Association Rules... i have got the rules but need help with the query ...

I need to predict the component along with the probability of correctness by providing  the Minor(maybe more than one) and Major Attributes.

The query should preferrably let me decide on the number of best probabilities to display

Thanks in advance

Sundeep Singh

An article on FOR XML in Microsoft SQL Server 2000 and 2005

I just posted article What does server side FOR XML return? (http://blogs.msdn.com/sqlprogrammability/articles/576095.aspx) which gives some details of the design and performance characteristics of various FOR XML flavors.

The article is posted to SQL Programmability & API Development Team Blog (http://blogs.msdn.com/sqlprogrammability/)

Best regards,

Eugene Kogan

Technical Lead

Microsoft SQL Server Relational Engine

WISH: Allow @vars as arguments to .nodes, .query, .value, etc.

... maybe this is already in the works?  Right now these functions only accept string literals.

-- Chris

 


Reply:

SQL Server development team has received multiple requests for this feature and is considering implementing it for next versions of SQL Server (after 2005).

 

Best regards,

Eugene


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

Remove Grand Total in Pivot Table issue using asp.net

Hi,

I am trying to remove the grad total amount from the pivot table list with asp.net,programing. The data I called from cube, I have rows,columns and data axis values, when I place my measure in to data axis it autometically created sub total and grand totals, I want remove those.  I could not found any property or method to remove that.

Please help me on this.

 

 

 


Reply:

Hi Please help, I still not got any answer.

 

 


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

Performance Issues with Analysis Services for SQL Server 2005

I'm experiencing performance problems with SSAS 2005. I have a data base with two years and midle of information , an average of 25 Million register per year. when I make a search to the data base consume all the CPU and anybody can not connect to the cube.

 

Sorry by my english.

Standard Deviation on a non aggregatable measure

I am new MDX and datawarehousing world. I would like your help in resolving this problem. There is very little documentation on the using the STDDEV function.

I need to calculate standard deviation for a non aggregatable measure of price for a given product (which varies by time). I tried making the price as a measure and selected 'none' under AggregateFunction of the property. I created calculation and tried putting the formula

iif(IsEmpty([Measures].[Price]), NULL,

StdDev([Dimension].[Product].Members, [Measures].[Price]))

However I am not seeing any value in the browser.

My fact table contains a new row for each product along with the price (and other data).

I need to display to the user for a given time period the average price, standard deviation of all the products. Please help me.

Thank you

 

Querying AS 2000 Cubes from SQLServer 2000

When creating a linked server from SQLServer 2000 to query data from AS 2000 cubes, I get an error:

Here's the command I used to create the linked server in SQL Server 2000:
 
EXEC sp_addlinkedserver
     @server='OLAP',
     @srvproduct='',
     @provider='MSOLAP',
 
     @datasrc='ServerABC',
     @catalog='OLAP_DB'
 
go
 
and then when I run the following query:
 
SELECT *
FROM OPENQUERY(  OLAP,
   'SELECT  [Segment],
    SUM([Transactions Total])
   FROM Summary_Cube 
   GROUP BY [Segment]'
  )
 
I get the following error:
 
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSOLAP' reported an error. 
[OLE/DB provider returned message: Database 'OLAP_DB' does not exist.]
OLE DB error trace [OLE/DB Provider 'MSOLAP' IDBInitialize::Initialize returned 0x80004005:   ].
 
Anyone else seen this before? How can I correct this?
 
Thanks,
 
John T
 

SSAS 2005 : Low performances with 2 dimensions using UNARY operator

I made some tests with SSAS 2005, because, when I used dimenions with unary operator, the performances was very bad.

I created a cube :
2 regular Dimensions
Dim 1 : 3 200 lines
Dim 2 : 150 lines

Facts : 400 000 lines

ProClarity is the client taht I use to query the cube.

Find below the details of the tests

Test 1 :
----------
No unary operator
Full process of dimensions and cube : Server memory (MSMDSRV) = 64 Kb,
virtual memory : 61 Kb
Open cube : server memory = 64 Kb, virtual memory : 61 Kb. Time = less
than 2 seconds
Drill to bottom level of the 2 dimensions : server memory = 195 Kb,
virtual memory : 192 Kb
Queries are very fast.

Test 2 :
---------- 
unary at the bottom level of Dim 1.
Full process of dimensions and cube : Server memory (MSMDSRV) = 70 Kb,
virtual memory : 68 Kb
Open cube : server memory = 70 Kb, virtual memory : 68 Kb. Time = less
than 2 seconds
Drill to bottom level of the 2 dimensions : server memory = 195 Kb,
virtual memory : 192 Kb
Queries are very fast.

Test 3 :
---------- 
unary at the bottom level of Dim 2.
Full process of dimensions and cube : Server memory (MSMDSRV) = 64 Kb,
virtual memory : 61 Kb
Open cube : server memory = 68 Kb, virtual memory : 65 Kb. Time = less
than 2 seconds
Drill to bottom level of the 2 dimensions : server memory = 195 Kb,
virtual memory : 192 Kb
Queries are very fast.

Test 4 :
---------- 
unary at the bottom level of Dim 1.
unary at the bottom level of Dim 2.
Full process of dimensions and cube : Server memory (MSMDSRV) = 64 Kb,
virtual memory : 61 Kb
Open cube : server memory = 834 Kb, virtual memory : 831 Kb. Time :
more than 1 minute, just to open the cube.
Drill to bottom level of the 2 dimensions : server memory = 962 Kb,
virtual memory : 959 Kb; Very fast
But if I navigate thru the level of the Dim 2, the memory used increase
: server memory = 1104 Kb, virtual memory : 1102 Kb

Test 5 :
----------
No unary on Dim 1.
Dim 2 is replaced by a parent-child dimension with 180 members. There
is a unary operator at the bottom level.
Full process of dimensions and cube : Server memory (MSMDSRV) = 65 Kb,
virtual memory : 62 Kb
Open cube : server memory = 68 Kb, virtual memory : 65 Kb. Time = less
than 2 seconds
Drill to bottom level of the 2 dimensions : server memory = 193 Kb,
virtual memory : 190 Kb
Very fast

Test 6 :
---------- 
unary at the bottom level of Dim 1.
Dim 2 is replaced by a parent-child dimension with 180 members. There
is a unary operator at the bottom level.
Full process of dimensions and cube : Server memory (MSMDSRV) = 68 Kb,
virtual memory : 66 Kb
Open cube : server memory = 834 Kb, virtual memory : 831 Kb. Time :
more than 1 minute, just to open the cube.
Drill to bottom level of the 2 dimensions : server memory = 226 Kb,
virtual memory : 223 Kb. very fast
But navigate thru the level of the Dim 2 (parent-child) is very slow :
more than 50 seconds for each drill.

Conclusion :
------------------
If you use just one dimension with unary operator. It's OK? You have
good performances. But if you use 2 or more dimensions with unary 
operator, the performance become very bad, especially if the dimensions
are regular : the memory used is very large.

If somebody have information or solution, it can help me

Thanks

analysis services on sql server 2005 and win2003 server are slowing down the system

Hi,

I'm working with the Team foundation server, and I've got the analysis services installed on a win2003 server in combination with the sql 2005 server.
The analysis services start (or uses) the msmdsrv.exe process which slows down my whole system. Once i stopped the TFSscheduler service and the "ms sql analysis services" using the sql configuration manager  it works normally. My server has got 2GB of RAM which is not that bad!

As I know this process is used to update the warehouse. this should be rebuilded every hour...

 Does anybody know if there's a memory leak? does anybody know what's the matter with this issue? I've seen that there's a bugfix for the sql2000 server, which should fix this issue...

thanks for any help

there's an error log in the event log. But I'm not sure if it is related to the issue above:

Detailed Message: Cube processing runtime error: \r\nMicrosoft.AnalysisServices.ResponseFormatException: The server sent an unrecognizable response. ---> System.Xml.XmlException: Root element is missing.
   at System.Xml.XmlTextReaderImpl.Throw(Exception e)
   at System.Xml.XmlTextReaderImpl.ThrowWithoutLineInfo(String res)
   at System.Xml.XmlTextReaderImpl.ParseDocumentContent()
   at System.Xml.XmlTextReaderImpl.Read()
   at System.Xml.XmlTextReader.Read()
   at System.Xml.XmlReader.MoveToContent()
   at System.Xml.XmlReader.ReadStartElement(String localname, String ns)
   at Microsoft.AnalysisServices.XmlaReader.ReadStartElement(String localname, String ns)
   at Microsoft.AnalysisServices.XmlaClient.SendMessage(Boolean endReceivalIfException, Boolean readSession, Boolean readNamespaceCompatibility)
   --- End of inner exception stack trace ---
   at Microsoft.AnalysisServices.XmlaClient.SendMessage(Boolean endReceivalIfException, Boolean readSession, Boolean readNamespaceCompatibility)
   at Microsoft.AnalysisServices.AnalysisServicesClient.Discover(IMajorObject obj, ObjectExpansion expansion)
   at Microsoft.AnalysisServices.Server.Refresh(IMajorObject obj, ObjectExpansion expansion)
   at Microsoft.AnalysisServices.Server.SendRefresh(IMajorObject obj, ObjectExpansion expansion)
   at Microsoft.AnalysisServices.MajorObject.Refresh()
   at Microsoft.AnalysisServices.MajorObject.get_Body()
   at Microsoft.AnalysisServices.Cube.get_Body()
   at Microsoft.AnalysisServices.Cube.get_MeasureGroups()
   at Microsoft.TeamFoundation.Warehouse.OlapCreator.IsSchemaUpdated(Database db)
   at Microsoft.TeamFoundation.Warehouse.OlapCreator.ProcessOlap(Boolean schemaUpdated, UpdateStatusStore updateStatus)
   at Microsoft.TeamFoundation.Warehouse.AdapterScheduler.RunCubeProcess()

Weitere Informationen über die Hilfe- und Supportdienste erhalten Sie unter http://go.microsoft.com/fwlink/events.asp.

Current User Roles List seems Incorrect

Our app uses the ROLES column of the CATALOGS rowset from the schema, and I seem to be seeing what I think are incorrect results.

The documenation says "Username is appended to ROLES if one of the roles uses dynamic security".  OK.

But I often get the username even when there are no MDX expressions used for dimension or cell access.  For example, every Foodmart 2000 cube I have migrated shows the username in the role list.

What is the definition of dynamic security for the purposes of the CATALOGS rowset?


Reply:

I should have noted, this is SSAS 2005.

This is generally how I am retrieving the roles list, if any one wishes to replicate.

OleDbConnection conn = new OleDbConnection( "provider=msolap;SSPI=Negotiate;Prompt=1" );  conn.Open();  DataTable schema = conn.GetSchema("Catalogs");  foreach(DataRow row in schema.Rows)   {   if (row["ROLES"].ToString().Length > 0) // only available for the current catalog    txtRoles.Text = row["ROLES"].ToString();   }  conn.Close();

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

MDX Linked time question

Hello, I am new to Analysis services but I have a quick question on a capability.

I am trying to calculate a measure both in an mdx select statement and later turn this into a calculated measure.

Here is the scenario:

Currently I have a fact table with links to 3 dimensions with one of them being an asset dimension, one being a time dimension, and metric dimension. In the time dimension I have day, year, hour, day of week, day of month,etc. What I want is to calculate a measure based upon both the day of the week and the hour of the day for a metric for an asset. So data from 11pm on Weds is averaged from all the data only on 11pm on a Weds.

Can you chain some time periods together to filter the measures among two different attributes from the same dimension?

 The end result is to show the data from that day and hour (in the measures group) along with the rolling average for the same day and hour in the same query.

Then to create a calucated measure to make the queries much easier later.

 

Thanks in advance.

at No comments:
Subscribe to: Posts (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...