marked transactions

I am running the following script in Query Analizer:
begin transaction xxxx with mark
update logmarks set logmark=2
commit transaction xxxx
go
The script runs without error and the logmarks table is updated correctly,
but when I check the logmarkhistory table to see if a row was inserted there
is nothing in there. What am I doing wrong or why does this happen?
Thanks in advance for any help.The transaction information will be stored in the logmarkhistory table
only if there is a active log backup chain.sql

Mapping two measure groups with different dimension usage

Hello.

I am looking for a solution for the following situation:

I have a measure group for my business facts and a measure group for tolerances and targets.

The first measure group uses a date dimension, a site dimension, a project dimension and an employee dimension.

The second group shares all dimensions except the employee dimension.

Lets say I have the business fact sales per hour.
The target value for this fact is stored in the second measure group without the employee info. The targets are valid for all employees at that site, project and day.

When i browse the cube i want see the targets next to the actual data - even on employee level.

Therefore i have to set IgnoreUnrelatedDimensions in the properties of the second measure group to True.
But when i do this i get an entry for every employee present in the employee dimension, showing the tolerance and target.

I am lost at the moment and belive that there must be an elegant solution. Hopefully someone has an idea?

Thanks,
ThomasFrom your description, it's not clear in which cases the IgnoreUnrelatedDimensions doesn't meet your needs - could you describe some scenarios? You could selectively use the MDX ValidMeasure() function in calculations, instead of applying IgnoreUnrelatedDimensions.|||Hello Deepak.

Thanks for your reply.

I'll try to explain my problem better with an example.

Data in my Tolerance FactTable:

DateProjectSiteTolerance_Sales_per_Hour20070320Upsell1Hamburg220070321Upsell1Hamburg220070322Upsell1Hamburg3

Data in my Sales FactTable:

DateProjectSiteEmployeeSales_Per_Hour20070320Upsell1HamburgBob1.520070320Upsell1HamburgMike2.120070321Upsell1HamburgBob2.720070321Upsell1HamburgMike3.320070322Upsell1HamburgBob1.2

When I select 20070322 as Date in the Browser, i get a row for Mike, although he dind't work on 22th.

DateProjectSiteEmployeeSales_Per_HourTolerance_Sales_per_Hour20070322Upsell1HamburgBob1.2320070322Upsell1HamburgMike3

Thanks to a tip from Markus i am using Scope-Statements at the moment

SCOPE([Measures].[Tolerance_Sales_Per_Hour]);
THIS=IIF (ISEMPTY([Measures].[Sales_Per_Hour]),NULL,[Tolerance_Sales_Per_Hour]);
END SCOPE;

But we are not sure, if this is the best solution.

Best regards,
Thomas

mapping a relationship to bulkload

I have the following document:

<customers>
<customer>
<name>xyz</name>
<address>1, Sacramento st</address>
<customer>
<customers>

I

want to map to the customer name to the customer(id, name, addr_id).

But the address to go custaddr table which has custaddr(id, address)

and the custaddr(id) goes to customer(addr_id ). I would like some

pointers to write a schema mapping for doing this.

I can change the xml format but I can't change the database design. Is it possible to do bulkload of this using XML Bulk load?

Thanks

vln
Hi ...

The link to this thread might help ...

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=174122&SiteID=1

Below is the Bulk Load implementation in C++.

Thanks,

Chris
Hi ...

I posted a question about Bulk Loading Xml data using C++ and the

SQLXMLBulkLoad.SQLXMLBulkload.3.0 COM Component. Below is the C++ code

that will do this. I have also included the xml, xsd and table

definition.
void CTestMeteorlogixApp::OnTestMsXmlBulkLoadRwis()

