(how frequent) in SQL Server?!

I have used Base SAS for analysis for a while and it was really great.. everything is easy just with a simple command.. I am sure it's not the same in SQL Server but I need some help on how to start with the following:

I have a field called call_country and another field called call_minute. Each call will be saved with the destination country and the total number of minutes..

and I want to run a query to see what are the TOP frequent destinations in this format:

United States - Count: 420 - Total Minues: 12,345

It should be easy in SQL too.

SELECT call_country, COUNT(call_minutes) AS tCount, SUM(call_minutes) AS TotalMinutes

FROM calltable

GROUP BY call_country

|||

If you want use the top, you can do this:

--1.based on total minutes

SELECT TOP (1) call_country, COUNT(call_minutes) AS tCount, SUM(call_minutes) AS TotalMinutes

FROM calltable

GROUP BY call_country

ORDER BY TotalMinutes DESC

--2. based on total count

SELECT TOP (1) call_country, COUNT(call_minutes) AS tCount, SUM(call_minutes) AS TotalMinutes

FROM calltable

GROUP BY call_country

ORDER BY tCount DESC

--3.based on total count and use total minutes as tie break.

SELECT TOP (1) call_country, COUNT(call_minutes) AS tCount, SUM(call_minutes) AS TotalMinutes

FROM calltable

GROUP BY call_country

ORDER BY tCount DESC, TotalMinutes DESC

--4.based on total minutes and use total count as tie break.

SELECT TOP (1) call_country, COUNT(call_minutes) AS tCount, SUM(call_minutes) AS TotalMinutes

FROM calltable

GROUP BY call_country

ORDER BY TotalMinutes DESC, tCount DESC

(Basic?) User-defined hierachy question

Hi Everyone,

I'm pretty new to developing with analysis services & OLAP, but was hoping someone here could point me in the right direction.

I have two dimension tables (Department, Employee), and one fact table (TelephoneCalls). Using a star schema, the two dimension tables are joined by their PK fields through the Fact table.

I'm wanting to drill down through the hierachies set up in Department, but also having the Employee branches populated at the right nodes. (ie a department may have a number of sub-departments, and also employees associated with it).

Based on this information, does anyone know how I could achieve this?

Much Appreciated,

Andrew

Is an employee associated with one and only one department when a telephone call occurs? If so, you may want to have a single dimension table for employee that associates the employee with a department. Otherwise, SSAS 2005 does not support cross-dimensional hierarchies. That said, you might find an interface that lets you structure something like this, and you can always do a cross-join of department and employee in MDX.

Hope that helps,
Bryan

|||

Unfortunately employee's have the freedom to jump around departments, so I can't make a single dimension table for it.

I'll try your suggestions to build it using MDX.

Thanks for your help.

Andrew

|||

"Jump around" as in "with each phone call" or just every few months or so? If it's the later, you might consider implementing a Type 2 slowly changing dimension.

B.

"Invalid authorization specification"

I use SQL Server 2005 Analysis Services Deployment Wizard to generate an xmla deployment script. I get the following error when I try to run it:

<Exception xmlns="urn:schemas-microsoft-com:xml-analysis:exception" />
<Messages xmlns="urn:schemas-microsoft-com:xml-analysis:exception">
<Error ErrorCode="3238395904" Description="OLE DB error: OLE DB or ODBC error: Invalid authorization specification; 28000." Source="Microsoft SQL Server 2005 Analysis Services" HelpFile="" />
<Error ErrorCode="3239182436" Description="Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'Max Min Sales DM 1', Name of 'Max Min Sales DM 1'." Source="Microsoft SQL Server 2005 Analysis Services" HelpFile="" />
<Error ErrorCode="3240034316" Description="Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Product', Name of 'Product' was being processed." Source="Microsoft SQL Server 2005 Analysis Services" HelpFile="" />
<Error ErrorCode="3240034317" Description="Errors in the OLAP storage engine: An error occurred while the 'Brand Name' attribute of the 'Product' dimension from the 'MaxMinSalesDM' database was being processed." Source="Microsoft SQL Server 2005 Analysis Services" HelpFile="" />
</Messages>

