OWC scans all partitions (SSAS 2005)

SQL Server Analysis Services

Index ‹ SQL Server

- Previous
  - 1
    - Need help with a sp Hi, The question is pretty vague. Can you please explain more what the context is of your problem. What do you want if you have records like this: TABLE FRUITS Id Value1 Value2 1 Apple Banana 1 Apple Melon 1 Apple null 1 grape null 2 Plum Melon 2 Plum null 3 berry null Which rows do you want to be returned for ID=1 Greetz, Geert Geert Verhoeven Consultant @ Ausy Belgium My Personal Blog Tag: OWC scans all partitions (SSAS 2005)
  - 2
    - How can I generate INSERT statements from Management Studio No, you can’t SSMS does not support that yet. You can create the statements using the script from here: http://vyaskn.tripod.com/code.htm#inserts Jens K. Suessmeyer --- http://www.sqlserver2005.de --- Tag: OWC scans all partitions (SSAS 2005)
  - 3
    - how to design database for chinese and japanese characters Moving to SQL Server Setup and Upgrade Tag: OWC scans all partitions (SSAS 2005)
  - 4
    - Get the error in dos cmd line I really need to know if there is some variable that gives me the sql error because when I execute the cmd, if there's an error the error is printed int the cmd window. The errorlevel var just give me the number "1" but I would like to get something like "An error in database ocurred due to database already exists" or something like that. Please I'll apreciate any ideias you may have Tag: OWC scans all partitions (SSAS 2005)
  - 5
    - SQL Server roles & ASP.NET 2.0 Forms Authentication Roles I think this might help: http://msdn2.microsoft.com/en-us/library/ms998317.aspx Tag: OWC scans all partitions (SSAS 2005)
  - 6
    - Data-Driven Subscription to Excel with labeled worksheets Question A few months ago there was a thread on this: http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1713177&SiteID=1 which notes that out-of-the-box, SSRS does not support named tabs when multiple pages are output to Excel. That said, Lisa Nicholls recently provided a link at the end of the thread to her blog that discusses how to achieve your goal using XML output and XSLT. You might give this a try to see if it works for you. http://spacefold.com/lisa/post/Changing-the-Sheet-names-in-SQL-Server-RS-Excel-QnD-XSLT.aspx Scott Tag: OWC scans all partitions (SSAS 2005)
  - 7
    - SSAS Best Practice It depends on the situation, but what type of layer were you thinking of Tag: OWC scans all partitions (SSAS 2005)
  - 8
    - distribution cleanup cannot clean up snapshot folder open up your distribution clean up task job, and in the job step properties do the following: setuser 'sqlrepadmin' GO EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 72 GO Tag: OWC scans all partitions (SSAS 2005)
  - 9
    - define/set parameter values in Management Studio? Unfortunately, I don't believe their is an easy and straightforward way to do this. About the only option I've been able to find is wrapping the MDX query in an XMLA query, which allows you to have parameters and define their values. The problem with this approach is that the result of the XMLA query is an XML response which contains a lot of metadata as well as the data (but it is not in any type of format that would allow you to easily look at just the query results). Here's a link to a topic in BOL that shows an example of this: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/mdxref9/html/a4754d16-d9c4-49f6-9be0-392180b912e4.htm If your query is a relatively simple one that returns a relatively simple result, this approach might work... HTH, Dave Fackler Tag: OWC scans all partitions (SSAS 2005)
  - 10
    - Best practices - currency elements Could this be a problem with the specification of the database column For example: Code Snippet declare @testo table(myTotal decimal , dec_9_2 decimal(9,2) ) insert into @testo select 5, 3 --select myTotal from @testo select myTotal from @testo for xml path('') /* XML_F52E2B61-18A1-11d1-B105-00805F49916B ----------------------------------------- <myTotal>5</myTotal> */ select dec_9_2 from @testo for xml path('') /* XML_F52E2B61-18A1-11d1-B105-00805F49916B ----------------------------------------- <dec_9_2>3.00</dec_9_2> */ In the first query the source column is simply defined as a DECIMAL column and is displayed without any fractional "decimal" portion. When this column is converted to XML it displays only the whole number portion because really, the data consists of whole number only. In the second query the source column is defined as DECIMAL (9, 2) column. This provides for 7 whole number digits and 2 decimal digits. When this column is converted to XML it displays the desired decimal places. Can you provide the DDL for your source column Tag: OWC scans all partitions (SSAS 2005)
  - 11
    - SQL Command Datareader Do you need to use the DataReader Tag: OWC scans all partitions (SSAS 2005)
  - 12
    - suser_sid returns value for nonexistent login sp_adduser depends on being able to access the (deprecated) catalog views in master DB in order to work. Some restrictions to access catalog views are new in SQL Server 2005, and old (deprecated) code does not work when there are restrictions on them. Most likely the login exists, but the principal executing sp_adduser doesn’t have permission to access the metadata for the login. I would recommend trying the following code to verify the existence of the login: CREATE USER [COMMERCIAL DIRECT] go The CREATE USER DDL doesn’t rely directly on the catalog views, and given that suser_sid returns a non-null value, it should work. If the statement above works, then most likely the principal you are using to create the users doesn’t have permission to access the catalog view. You can also verify if the login exists using a privileged (i.e. sysadmin) account. SELECT * FROM sys.server_principals WHERE sid = suser_sid ( N 'COMMERCIAL DIRECT' ) I hope this information helps. -Raul Garcia SDE/T SQL Server Engine Tag: OWC scans all partitions (SSAS 2005)
  - 13
    - Count the occurance of a character in a string select len(@s) - len(replace(@s, 'a','')) Tag: OWC scans all partitions (SSAS 2005)
  - 14
    - What technique to use to update production table from staging table 1: Probably not. 2: You could use sp_rename. You'd have to drop the other table first. 3: The checksum function might help with this, but the built-in SQL Server function tends to have a high collision rate (same checksum value for different input values). You also need a primary key to compare on. So you'd do a join between the tables on the primary key, run all the columns from each table into its own checksum function, and include a WHERE clause looking for checksum values that don't match. Not sure how good performance will be. It helps if you can modify the table definition to include the checksum value as a column, so you don't have to do the calc each time. Hope this helps. Tag: OWC scans all partitions (SSAS 2005)
  - 15
    - truncation error message Hi Have you tried adding "Derived Column transformation". Worked for me. In "Derived Column" select Replace '<your column name>' In Expression - SUBSTRING(<your column name>, 1, 50 ) This should truncate your column to the appropriate length NB Tag: OWC scans all partitions (SSAS 2005)
  - 16
    - Error in executing stored proceduNot a valid identifier Hey I have written the following the stored procedure and executed it.But i am getting the following error. I don't know the reason for this. set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go Create PROCEDURE [dbo] . [GSU_Site_ReterieveActiveSitesOnSearch] @whereClause nvarchar(2000) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. declare @sqlstr as varchar ( max ) set @sqlstr = 'SELECT Site.siteid as siteid,' set @sqlstr = @sqlstr + 'Site.Sitename as sitename, ' set @sqlstr = @sqlstr + 'Customer.customerid,' set @sqlstr = @sqlstr + 'Customer.customername as CustomerName,' set @sqlstr = @sqlstr + 'Site.City as City,' set @sqlstr = @sqlstr + 'site.Address as Address,' set @sqlstr = @sqlstr + 'Site.state , ' set @sqlstr = @sqlstr + 'Country.countryid as countryid,' set @sqlstr = @sqlstr + 'Country.countryname as country,Businessunit.businessunitid ,businessunit.businessunitname as BUName,' set @sqlstr = @sqlstr + 'SystemType.SystemTypeID,SystemType.SystemTypeName FROM Site INNER JOIN Country ' set @sqlstr = @sqlstr + 'ON Country.countryid = Site.countryid INNER JOIN Customer ON Customer.customerid=Site.customerid ' set @sqlstr = @sqlstr + 'INNER JOIN Businessunit ON Businessunit.businessunitID=Site.BusinessUnitID INNER JOIN SystemType ON ' set @sqlstr = @sqlstr + 'SystemType.SystemTypeID=Site.SystemTypeID INNER JOIN GSUStatus ON Site.GSUStatusID=GSUStatus.GSUStatusID ' set @sqlstr = @sqlstr + @whereClause -- --set @sqlstr=@sqlstr+' WHERE GSUStatus.GSUStatusID=' +@GSUStatusID --if @BusinessUnitID <> 0 --set @sqlstr=@sqlstr+'and site.BusinessUnitID ='+@BusinessUnitID --if @CountryID <> 0 --set @sqlstr=@sqlstr+'and site.countryid='+@CountryID --if @CustomerID <> 0 --set @sqlstr=@sqlstr+'and site.customerid='+@CustomerID --if @SystemTypeID <> 0 --set @sqlstr=@sqlstr+'and site.SystemTypeID='+@SystemTypeID --if @SiteName <> '' --set @sqlstr=@sqlstr+'and site.Sitename like ' + @SiteName --if @Address <> '' --set @sqlstr=@sqlstr+'site.Address like '+ @Address --if @City <> '' --set @sqlstr=@sqlstr+'site.City like '+ @City --if @State <> '' --set @sqlstr=@sqlstr+'and site.state like '+ @State print @sqlstr exec @sqlstr END I executed the procedure by pasing parameters Exec [GSU_Site_ReterieveActiveSitesOnSearch] " where GSUStatus.GSUStatusID=1 and site.Sitename like 'lakshmisite' " and getting the following error - exc {"The name 'SELECT Site.siteid as siteid,Site.Sitename as sitename, Customer.customerid,Customer.customername as CustomerName,Site.City as City,site.Address as Address,Site.state , Country.countryid as countryid,Country.countryname as country,Businessunit.businessunitid ,businessunit.businessunitname as BUName,SystemType.SystemTypeID,SystemType.SystemTypeName FROM Site INNER JOIN Country ON Country.countryid = Site.countryid INNER JOIN Customer ON Customer.customerid=Site.customerid INNER JOIN Businessunit ON Businessunit.businessunitID=Site.BusinessUnitID INNER JOIN SystemType ON SystemType.SystemTypeID=Site.SystemTypeID INNER JOIN GSUStatus ON S' is not a valid identifier."} System.Exception {System.Data.SqlClient.SqlException} Please let me know the problem in this. Thanks Kusuma Tag: OWC scans all partitions (SSAS 2005)