/* ///////////////////////////////////////////////////////////////////////

Method: CTestMeteorlogixApp::OnTestMsXmlBulkLoadRwis()

Description:

Microsoft XML Core Services (MSXML) offers several programmatic

extensions for writing XML applications. We will play with some

of these COM methods here.

Parameters: None

Return: None

Note:

Some of the code in this mehtod was derived from the following

discussion on xml:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/htm/bulkload_7pv0.asp

Notes: ISQLXMLBulkLoad

We had to do a little work to get the ISQLXMLBulkLoad object in here.

First we installed the XMLSQL Component onto the computer. After this

we needed to find the dll that we are going to import. You will

find the import (#import <xblkld3.dll>) in stdafx.h.

We then added the code below thinking that we could create the

object this way. This did not work. We will talk about this

alitttle more later.

ISQLXMLBulkLoad pISQLXMLBulkLoad = NULL;

pXMLDoc.CreateInstance("SQLXMLBulkLoad.SQLXMLBulkload.3.0");

Using the object viewer in Visual Studio [Tools]+[OLE/COM Obejct Viewer]

we were able to find the SQLXMLBulkLoad Class. From this we were able

to find the path where the dll is located an then look at the

IDL by viewing the TypeLib of the Class [File]+[View TypeLib...].

Looking at the IDL I decided that I would implement the object

as I would from an COM class using the progID[] and CoCreateInstance().

So in the end that is what we have done and the code is below. We

are still testing but it all is working. Note that we also looked

at the readme.txt from from the SQLXML install and then have

noted that they copyed the following files and I know that we would be

needed these to implment the COM interface.

// You will need these.

// C:\Program Files\SQLXML 3.0\include:

Xmlblkld.h

Xmlblkld_i.c

/////////////////////////////////////////////////////////////////////// */

