Showing posts with label cube. Show all posts
Showing posts with label cube. Show all posts

Thursday, March 29, 2012

count only records from left table ROLLUP or CUBE

Hello,

Using rollup I want to count the number of rows of a table
called Table1 which is LEFT JOINED with a table called Table2.

The problem is that we can have more than 1 rows in Table2 that
matched 1 row in Table1. As a consequence the count of rows in table1
is bigger than the real number of rows contained in table1.

For example:

Select COUNT(Table1.employeeID), Table1.country
FROM Table1
LEFT OUTER JOIN Table2 ON Table1.phone_number = Table2.phone_number
GROUP BY Table1.country WITH ROLLUP

that works but give me a COUNT higher than the truth because I can have
several times the same phone number in Table2!

COUNT(DISTINCT...) would work if WITH ROLLUP not used BUT I want
absolutely to use ROLLUP.
Does someone know a workaround?

Help much appreciated.In your example, the workaround is very easy: remove the join with
Table2, because Table2 is not used at all in the query (there are no
conditions on this table, no columns selected from it; the only effect
of joining this table is the higher count, that you do not want).

Please post a query that is closer to your requirements, along with
DDL, sample data and expected results, as documented in:
http://www.aspfaq.com/etiquette.asp?id=5006

Razvansql

Tuesday, March 27, 2012

count measure not working as expected

Hi

I have diamension Calender in my cube. I have a table called incident which is working as both measure as well as diamension. There is no relation between the calender and incident tables in the database. When i selected the incident table to be a measure using the cube creating wizard it added a measure attribute called incident count inside the incident measure. I have heirarchy defined in the calender diamension as year-month-date. What i did was dropped the incident count measure in the data area while browsing the cube. And i added the

year-month-date heirarchy to 'drop row field here' area.

Then i went into the diamension usage and defined a relation between Calender diamension and incident measure using the incidentDate field in incident table and Date field in the Calender table. Then i deployed the cube and browsed. What i get is count the total number of incidents in the incident table in front of all the years and months.

Can anybody suggest what i am doing wrong. I want a count of incidents to be displayed for every month or every year.

Thank you.

-padu.

Sounds like you have not defined attribute relationships in your Calendar dimension. With the attributes you mentioned in your previous post Date should have a relationship to month and month should have a relationship with year. Assuming that date is the key attribute you would also need to ensure that the year is not related to the date as this redundant relationship could also cause issues.sql

Saturday, February 25, 2012

could not deploy the cube

hi hannes,

i tried the option you had told me

now i am getting that i dont have permission to create a object in the server i.e, the client on which i am wroking has no permission

and i dont know how to give it...

could you help me with this

lalitha

Hi Lalithak,

You would use Manage Roles option in your Analysis Services Database.

Good Coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

|||

Javier Luna wrote:

Hi Lalithak,

You would use Manage Roles option in your Analysis Services Database.

Good Coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

is it in server or client..

lalitha

|||

Hi Lalithak,

It is in your server.

Good Coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

|||

See my reply in http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=676039&SiteID=1

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

could not deploy the cube

hi guys,

i have a problem in deploying the cube in sql server 2005 ... i had no permission for creating the object in the server...

i got a response as.....

Hi Lalithak,

You would use Manage Roles option in your Analysis Services Database.

Good Coding!

but i didnt get where will i get this option in server or in client....

lalitha

Use SQL Server Management Studio to connect to your Analysis Server. See if you permission to create a database.

If not, ask for your server admin for the following:

Create a new database for you.
Add a new role to the database and grant the role DB admin permissions.
Add you as a memeber of the role.

How you should be able to deploy your cube. Make sure deployment options in your BI Dev Studio target a new database.
It is also a good idea to check your deployment is not going to override the DB admin role.

Hope this helps.

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

Tuesday, February 14, 2012

corrupted file error

I think one of my file got corrupted when I tried to clean up my dsv and dimension since I got the following message when try to open the cube:

Errors in the metadata manager. An error occurred when loading the
DimProduct dimension, from the file, '\\?\E:\Program Files\Microsoft SQL
Server\MSSQL.2\OLAP\Data\Financial.0.db\DimProduct.4.dim.xml'.
Errors in the metadata manager. An error occurred when loading the
RevenueAnalysis cube, from the file, '\\?\E:\Program Files\Microsoft SQL
Server\MSSQL.2\OLAP\Data\AW.0.db\AW\View.26.cub.xml'.

What is the easy way to fix this, I have the back up copy or xmla script, but it won't allow me to run it. I can not even delete the AS database since I am thinking about restore from my backup

Thanks

Don

MS tech support claims this normally goes away after a full process of the database but I haven't found this to be true.

I had to shut down the SSAS service, delete all files within the data directory of the particular database with the problem, start back up the service, then delete the database via SSMS. After this I just re-deployed from our source control and did a full process.

Hasn't happened since our SP2 upgrade (fingers crossed).

Hope this helps.