in cube we have one role defined. users in that cube should be allowed to see only last 2 days of data. I mean they should be able to browse only last 2 days of data based on current date. How to implement?
View 5 RepliesDoes anyone know how can we prevent users from creating offline/local cube (in Excel) from our OLAP/Analysis Service server ?
Thank you.
I want to implement population data in sales cube.
Fact table has customer code which is foreign key of Customer master dimension which in turn is linked to census data dimension. Census data dimension have city wise population data having foreign keys of zone and state.
We want to add population data in fact table.
I've
1. SQL Server Data Warehouse
2. OLAP CUBE in Analysis Services
My question is - If my SQL Server Data Warehouse is changed (Having Append Data) - Is that My OLAP Cube will have the Append Data?
It's possible, my OLAP Cube always having Append Data if my Data Warehouse is changed? If yes, how to do it without re-deploy and re-process my Analysis Services Project.
So I use Excel 2010 connect to a cube I have built. Then I change some values in the cube via my ETL and re-process the cube. Then I verify that record is NOT there in the fact table - check!
However, when I refresh the worksheet where the pivot table is pulling data from the cube, but that old record wont go away!
Just realized my cube data source on the dev server, was in fact still configured with my local workstation name. Once I updated that, processed the cube all was well.
I have a MDX query , where I have a date Range in where clause.
I want to replace it with Cuurent Date and Last 7 days date.
I tried multiple ways using NOW function , but could not get it correct .
modifying the Query so that I can fetch DATA for last 7 days
SELECT NON EMPTY { [Measures].[X] } ON COLUMNS, NON EMPTY { ([PRODUCT].[PRODUCT].[PRODUCT].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM ( SELECT ( { [COLOR].[COLORName].&[BLACK], [COLOR].[COLORName].&[BLUE] } ) ON COLUMNS
FROM ( SELECT ( [Date].[Calendar].[Calendar Year].&[2015].&[2015]&[3].&[7].&[20150706] : [Date].[Calendar].[Calendar Year].&[2015].&[2015]&[2].&[6].&[20150629] ) ON COLUMNS
FROM [MYCUBE]))
I want to replace Date Hard code value , I have used Calendar Hierarchy of date dimension. to find Last 7 days Data.
I am looking for some Simple/Best Data Visualization tools for Desktop which accept data from SSAS Cubes.
View 5 Replies View RelatedIs there any way to copy my Data of 2015 to the Planning/Forecasting Value of 2016?
My question is based on that we use INFOR ION BI right now and there we can just add an Button in our reports wich physically copies the value from one year to the next year based on some other rules in the cube.
Now I need to make this example work with SSAS and Excel PivotTables but I cant figure out how.
I have absolutely no clue where and how to accomplish it. Do I use Calculations, do I use Actions, do i make it in the Dataview, Cube or directly in Excel?
I have problems creating a cube with AMO.
I can add the cube to the database object and fill it with dimensions and a measuregroup (see code below).
If I call cube.Update() it says something like "Error in meta data manager. Cube has no measuregroups." (getting the message in german language)
The error in Microsoft.AnalysisServices.OperationException.Results.Messages is -1055653629
I can't find any documentation about this (or any other) error code in Microsoft documentation.
Here's my Code:
Cube newCube = database.Cubes.Add("MyCube","MyCube");
newCube.Language = 1031;
newCube.Collation = "Latin1_General_CI_AS";
CubeDimension dim = newCube.Dimensions.Add("dim1","dim1","dim1");
CubeAttribute attrib = dim.Attributes.Find("dim1Attr1");
[code]....
I am facing a very weird issue in our SSAS database. We have several roles with 'Read definition' access. None of these roles are able to see data in the cubes. I have checked these roles over and over and there is no problem with the definition. Each of the roles have been given read access to Data Source, Cube and Dimensions.
The users are able to access the definition and structure of the cube i.e. they can see the measures & dimensions available but when they drag measures and dimension attributes in the browser (SSMS) OR execute an MDX - they get null values. The roles with 'Full Control (administrator)' access are able to see all the data without any issues.
I have tried the following:
1. Deleted all roles and re-created.
2. Created roles directly on SSAS DB.
3. Deployed all objects and processed the DB.
Each time only the admins are able to see the data.
We have an OLTP database and operational reporting is carried out on a replica server / database. We have plans to build a new data warehouse and an analysis services cube.
Question 1:Should a cube be designed to extract data from a physical star schema rather than a logical one (3NF relational (ODS?) using a data source view to derive the star)? I'm guessing for performance it's better to pull data from similar structures (physical facts and dimensions as required by analysis services) but is the difference significant?
Question 2:Depending on the answer to q1, is it bad practice to ETL data from a staging database (replica > staging) directly to a star schema (multiple data sources and cleansing / business rules required)? Or should it be processed from staging to an ods and only then to a star schema (physical or logical). I still don't know if an ODS is required but I guess the consideration for this decision is whether the business would require daily operational (or ad hoc) reporting on the consolidated data sources (without needing historical DW functionality).
I created SSAS cube in VS 2008 and have been able to deploy it successfully to the server. While creating the cube I was able to browse dimensions and all underlying tables just to make sure it has data. After deploying successfully when I drag and drop any measure group to browser it does not display anything.
The only thing I did different from straightforward cube building process was that when I created those measure groups the partitions that were created by default were giving me some unknown errors so I had to delete them in order for cube to process successfully.
Did that made any difference because I thought partitions are for improving query performance and has nothing to do with cube processing errors.
While web hosting I use Sql Server 2000 as the database. Imagine I have hosted 3 Web Sites. All these 3 users want to modify/update their designs. What I did is I created respective 3 users having access to the respective databases only. So that they can registerd the ip and add to the Sql Server 2000 Enterprise Manager. These users are able to access their own databases only. But all these 3 users are able to access the default databases like master,pubs etc. How to restrict this.
Also suggest which is the optimal way to give control to the respective users while using Sql Server 2000.
====Suresh, P.R, Postal Training Centre, Mysore.
I have a cube that we are processing nightly via an Analysis Service Processing Task in SSIS. In order to increase the performance of the processing time, we elected to use a lot of rigid dimension attributes, and do a full process of everything in the SSIS task. The issue that I am having is that after that task completes, I need to go into Visual Studio to deploy the cube becuase we are unable to browse or use the cube. This issue seemed to start once we changed the SSIS Analysis Service Processing Task to do a full process on the dimensions, rather than an incremental.
I would expect that once development is done, and it is processed and deployed, that is it. My thinking is that the SSIS task should just update the already deployed cube,
Hi
I'm looking to deploy some SQL Server reports and I want to restrict the access that the users have. Currently when connecting to the reports site they have access to a lot of functionality through the header bar, for example
- Properties
- New Folder
- New Data Source
- My Subscriptions
- Site Settings
- Search
etc.
How can I disbale or hide all these options so that all the user sees is the list of reports?
Thanks in advance
Mark
Is it possible to restrict the Users to Export Report Results only to Excel ?
So instead of the default drop down options of csv, pdf, tiff,Web Archive,Web Archive etc... can we get it to just display Excel.
Any help would be appreciated.
Hi,
I am developing a report using SQL Server Reporting Services 2005. Now I need to restrict the Export to Excel functionality for some users, but allow it to others, on a given report (Not all reports). Please let me know if it can be done, and if Yes, then How?
Thanks & Regards,
Rahul
Hi,
I would like to restrict users to allow to view related reports only...
like i have 2 users..with 4 reports
1 user can see only two reports
2 user can see only another two reports
how can i do this by SSRS???
Can i do by using Configure item-level role definitions in seetings link..
or is thr any another way to do this?
Pls reply back
Regards,
Maruthi..
I am trying to configure the reporting for TFS using SQL Server. But I get following error when viewing any report:
So I try to manually process the cube to check if it works. I am following this article: [URL] ....
When I click on GetProcessingStatus and invoke it (with last field set as TRUE) I get following error:
How to resolve this issue and be able to see the reports.
The logic of MDX eludes me oftentimes... Why the subcube query below will not return the expected result. I'm using the SSAS browser to test a calculated member, and found the query it is generating uses several layers of sub-queries. I've managed to replicate the issue in a simpler query from 2008 Adventure works:
with member x as
count(existing ([Date].[Date].[Date]))
select non empty { x, [Measures].[Order Count] } on 0
from (select ([Date].[Date].&[20080601] : [Date].[Date].&[20080602]) on columns from [Adventure Works] )
Order Count returns the right number, 287 for the 2 days that are in the subcube. x however, returns 2191 instead of 2.
I've tried a lot of variations using filter and descendants, have also tried different levels of the dimension such as [Date].[Date]. I get some different numbers, but never 2.
If I include the Date hierarchy on axis 1 (rows), I get the right answer of 1 day for each row, and there are only 2 rows. But if I use any other dimension on rows, it repeats the value 287 for order count, and whatever strange number there is for x.
How do I get this count of days to return 2 ?
I would like to count the working days of a spesific time range and then find the Average Daily Dispatches.Currently the time range is at WHERE statement. how to count the dates in a month range. I prefer not to add a new calc Member.
WITH MEMBER [Measures].[Working Days] AS
COUNT(Date.[Working Date].&[1])--Doesn't work
MEMBER [Measures].[Average Daily Dispatches] AS
[Measures].[Total Dispatches]/[Measures].[Working Days]
SELECT [Measures].[Average Daily Dispatches] ON 0
FROM [cube]
WHERE (
[Date].[Month].&[2015-01-01T00:00:00]:[Date].[Month].&[2015-08-01T00:00:00]
);
Hi,
I was talking to my boss to day and our report request are not very consistant. We always having someone coming back to change something in our report. We were thinking of useing something called the Cube Analysis. Then it give our employees the raw data for them to run any standard query for themself. We have folks that want a report one way, but then they changed their minds and we are creating yet another report 4 or 5 times. what are your thoughts about this type of database?
I am trying to get a cumulative count of business days. I have a column in my date dimension BusinessDayInd which is 'Y' or 'N'. I have been trying to create a calculated column in the cube but have not been successful yet. I think I'm close with this:
COUNTROWS(FILTER(DATESMTD(PostingDate[Posting Date]), PostingDate[BusinessDayInd] = "Y"))
However, this is giving me a count of calendar days for all business days and null for non-business days. How do I apply the filter to DATESMTD? I've tried many iterations of this using CALCULATETABLE, CALCULATE, and FILTER. All are giving me the same result.
What am I doing wrong?
I'm making my first attempt at creating a cube using Analysis Services based on my exisiting datamart. Datasource, views, and dimensions have been defined. But comes deploying the cube, it's giving the error saying "A connection cannot be made. Ensure that the server is running." The Deploy Target server and database are the same where my datamart is. Or, maybe I don't know what I'm doing.
Would appreciate any suggestion for my enlightenment. Thanks
I setup Hiarchy in the dimentions of my cube, however when I go and look at it via proclarity, I can't see the hiearchy there.
I have one table that has:
Category
Subcategory
Partnumber
And I have the hiearchy set from Subcategory to partnumber.
Then I go into proclarity and limit the category by "my catname" and I still see the 8000 partnumbers in the list.
Any ideas?
I have a (hopefully typical) problem when it comes to cube design. Westore millions of product records every year, broken down bymonth/quarter. Each product can be assigned to various heirarchialclassification groups etc. The data in an OLTP DB occupies roughly100G for a typical year.We're looking at breaking this out into OLAP to provide faster accessto the data in various configurations and groupings. This is not aproblem, as this is the intended use for Analysis Services.The problem is that we apply projection factors on the product pricesand quantities. This would be ok if it only happened once, however,this happens every quarter (don't ask why). The projection factorschange 4 times a year, and they affect all historical product records.This presents a challenge because to aggregate the data into a usefulconfiguration in the cubes, you throw out the detail data, but thismeans throwing out the price and quantities which are needed to applythe projection.So if you have Product A at $10 and Product B at $20, and roll both upinto Category X, you'll have $30, but you'll lose the ability to applya projection factor of .5 to Product A and .78 to Product B. They'rerolled up.I don't want to regenerate the cubes every 3 months. That's absurd.But we can't live without the ability of projection theprices/quantities on a product level (detail level). So how can thisbe achieved when the other cubes are created at a higher level withless details and sums of the detail data?My initial guess is that we have to update the product data, and thenreaggregate all the other data that is built upon that product data.Is there any other way to apply math to the data on the way out?Thanks in advance!Regards,Zach
View 2 Replies View Relatedi want to create cube withe 2 fact tables. is it possible?? if it is could you show me how !!
View 2 Replies View Relatedhow can use this mdx script in the calculation part of a cube, will i simply dump it in the script form by starting with the 'create member current cube.
[measures].[test]'
select
[measures].[abc] on 0,
[xyz].[xyz].(&0):[xyz].[xyz].(&60) on 1
from
(
select
(tail([month].[month].[month].members,6))on 0
from
[cube])
The data attached below is from a Fact table. When this data is browsed in the Cube the end user is only interested in value of Measure 1 when it is not equal to zero. Measure 1 is a base measure .how to suppress the value 0 for Measure 1 in the Cube.
DimesionKey1
DimensionKey2
DimensionKey3
Measure 1
Measure 2
Measure 3
[code]...
I know where the data files are etc, but what is the physical structure of a cube like on disk?
Logically we see the data as a star. Is the physical file akin to a star also? Or is a single file a measure group containing all the required member and measure data - thereby eliminating the need for physical join operators.
I am planning to implement the security in my cube..
Total sales is 1000..
Customer Sales
A 100
B 200
C 300
D 400
Total Sales 1000
When i want to give access to the customer A, he can capabule of viewing only A value 100... But the problem is A is able to see total sales 1000.. How can i restrict the sales a value to 100 in the cube.. Where i have to set the property to fix this issue.....
Is it correct to say that for each cube you can have only one Fact Table? I am having a funny dispute just now.
According my experience I never built cubes with more than one fact table, if I want take data from more than one table I write a view and I use it as Fact table...but a cube with two or three fact tables? Never tried..
i am currently trying to build a distinct count on my cube (mssql2005 analysis services).But after i added the discount count on the field i want to and start the processing, the following errors appear.Errors in the OLAP storage engine: The sort order specified for distinct count records is incorrect.
View 17 Replies View Related