{

int nDataCount = 0;

int nCount = 0;

long nIndex = 0;

long nIndex2 = 0;

CString csErrorMessage;

CString csLogMessage;

CString csMethodName;

CString csMessage;

CString csXmlSchemaFile("C:\\Macgowan\\Project\\TestMeteorlogix\\Data\\rwis_oh.xsd");

CString csXmlDataFile("C:\\Macgowan\\Project\\TestMeteorlogix\\Data\\rwis_oh.xml");

CString

csXmlErrorLogFile("C:\\Macgowan\\Project\\TestMeteorlogix\\Data\\rwis_oh.err");

CString csTableName1("Alphanumericdata.dbo.MacgowanTestRWISRawAtmospheric");

CString csTableName2("Alphanumericdata.dbo.MacgowanTestRWISRawSurface");

CString csProvider("sqloledb");

CString csDataSource("SQLDEV");

CString csDatabase("Alphanumericdata");

CString csUserId("cmacgowan");

CString csPassword("7498757");

_bstr_t bstrXmlData;

_bstr_t bstrConnect;

_bstr_t bstrXmlSchemaFile;

_bstr_t bstrXmlDataFile;

_bstr_t bstrXmlErrorLogFile;

_bstr_t bstrSql;

variant_t vResult;

variant_t vXmlDataFile;

HRESULT hResult;

CString csXmlData;

CString csTemp;

CString csTemp2;

CString csResult;

CString csConnect;

CString csSql;

// Define ADO connection pointers

_ConnectionPtr pConnection = NULL;

CMainFrame *pMainFrame = (CMainFrame *)AfxGetMainWnd();

CFrameWnd* pChild = pMainFrame->GetActiveFrame();

CTestMeteorlogixView* pView = (CTestMeteorlogixView*)pChild->GetActiveView();

pView->WriteLog("Start OnTestMsXmlBulkLoadRwis().");

try

{

CoInitialize(NULL);

// Tell the user what is going on ...

pView->WriteLog("Bulk

load xml using the SQLXMLBulkLoad.SQLXMLBulkload.3.0 COM Object");

csLogMessage.Format("Schema file: %s", csXmlSchemaFile);

pView->WriteLog(csLogMessage);

csLogMessage.Format("Data file: %s", csXmlDataFile);

pView->WriteLog(csLogMessage);

csLogMessage.Format("Error log file: %s", csXmlErrorLogFile);

pView->WriteLog(csLogMessage);
// When we open the application we will open the ADO connection

pConnection.CreateInstance(__uuidof(Connection));

// Set the connection string

csConnect.Format("provider=%s;data Source=%s;database=%s;uid=%s;pwd=%s",

csProvider,

csDataSource,

csDatabase,

csUserId,

csPassword);

bstrConnect = csConnect.AllocSysString();

// Open the ado connection

pConnection->Open(bstrConnect,"","",adConnectUnspecified);

// Convert filenames from CString to varients

bstrXmlSchemaFile = csXmlSchemaFile.AllocSysString();

bstrXmlDataFile = csXmlDataFile.AllocSysString();

vXmlDataFile.SetString(LPCTSTR(csXmlDataFile));

bstrXmlErrorLogFile = csXmlErrorLogFile.AllocSysString();

char progID[] = "SQLXMLBulkLoad.SQLXMLBulkload.3.0";

// Now make that object.

CLSID clsid;

wchar_t wide[80];

mbstowcs(wide, progID, 80);

CLSIDFromProgID(wide, &clsid);

ISQLXMLBulkLoad* pISQLXMLBulkLoad = NULL;

if(SUCCEEDED(CoCreateInstance(clsid, NULL, CLSCTX_ALL,

IID_ISQLXMLBulkLoad, (void**)&pISQLXMLBulkLoad)))

{

hResult = pISQLXMLBulkLoad->put_ConnectionString(bstrConnect);

hResult = pISQLXMLBulkLoad->put_BulkLoad((bool)TRUE);

hResult = pISQLXMLBulkLoad->put_ErrorLogFile(bstrXmlErrorLogFile);

hResult = pISQLXMLBulkLoad->put_KeepIdentity((bool)FALSE);

hResult = pISQLXMLBulkLoad->Execute(bstrXmlSchemaFile, vXmlDataFile);

if (SUCCEEDED(hResult))

{

pView->WriteLog("pISQLXMLBulkLoad->Execute() was successful.");

}

else

{

pView->WriteLog("Error: pISQLXMLBulkLoad->Execute()

failed.");

}

}

else

{

AfxMessageBox("Error: We could not find the ProgID!");

}

}

catch(_com_error *e)

{

CString Error = e->ErrorMessage();

AfxMessageBox(e->ErrorMessage());

pView->WriteLog("Error processing TestDatabase().");

}

catch(...)

{

pView->WriteLog("Error processing TestDatabase().");

}

pView->WriteLog("End OnTestMsXmlBulkLoadRwis().");

CoUninitialize();

return;

}
///////////////////////////////////////////////////////////////////////

// xsd schema

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"

xmlns:sql="urn:schemas-microsoft-com:mapping-schema">

<xsd:element name="site" sql:relation="MacgowanTestRWISRawAtmospheric" >

<xsd:complexType>

<xsd:attribute name="sysid" type="xsd:string" sql:field="SystemId"/>

<xsd:attribute name="rpuid" type="xsd:string" sql:field="RpuId"/>

</xsd:complexType>

</xsd:element>

<xsd:element name="atmospheric" sql:relation="MacgowanTestRWISRawAtmospheric" >

<xsd:complexType>

<xsd:attribute name="datetime" type="xsd:date" sql:field="ObsDateTime"/>

<xsd:attribute name="airtemp" type="xsd:string" sql:field="Temperature"/>

<xsd:attribute name="dewpoint" type="xsd:string" sql:field="DewPoint"/>

</xsd:complexType>

</xsd:element>

</xsd:schema>
///////////////////////////////////////////////////////////////////////

// xml data

<?xml version="1.0"?>

<odot_rwis_site_info>

<site id="200000" number="1" sysid="200" rpuid="0"

name="1-SR127 @. SR249" longitude="-84.554946" latitude="41.383527">

<atmospheric datetime="12/05/2005 03:48:00 PM"

airtemp="-490" dewpoint="-800" relativehumidity="73" windspeedavg="11"

windspeedgust="19" winddirectionavg="265" winddirectiongust="295"

pressure="65535" precipitationintensity="None" precipitationtype="None"

precipitationrate="0" precipitationaccumulation="-1" visibility="2000"

/>

<sensors>

<surface id="0" datetime="12/05/2005

03:48:00 PM" name="North Bound Driving Lane" surfacecondition="Dry"

surfacetemp="1900" freezingtemp="32767" chemicalfactor="255"

chemicalpercent="255" depth="32767" icepercent="255"

subsurfacetemp="450" waterlevel="0">

<traffic

datetime="12/05/2005 03:48:00 PM" occupancy="0" avgspeed="82"

volume="21" sftemp="1900" sfstate="255">

<normalbins>

<bin datetime="12/05/2005 03:48:00 PM" binnumber="0" bincount="7"

/>

<bin datetime="12/05/2005 03:48:00 PM" binnumber="1" bincount="0"

/>

</normalbins>

<longbins>

<bin datetime="12/05/2005 03:48:00 PM" binnumber="2" bincount="0"

/>

<bin datetime="12/05/2005 03:48:00 PM" binnumber="3" bincount="0"

/>

<bin datetime="12/05/2005 03:48:00 PM" binnumber="4" bincount="1"

/>

<bin datetime="12/05/2005 03:48:00 PM" binnumber="5" bincount="0"

/>

</longbins>

</traffic>

</surface>

<surface id="1" datetime="12/05/2005

03:48:00 PM" name="Bridge Deck Simulator" surfacecondition="Other"

surfacetemp="-60" freezingtemp="32767" chemicalfactor="255"

chemicalpercent="255" depth="32767" icepercent="255"

subsurfacetemp="-999999" waterlevel="0" />

</sensors>

</site>

</odot_rwis_site_info>
///////////////////////////////////////////////////////////////////////

// table definition

CREATE TABLE [MacgowanTestRWISRawAtmospheric] (

[RecordId] [int] IDENTITY (1, 1) NOT NULL ,

[DataSourceId] [char] (4)

COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT

[DF_MacgowanTestRWISRawAtmospheric_DataSourceId] DEFAULT ('OH'),

[ProductInstanceId] [char]

(38) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT

[DF_MacgowanTestRWISRawAtmospheric_ProductInstanceId] DEFAULT

('5abbbc86-fb2c-4703-9589-b55f763ee150'),

[SystemId] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[RpuId] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[SensorId] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[ObsDateTime] [datetime] NULL ,

[InsertDateTime] [datetime]

NOT NULL CONSTRAINT [DF_MacgowanTestRWISRawAtmospheric_InsertDateTime]

DEFAULT (getdate()),

[Temperature] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[DewPoint] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

CONSTRAINT [PK_MacgowanTestRWISRawAtmospheric] PRIMARY KEY NONCLUSTERED

(

[RecordId]

) WITH FILLFACTOR = 70 ON [PRIMARY]

) ON [PRIMARY]

GO

Many to many or not ?

Hello I have a question regarding many to many diemensions.

I have a fact table with the following structure :

- fact_id

- act_cat

- amount

The dimension table 'PL structure' has the following structure :

- pl id

- report_view

- act_cat

- name

One line from the fact table relates to two lines from the dimension table.

Example:

the act_cat 'Holiday' frol the fact table relates to the act_cat 'Holiday' in the dimension table. But in the dimension table the act_cat 'Holdiay' exists two times, one time with report_view 'View 1' and one time with report_view 'View 2'

The report_view will become a parameter on the reports. So the end-user must always select one report_view.

Now is the question. how can I solve this problem in Analysis Services. Can I do this only with many to many dimensions, or is there another solution.

The name "Report_view" suggest me that you may face this scenario in a different way, but assuming that you really have to handle this situation, the many-to-many approach should be working (below I suggest you how).

But please let me explain one thing: it is always strange when you have a dimension (PL structure) that has an ID (pl id) that is not referenced into the fact table. You don't have the star schema, and when your relational model is not star-schema based, you always have some hidden issue that was not solved in the relationa design.

Anyway, if for whatever reason you model is the best one (or is the only you can use...) then this is a possible solution.

You have to define one named query (or a view) that I name Factless_ActCat_PL_Id:

SELECT pl_id, act_cat FROM [PL Structure]

Then you have to define another named query (or view) that I name Dim_Act_Cat:

SELECT DISTINCT act_cat FROM [PL Structure]

At this point you have two fact tables and two dimensions.

You define the Data Source View with these 4 tables/views and you have to define this logical primary key by hand if the wizard doesn't find them:

act_cat must be the primary key for Dim_Act_Cat.

Then you define 2 dimension (PL structure and Dim_Act_Cat) and two measure groups (original fact table and Factless_Act_Cat_PL_Id). Your fact table has a regular relationship with Dim_Act_Cat and a many-to-many relationship with PL Structure - to build that, the Factles_Act_Cat_PL_Id measure group must have two regular relationships with PL structure and Dim_Act_Cat dimensions.

Please read my paper on many-to-many dimensions if you are in trouble with these concepts:
http://www.sqlbi.eu/manytomany.aspx

Let me know if it works as you expected.

Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi

Many to many or not ?

Hello I have a question regarding many to many diemensions.

I have a fact table with the following structure :

- fact_id

- act_cat

- amount

The dimension table 'PL structure' has the following structure :

- pl id

- report_view

- act_cat

- name

One line from the fact table relates to two lines from the dimension table.

Example:

the act_cat 'Holiday' frol the fact table relates to the act_cat 'Holiday' in the dimension table. But in the dimension table the act_cat 'Holdiay' exists two times, one time with report_view 'View 1' and one time with report_view 'View 2'

The report_view will become a parameter on the reports. So the end-user must always select one report_view.

Now is the question. how can I solve this problem in Analysis Services. Can I do this only with many to many dimensions, or is there another solution.

The name "Report_view" suggest me that you may face this scenario in a different way, but assuming that you really have to handle this situation, the many-to-many approach should be working (below I suggest you how).

But please let me explain one thing: it is always strange when you have a dimension (PL structure) that has an ID (pl id) that is not referenced into the fact table. You don't have the star schema, and when your relational model is not star-schema based, you always have some hidden issue that was not solved in the relationa design.

Anyway, if for whatever reason you model is the best one (or is the only you can use...) then this is a possible solution.

You have to define one named query (or a view) that I name Factless_ActCat_PL_Id:

SELECT pl_id, act_cat FROM [PL Structure]

Then you have to define another named query (or view) that I name Dim_Act_Cat:

SELECT DISTINCT act_cat FROM [PL Structure]

At this point you have two fact tables and two dimensions.

You define the Data Source View with these 4 tables/views and you have to define this logical primary key by hand if the wizard doesn't find them:

act_cat must be the primary key for Dim_Act_Cat.

Then you define 2 dimension (PL structure and Dim_Act_Cat) and two measure groups (original fact table and Factless_Act_Cat_PL_Id). Your fact table has a regular relationship with Dim_Act_Cat and a many-to-many relationship with PL Structure - to build that, the Factles_Act_Cat_PL_Id measure group must have two regular relationships with PL structure and Dim_Act_Cat dimensions.

Please read my paper on many-to-many dimensions if you are in trouble with these concepts:
http://www.sqlbi.eu/manytomany.aspx

Let me know if it works as you expected.

Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi

Many thanks in advance! - Simple Date function - Please Help!

Hi All,

Does anyone know how to return a date the sql query analyser like (Aug 2, 2004)

Right now, the following statement returns (Aug 2, 2004 8:40PM). This is now good because I need to do a specific date search that doesn't include the time.

Many thanks in advance!!
Brad

--------------
declare @.today DateTime
Select @.today = GetDate()
print @.todaylook at the Convert function...something like...

select convert(varchar, getdate(), 6)

|||Heres a tutorial that I thought was helpful
http://www.easerve.com/developer/tutorials/asp-net-tutorials-dates.aspx

Many - Many Currency Conversion

I ran the "Currency conversion" BI wizard to implement Many to Many currency conversion but get the following error when I click on Finish in the wizard. Looks like the wizard is trying to generate some thing wrong. Any clue ?

The 'DimensionAttribute' with 'Name' = 'Reporting Currency' doesn't exist in the collection

Cheers,

Arun

Got this to working by creating a "Role Playing dimension" for currency that is used by the FX rates measure groups. Now, I have a bigger problem.

1. er the wizard generates the dimensions, calculation etc., we need to define the relationship b/w the generated "Reporting Currency" dimension and the measure groups. (Else the new dimension will not be displayed in the client). But, logically there is no relationship. How do we overcome this ?

measure group1 : Balance

Dimension : Calendar, Currency, ......

measure group2 : Rates (The fact table contains rates for all currencies from EUR for all days. cols - Date, CurrencyCode, Rate)

Dimension : Calendar, ToCurrency, ....

Newly generated dimension : Reporting Currency

2. Also a member called "Local" is used in calcualtion. Can some one explain the need for this ?

Any help is appreciated.

|||

Could you clarify your goal? Are you saying you want to see the "To Currency" (cube) dimension against the Balance measure group? If so, could you confirm a (many to many) relationship exists between the "To Currency" dimension and the Balance measure group after the wizard is finished?

Thanks,

Bryan

|||

Well, my goal is to get the many to many currency conversion working.

One of the problems is that once the wizard is finished, I can see a new "Reporting Currency" dimension created and no relationship exists in Dimension Usage and hence I'm not able to see this dimension through Excel or from any other client.

Thanks,

Arun

|||

I am familiar with currency conversion in theory, but I have not used the wizards. I suspect all you need to do is set a many-to-many relationship between the "To Currency" (cube) dimension and the Balance measure group.

To do this, open the cube editor and select the dimension usage tab. Click on the grey-box at the intersection of the Balance measure group and the "To Currency" dimension. This should give you a button in the right-hand side of this box. Click that button to get the Define Relationship dialog.

In the Define Relationship dialog, set relationship type to many-to-many and set the Rates measure group as the intermediate measure group. If the Rates measure group is not available in the drop down, SSAS can't identify a set of shared dimensions between these two measure groups. This indicates a deeper problem and I suspect you might need to revisit how you've used the Currency Conversion wizard.

BTW, the SSAS 2005 Step-by-Step book covers currency conversion with reasonable detail. That could be a good resource for getting started.

Bryan

|||

I have a ToCurreny dimension (I created this myself. Has relationship only to Rates) + another "Reporting Currency" dimension generated by the wizard.

In all the examples given in the book, they have the above 2 dimensions as the same but in real-world scenario it cant be the same. ToCurrency - Will contain all currencies for which rates are available. They will have to include rates for all transaction currencies in the fact table. (in my case 100 currencies)

Reporting Currency - should include only the set of currencies for which we need the conversion (in my case only some 6 currencies)

But the problem is that, there is no logical relationship b/w Reporting Currency and Rates or Balance measure group as that dimension will only be used for on-the-fly calculation. So, I'm in a fix now to set the dimension usage for this dimension whithout which , this cannot be viewed from the client.

thanks,

Arun

|||

Problem solved.

Solution:

I had created 2 diff measures (so 2 diff measure groups)

1) Rates_ToEUR ==> Will contain rates for all currencies to EUR for all days

2) Rates_FromEUR ==> Will contain rates from EUR to only the reporting currencies