In the script, I see the following data sources:

<DataSources>
<DataSource xsi:type="RelationalDataSource">
<ID>Max Min Sales DM</ID>
<Name>Max Min Sales DM</Name>
<ConnectionString>Provider=SQLNCLI.1;Data Source=localhost;Persist Security Info=True;Password=;User ID=;Initial Catalog=MaxMinSalesDM;DataTypeCompatibility=80</ConnectionString>
<ImpersonationInfo>
<ImpersonationMode>Default</ImpersonationMode>
</ImpersonationInfo>
<Timeout>PT0S</Timeout>
</DataSource>
<DataSource xsi:type="RelationalDataSource">
<ID>Max Min Sales DM 1</ID>
<Name>Max Min Sales DM 1</Name>
<ConnectionString>Provider=SQLNCLI.1;Data Source=localhost;Persist Security Info=True;Password=;User ID=;Initial Catalog=MaxMinSalesDM;DataTypeCompatibility=80</ConnectionString>
<ImpersonationInfo>
<ImpersonationMode>Default</ImpersonationMode>
</ImpersonationInfo>
<Timeout>PT0S</Timeout>
</DataSource>
</DataSources>

I am surprised the connection strings don't contain any username/password info since the data source objects have user/password info saved with them when I access the project through Visual Studio. I try manually adding in User ID and Password, rerun, and get a different error:

<root xmlns="urn:schemas-microsoft-com:xml-analysis:empty">
<Exception xmlns="urn:schemas-microsoft-com:xml-analysis:exception" />
<Messages xmlns="urn:schemas-microsoft-com:xml-analysis:exception">
<Error ErrorCode="3238395904" Description="OLE DB error: OLE DB or ODBC error: Communication link failure; 08S01; Shared Memory Provider: I/O Error detected in read/write operation [4]. ; 08S01; Login failed for user 'MinMaxUser'.; 28000." Source="Microsoft SQL Server 2005 Analysis Services" HelpFile="" />
<Error ErrorCode="3239182436" Description="Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'Max Min Sales DM 1', Name of 'Max Min Sales DM 1'." Source="Microsoft SQL Server 2005 Analysis Services" HelpFile="" />
<Error ErrorCode="3240034316" Description="Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Product', Name of 'Product' was being processed." Source="Microsoft SQL Server 2005 Analysis Services" HelpFile="" />
<Error ErrorCode="3240034317" Description="Errors in the OLAP storage engine: An error occurred while the 'Brand Name' attribute of the 'Product' dimension from the 'MaxMinSalesDM' database was being processed." Source="Microsoft SQL Server 2005 Analysis Services" HelpFile="" />
</Messages>
</root>

Any ideas? Thanks in advance!

This is quite common situation you are having: Analysis Server doesnt have permission to access SQL Server relational database.
It takes little while to troubleshoot connectivity problems. You need to make sure you set correctly security credentials required for Anlaysis Server to access relational database.

For one, try and make sure the account Analysis Server runs under can access SQL Server using windows authentication.

There is quite a bit information out there on how to setup connectivity and connection security.

HTH.

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

"Error in Processing Cubes in Analysis Services"

Hi,

While processing the cube in Analysis server, we are getting frequent error saying

"Analysis Server Error: Connection to the server is lost; Time:2/9/2007 3:56:08 PM
Error(-2147221411): Process operation failed; Time:2/9/2007 3:56:08 PM
Error(-2147221423): Could not open server object (Internal (Database 'PMM' could not be opened on the server)); ". help in this regard will be greatly appreciated.

Thanks,

Umesh

Take a look at the log folder that is located under OLAP folder, right next to the bin folder. It is often installed to C:\Program Files\Microsoft SQL Server\MSSQL.2

If you see multiple SQLDump***.mdmp there. And if you see that after any such processing attempt you see new file is created- this is indication of Analysis Server running into situation it cannot recover from, but abort processing operation.

You should install latest service pack for SQL Server 2005 - currently it is SP1.

After, try running unprocess for entire database and then processing all the objects.

If you still see .mdmp files created, contact customer support and show them the repro schenario for the problem you having.

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