- Next
  - 1
    - SQL Server Mobile Mark, Your're right. The Database Diagram tool in the Server and Object explorers do not work with SQLCe databases. While you can create relationships using SQL statements, you might simply want to create a new Data Source in VS and add several related TableAdapters. The relationship tool in the TableAdapter Configuration Wizard does work. I describe how this is done in my EBook. See www.hitchhikerguides.net . hth Tag: OWC scans all partitions (SSAS 2005)
  - 2
    - Conditional Bulk insert Try INSERT INTO Categories SELECT CategoryId, @LastUpdate FROM OPENROWSET ( BULK @filePath , FORMATFILE = @formatFile, FIRSTROW = 2 ) AS a where a.CategoryID not in (select distinct CategoryId from Categories ) Tag: OWC scans all partitions (SSAS 2005)
  - 3
    - XML Configuration Issue Hi Matt, Do you have multiple script tasks in your package. Can you provide more info on the composition of the package and where this variable is being used. Thanks Tag: OWC scans all partitions (SSAS 2005)
  - 4
    - Management Studio and Enterprise Manager on Same Machine? Yes, it's OK, I've done it. But it's not recommended for a production machine. Uninstalling either may cause either to not function properly. Tag: OWC scans all partitions (SSAS 2005)
  - 5
    - How to do this in SSIS? You could use a Derived Column transformation to do this, but what is it that you want to do with the values A Derived Column transformation would work if you always have the same three "words", "abc", "xyz" and "temp", because it would allow you to create one output column for each "word". It would not allow you to create columns with dynamic names. Tag: OWC scans all partitions (SSAS 2005)
  - 6
    - How to get sql server name with a vb-script? I have a script in vb .net but it doesn't target your request Imports Microsoft.SqlServer.Management.Smo Dim dt As DataTable dt = SmoApplication.EnumAvailableSqlServers For Each dr As DataRow In dt.Rows MsgBox(dr(0).ToString) Next Tag: OWC scans all partitions (SSAS 2005)
  - 7
    - "connection timeout or connection error" when trying to connection analysis services 2005 to sharepoint office data connection the AS and Sql server are on 1 server and the sharepoint is on 2nd server. the ODC file is working fine in excel. i have office 2007 and sharepoint 2007 with sql server 2005. When trying to upload the ODC to the data connection library im getting the error. Please URGENT help is needed Tag: OWC scans all partitions (SSAS 2005)
  - 8
    - Can anyone help explain 'XactSequence' in profiler trace transaction sequence. Tag: OWC scans all partitions (SSAS 2005)
  - 9
    - Problem with Date Function ? This has to do with the way that datetime data is stored. Try this: Code Snippet select * from someTable where date >= convert(datetime(convert(varchar(10), getdate(), 101))) and date < convert(datetime(convert(varchar(10), getdate()+1, 101))) or Code Snippet select * from someTable where date >= '6-4-2007' and date < convert(datetime, '6-4-2007')+1 The problem with using the BETWEEN operator is that it includes events that occur exactly on midnight which is technically part of the next day. There are a number of other ways of doing this in addition to these two. Something that you ought to avoid is loading either GETDATE() or your date constant '6-4-2007' into a variable. Loading this data into a variable eliminates some optimization options that might be exploited by the SQL optimizer. Tag: OWC scans all partitions (SSAS 2005)
  - 10
    - How do I find Database Designer in 2005 Managment Studio? I believe that functionality was moved to BIDS or VSTS and is NOT in SSMS. Tag: OWC scans all partitions (SSAS 2005)
  - 11
    - Restore Express 2005 to Server 2000 The database files, and backup formats, are not compatible with earlier versions of SQL Server. The only option is to output the objects and data as scripts and recreate the SQL 2000 database from the scripts. You could use the Database Publishing Wizard for the task. Database Publishing Wizard http://www.microsoft.com/downloads/details.aspx familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en And there are third party tools that would also do the task. Check ApexSQL's SQLDiff and RedGate's SQLCompare -I think that they have fully featured 'eval' editions. Tag: OWC scans all partitions (SSAS 2005)
  - 12
    - Page Header + Export to Excel One suggestion is to have 2 versions of the report, one with headers and one without. On your report with headers, you can setup a link in the header or footer: "Export Friendly" or something of that nature. Then you can jump to URL in a new window, open the non-header report, passing all of the parameters over in the link. Just an idea. BobP Tag: OWC scans all partitions (SSAS 2005)
  - 13
    - SQL2000 doesn't appear in server explorer Right click on your existing connection and either add new or change the server, I remember my local SQL Server 2005 developer edition was not in my server explorer I had to add it to what VS2005 team suites thinks was available. Hope this helps. Tag: OWC scans all partitions (SSAS 2005)
  - 14
    - Rank questions The TOPCOUNT is often used for this kind of thing. It's like performing ORDER and HEAD function calls against a set. Here is an example (also leveraging the GENERATE function) to assemble a set along the lines of what you described: Code Snippet with set [Top Products By Month 2003] as GENERATE ( [Date].[Calendar Year].&[2003] * HEAD ([Date].[Month of Year].[Month of Year]. Members ,3), ([Date].[Calendar Year]. CurrentMember ,[Date].[Month of Year]. CurrentMember ) * TOPCOUNT ([Product].[Product].[Product]. Members ,5,[Measures].[Reseller Sales Amount]) ) select {[Measures].[Reseller Sales Amount],[Measures].[Internet Sales Amount]} on 0, [Top Products by Month 2003] on 1 from [Adventure Works] To keep things simple, I moved the row set definition to a named set at the top. In the first half of the GENERATE function, I assemble the set of the first three months of calendar year 2003. The GENERATE function allows me to loop through each member of that set. Each pass of the loop executes the second half of the function to determine the top 5 products for the given year-month based on Reseller Sales Amount. The assembled set is placed on the rows with Reseller Sales Amount and Internet Sales Amount on the columns. Hope that helps give you an idea on how to tackle this problem. B. Tag: OWC scans all partitions (SSAS 2005)
  - 15
    - Problem to Drop database in SQL server 2005 Hi, you did not mention the actual error, but perhpas this entry in my blog will help you: http://www.sqlserver2005.de/SQLServer2005/Default.aspx tabid=56&EntryID=9 Jens K. Suessmeyer. --- http://www.sqlserver2005.de --- Tag: OWC scans all partitions (SSAS 2005)
  - 16
    - issues with pull subscriptions Is this transactional or merge If it is transactional, have you rerun snapshot Tag: OWC scans all partitions (SSAS 2005)

NilgunCelikok

Hello,

I have a cube with three partitions. Each partition holds data for the years 2005,2006 and 2007 respectively. In our web application we are creating OWC Pivot Table and putting "date dimension" in the filter area automatically. Altough only "2007-01-01" is included in filter, when we trace the query from profiler we saw that all partitions are read. Is there anyone encountered the same thing

By the way, storage modes for all partitions are MOLAP.

Nilgun Celikok

Top

Re: OWC scans all partitions (SSAS 2005)

Christopher Webb

Hi Nilgun,

First of all, have you set the Slice property on your partitions Although in general AS is able to detect what data is in each partition, read the following article for details on when it will and won't work:

http://blogs.msdn.com/sqlcat/archive/2007/03/05/ssas-partition-slicing.aspx

In your case it sounds like it will be quite straightforward to set the slice, and this will also detect whether your partitions hold the data you think they hold (which could be another reason why you're seeing AS scan all partitions).

If you have set the slice, however, you could be running into an issue I've seen before and which I think is fixed in the latest cumulative hotfix build for SP2:

http://support.microsoft.com/kb/936305

Look for the brief description under issue 50000890.

HTH,

Chris

Top