Dimension Usage :

Measure group - Rates_ToEUR -- Currency (same one as used by balance measure group) and Calendar

Measure group - Rates_FromEUR -- Calendar and ReportingCurrency. Also, Balance measure group has many to many relation ship with ReportingCurrency dim through this measure group.

Also, I have simplified the calculation to below :

Scope ( { Measures.[DLY Value Reporting CCY], Measures.[MTD Value Reporting CCY], Measures.[YTD Value Reporting CCY], Measures.[LTD Value Reporting CCY]});

Scope( Leaves([Calendar]) ,Leaves([Currency]), [Reporting Currency].[Currency].Members);

This = Measures.CurrentMember * Measures.[Rate_ToEUR] * (Measures.[Rate_FromEUR], [Reporting Currency].[Currency].CurrentMember) ;

end scope;

End Scope; // Measures

The above solution works perfectly till now. Any one can see any issues?

So, I would suggest implementing currency conversion by yourself without using the wizard (BI) which does not help much.

Cheers,

Arun

sql

Manually defining aggregations

The Aggregation Wizard designed the following aggregation, on 2 dimensions.
I understand it is aggregating at the Country level in the StoreGeography dimension. However, in the Time dimension, since it didn't list any attributes, I am wondering where it is aggregating.

Is it at the All level, or is it at the leaf level ?

Thanks for any help.

<Aggregation>
<ID>Aggregation 7</ID>
<Name>Aggregation 7</Name>
<Dimensions>
<Dimension>
<CubeDimensionID>Store Geography</CubeDimensionID>
<Attributes>
<Attribute>
<AttributeID>Country</AttributeID>
</Attribute>
</Attributes>
</Dimension>
<Dimension>
<CubeDimensionID>Time</CubeDimensionID>
</Dimension>
</Dimensions>
</Aggregation>

That's right: In case and if no attribute specified for a dimension in the definition of the aggregation, the aggregation contains data for dimension on level "All" for the dimension.

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

Manual creation of aspnet database fails "does not allow remote connections"

What do I have to do to get this to work?

C:\>aspnet_regsql.exe -A m -E

Start adding the following features:
Membership

............
An error has occurred. Details of the exception:
An error has occurred while establishing a connection to the server. When conne
cting to SQL Server 2005, this failure may be caused by the fact that under the
default settings SQL Server does not allow remote connections. (provider: Named
Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Unable to connect to SQL Server database.

Hi,

If you're connecting to a remote machine, you will need to turn on remote connection on that server. Please follow this KB article to achieve this.

http://support.microsoft.com/kb/914277/en-us

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

|||SQL Server is set to allow remote connections already. I probably should have mentioned that in the first post.|||

Hi,

Named Pipes Provider, error: 40 - Could not open a connection to SQL Server

Is just a basic connectivity error meaning the client could not connect to the target SQL Server. So just follow the basic connectivity troubleshooting guidelines on our SQL Protocols blog, see:

SQL Server 2005 Connectivity Issue Troubleshoot - Part I

http://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx

and

SQL Server 2005 Connectivity Issue Troubleshoot - Part II

http://blogs.msdn.com/sql_protocols/archive/2005/10/29/486861.aspx

This should help you debug the problem.

Manintenance Plan

I've created a maintenance plan and getting the following error message in the event log. This has been working for a while problem happend a week a ago, no changes to the system. Novice SQL user, any help on this appreciated.

Event ID 208. SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan 'RSS Pro2000 DBMP'' (0xFE3D7C9C154F9E48A4AA953C88D9F97E) - Status: Failed - Invoked on: 2007-03-19 13:28:00 - Message: The job failed. The Job was invoked by Schedule 13 (Schedule 1). The last step to run was step 1 (Step 1).

Are there any changes to the recovery model during this time of execution?

Also check the password or any information pertaining to SQLAgent account used here.

http://www.sqlservercentral.com/columnists/aingold/workingaround2005maintenanceplans.asp fyi.

|||No changes made. SQLAgent using 'Local System' a/c|||

Try to execute the job as manually with your account credential.

Do you have any other databases scheduled in this database?

if so are they getting same error?

|||The Db's are being backed up (there's 4 in total). It's only the transaction log that are not running.I've changed the a/c to 'administrator' and ran another transaction log and getting the same message.|||Have you applied the service pack or any changes to the server recentlY?

Managing reporting services models

Hi everybody,

I have the following scenario: I have web application which is creting new SQL Server database each time when new customer is created in the application.

I would like to give users the possibility of creating ad hoc reports and thus I need to create new connection and report model (and deploy them to my reoport server) each time when the new database is created (report models can not use multiple databases). Does anybody knows how can I do that?

Maybe there is another approach to this kind of problem?

Thank you in advance,

Marek

You can create datasources and autogenerate models from these datasources using the SSRS SOAP API. Check out this article in BOL for information on how to get started using the SOAP API:

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

Management Studio will not allow me to create a view

Hi there,
When I run the following query I get the correct result.
select * from Inventory As I Full Outer Join Publisher As P on
I.ID=P.InventoryID
However, when I try to create a view with the same select statement I get
the following error:
Msg 4506, Level 16, State 1, Procedure InventoyPublisherView, Line 2
Column names in each view or function must be unique. Column name 'ID' in
view or function 'InventoyPublisherView' is specified more than once.
The CREATE VIEW statement I'm using is:
CREATE VIEW InventoyPublisherView AS
(SELECT * FROM Inventory AS I FULL OUTER JOIN Publisher AS P ON
I.ID=P.InventoryID)
Many thanks in advance for the help. Very much appreciatedName the columns in the SELECT statement. Both tables has a column named ID,
and the view cannot
have two columns with the same name.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Chris L" <ChrisL@.discussions.microsoft.com> wrote in message
news:0C10F52D-2845-4F6B-92B7-648A5D1FBC50@.microsoft.com...
> Hi there,
> When I run the following query I get the correct result.
> select * from Inventory As I Full Outer Join Publisher As P on
> I.ID=P.InventoryID
> However, when I try to create a view with the same select statement I get
> the following error:
> Msg 4506, Level 16, State 1, Procedure InventoyPublisherView, Line 2
> Column names in each view or function must be unique. Column name 'ID' in
> view or function 'InventoyPublisherView' is specified more than once.
> The CREATE VIEW statement I'm using is:
> CREATE VIEW InventoyPublisherView AS
> (SELECT * FROM Inventory AS I FULL OUTER JOIN Publisher AS P ON
> I.ID=P.InventoryID)
> Many thanks in advance for the help. Very much appreciated
>|||"Chris L" <ChrisL@.discussions.microsoft.com> wrote in message
news:0C10F52D-2845-4F6B-92B7-648A5D1FBC50@.microsoft.com...
> Hi there,
> When I run the following query I get the correct result.
> select * from Inventory As I Full Outer Join Publisher As P on
> I.ID=P.InventoryID
> However, when I try to create a view with the same select statement I get
> the following error:
> Msg 4506, Level 16, State 1, Procedure InventoyPublisherView, Line 2
> Column names in each view or function must be unique. Column name 'ID' in
> view or function 'InventoyPublisherView' is specified more than once.
> The CREATE VIEW statement I'm using is:
> CREATE VIEW InventoyPublisherView AS
> (SELECT * FROM Inventory AS I FULL OUTER JOIN Publisher AS P ON
> I.ID=P.InventoryID)
> Many thanks in advance for the help. Very much appreciated
It's because you have a column named ID in both tables.
If you didn't use Select * (and you should not) you wouldn't have the
problem. Name the columns.
Besides, why would you select I.ID and P.InventoryID in the query since they
have the same value.|||chris,
use column names in the select list instead of the asterisk:
select i.id, i.col2, i.col3, p.col1, p.col2, etc..
from Inventory As I Full Outer Join Publisher As P on I.ID=P.InventoryID
dean
"Chris L" <ChrisL@.discussions.microsoft.com> wrote in message
news:0C10F52D-2845-4F6B-92B7-648A5D1FBC50@.microsoft.com...
> Hi there,
> When I run the following query I get the correct result.
> select * from Inventory As I Full Outer Join Publisher As P on
> I.ID=P.InventoryID
> However, when I try to create a view with the same select statement I get
> the following error:
> Msg 4506, Level 16, State 1, Procedure InventoyPublisherView, Line 2
> Column names in each view or function must be unique. Column name 'ID' in
> view or function 'InventoyPublisherView' is specified more than once.
> The CREATE VIEW statement I'm using is:
> CREATE VIEW InventoyPublisherView AS
> (SELECT * FROM Inventory AS I FULL OUTER JOIN Publisher AS P ON
> I.ID=P.InventoryID)
> Many thanks in advance for the help. Very much appreciated
>|||Thank you very much, Tibor. I am following exercises from a Wrox book. I
think I will have to shoot the author for writing incorrect code in his
examples.
"Tibor Karaszi" wrote:

> Name the columns in the SELECT statement. Both tables has a column named I
D, and the view cannot
> have two columns with the same name.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Chris L" <ChrisL@.discussions.microsoft.com> wrote in message
> news:0C10F52D-2845-4F6B-92B7-648A5D1FBC50@.microsoft.com...
>|||Thank you very much Raymond. The speed of all of your replies (from all of
you guys) is very reassuring for a total beginner like myself. Fantastic job
.
thanks
"Raymond D'Anjou" wrote:

> "Chris L" <ChrisL@.discussions.microsoft.com> wrote in message
> news:0C10F52D-2845-4F6B-92B7-648A5D1FBC50@.microsoft.com...
> It's because you have a column named ID in both tables.
> If you didn't use Select * (and you should not) you wouldn't have the
> problem. Name the columns.
> Besides, why would you select I.ID and P.InventoryID in the query since th
ey
> have the same value.
>
>|||Thanks Dean. Much appreciated. Like I mentioned in the post above the author
of the book I′m following put incorrect code in his examples. Luckily, ther
e
are great people out there to come to the resuce. Cheers
"Dean" wrote:

> chris,
> use column names in the select list instead of the asterisk:
> select i.id, i.col2, i.col3, p.col1, p.col2, etc..
> from Inventory As I Full Outer Join Publisher As P on I.ID=P.InventoryID
> dean
> "Chris L" <ChrisL@.discussions.microsoft.com> wrote in message
> news:0C10F52D-2845-4F6B-92B7-648A5D1FBC50@.microsoft.com...
>
>|||"Chris L" <ChrisL@.discussions.microsoft.com> wrote in message
news:00CEBECA-7183-436E-BB8A-CA36F3388B23@.microsoft.com...
> Thank you very much, Tibor. I am following exercises from a Wrox book. I
> think I will have to shoot the author for writing incorrect code in his
> examples.
This must be why WROX went bankrupt.
All their authors were shot. :-)