Analysis :: Show Dimension Names In MDX Query Result

Oct 28, 2015

I am facing an issue in MDX Query. I have a custom MDX query, When I run the query I am getting results too. Now my requirement is to show the dimension names in the query result, So that I can get those header names in the cell set itself. Please see the below image.

In this image I need to show 'Fiscal Year' and 'Fiscal Quarter' in that highlighted area. Is there any custom query for this?

View 3 Replies


ADVERTISEMENT

Analysis :: Show Only Ultimo Values In Date Dimension?

Aug 28, 2015

I've a table which contains a record for each day an employee is employed:In the cube  I've create a measure which is a sum of the field "Employed"  which provides me the number of employees that are employed for a given date (lowest level of my time dimensions which are YEAR-MONTH-DATE)The problem is that the number aggregating on YEAR and MONTH which provides me with wrong figures at these levels - So how can create a calculated measure (maybe with Scope) that only show the Ultimo Numbers at the lowest level when I am browsing on the Month or Year level. For example if I am on the Year level I only want it to sum on the date 31-12-xxxx and if I'm at the month level(for example July 2012) it should show me the sum of 31-07-2012 which are the last level for the given month.

View 4 Replies View Related

Analysis :: List All Dimension And Fact Column Names

Feb 24, 2011

I am using sql server 2005 enterprise edition.

How  to list all the dimension and fact column names with mdx or tsql query...

View 9 Replies View Related

Analysis :: How To Change The Member Names Of Time Dimension

Oct 19, 2015

<SQL Server 2008 R2>

I created a Time table using BIS. I found that the default naming of time members is too long and redundant.

For example, the wizard generated "Fiscal Calendar 2015", "Fiscal Quarter 1, 2015", etc. However, shorter expression like "FY2015", "FQ1 2015", etc would be enough for me. 

Is it possible to change the default naming rule, or does SSAS works correctly if I update the Time table values using SQL?

View 2 Replies View Related

Analysis :: Add Dimension To Cube Dimension Without Any Relation In Dimension Usage

Oct 26, 2015

When i add a dimension to the cube dimension without any relation in my dimension usage to any measure group my units are going down.However when i remove the dimension from the cube am getting the correct values.

View 4 Replies View Related

How To Query And Show The Result In A Form

May 12, 2006

I am an beginner in VC#2005,

my question is:

I connect a table and transtorm to WM5.0 (PDAphone),

I don't know how to show the result of my query,

EX:

table1,(two column: name , phoneNo)

private void button1_Click(object sender, EventArgs e)
{
SqlCeConnection cn = null;
try
{
cn = new SqlCeConnection("Data source=\Programs files\sqltest1\sqlPDA.sdf");


SqlCeCommand cmd = new SqlCeCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * from Table1 where phoneNo='0922123456'";


SqlCeDataAdapter da = new SqlCeDataAdapter();
DataSet ds = new DataSet();
da.SelectCommand = cmd;

}
finally
{
if (cn.State != ConnectionState.Closed)
{
cn.Close();
}

}
}

there is nothing happen, please help me.

thanks

View 3 Replies View Related

Query Results To Only Show ONE Result Per Locale

Feb 5, 2015

My query produces accurate results just produces one instance for each sales region. I am needing it to only give me one instance of each sales region and give me total counts. What should I re-write?

Code:
Select
salesmanname,
case when [state] IN ('GA', 'FL', 'AL', 'SC', 'NC', 'TN') Then 'South'
when [state] IN ('CA', 'NV', 'WA', 'OR', 'TX') Then 'West'
when [state IN ('NY', 'NJ', 'PA', 'DL', 'CT') Then 'NE'
end As [Sales Region]

[Code] ....

View 1 Replies View Related

Show Only Non-empty Dimension Members?

Jul 14, 2005

greetings,

if I create a dimension from fact table, then it only has members which appeared in fact table. but if I create dimension from dimension table, then it shows all members, even those with no data. how can I show only those with existing data in member list?

thank you

View 1 Replies View Related

Analysis :: Edit Dimension DSV

Jun 10, 2015

I am unable to see my DSV when I try to edit my dimension. I have added some new fields in my dimension table and now want to use them in my cube dimension.

View 5 Replies View Related

Analysis :: How To Get Dimension Key Attribute In MDX

Apr 30, 2015

Many dimensions don't have unique members.  Instead, the dimension source data has duplicates at the leaf level: it's left up to SSAS to aggregate up to the actual leaf level used in hierarchies.

Every cube I've worked on in the past, a dimension is clearly defined in the source data, with uniqueness already present there: we don't make a dimension out of duplicated, sort of facty data.  This kind of design seems as weird to me as an unnormalised SQL database.

Here's an example to illustrate what I mean; I'll use that Adventureworks database.

We have a Geography dimension with a Geography hierarchy.  Levels go like this from top to bottom:

Country
State-Province
City
Postcode

The Geography dimension has a key attribute called Geography Key.  It's there in the cube design as a dimension attribute, but it's not in any of the hierarchies, so I can't query it in MDX.  But that's fine: it has the same cardinality as the lowest level (Postal Code), because the dimension has some kind of normal design.

In the cube I'm dealing with, it's all messed up.  Using the AdventureWorks example above as a parallel, someone made a Geography dimension with source data keyed on [PostalCode, ExactAddress], but only wanted the dimension granularity to be PostalCode.

This makes it very hard to debug why the data in this dimension is incorrect.  I can't match up the dimension members in the cube to the source data, because the dimension doesn't actually go down to the real leaf level!

So I have a dimension attribute called ExactAddressKey, but I can't query on it in MDX, because it's not part of any dimension hierarchy.  Unfortunately changing any part of this cube design is not possible, so I can't even experiment with settings and see what happens.

How I could get to the leaf level of the data imported?  Something like

Geography.Geography.TheInvisibleLeafLevel.Members.Properties('Key')

Or does this kind of dimension design result in SSAS discarding all the data that's more granular than the most granular attribute defined in any hierarchy - so that the data actually isn't there to be queried?

View 2 Replies View Related

Analysis Services - Dimension Problem

Dec 24, 2002

Trying to create a time dimension off of a large table. When I go with the default "start year at Jan. 1", everything goes fine. When I switch that to July 1, however, (drat those fiscal years, anyway), the construction of the dimension times out. Any suggestions?

View 5 Replies View Related

Analysis Services Dimension Question

Apr 16, 2004

I have a database with millions of users with email addresses. I want to create an email domain dimension that groups domains into all the big email domains (hotmail, aol, yahoo) and an "other" consisting of all the other domains. I can create a table with entries for the big domains, and get the grouping working but anything that is not a big domain will get thrown out rather than put into an "other" category.

Any idea how to get such an "other" category?

View 1 Replies View Related

Analysis :: Can Use CurrentMember In Dimension Security

Aug 19, 2015

I am trying to define the security based on Current Member.

Can i put following statement in the DimensionData - Allowed Members Set

EXISTS([Dim M].[Emp No].MEMBERS, [Security EMP].[Role].CurrentMember, "Uvw Security Emp ")

So that the list of EMP Nos that are visible changes when a different Role is selected (Filtered).

If not then is there any other way of achieving the same? What I want is that the list of EMP No that are visible should change when the user filters it using "Role" attribute in the "Uvw Security Emp" dimension. 

If I add the Role attribute in the Dim M Dimension I think it will unnecessarily calculate aggregations, etc.

View 2 Replies View Related

Analysis :: Get Value Based On Max Of Date Dimension

Nov 18, 2015

I have a measure group product price, the measures are

1. Product Id
2. Price
3. Date

I have mapped the product id and date with product and date dimensions.I need to get the latest price of the product.For example:

Prod1, 100, 1/1/2015
Prod1, 300, 2/1/2015
Prod1, 250, 3/1/2015
Prod1, 150, 10/1/2015

So, i need the latest price. The latest price is 150.I new to MDX query. MDX query to achieve this?

View 3 Replies View Related

Analysis :: MDX For And Operator In 2 Date Dimension

Jun 4, 2015

I am in a situation to slice a fact in two date dimension .. my <g class="gr_ gr_73 gr-alert gr_spell ContextualSpelling ins-del multiReplace" data-gr-id="73" id="73">sql</g> code is like this 

select count(1) from fact_table  where end_date >=(selecteddate on filter) and startdate <=end_date .

The common date dimension is Start date. and the filter will be only on <g class="gr_ gr_168 gr-alert gr_gramm Grammar only-ins doubleReplace replaceWithoutSep" data-gr-id="168" id="168">end</g> date.

Below is the code so far

<g class="gr_ gr_228 gr-alert gr_tiny gr_spell ContextualSpelling multiReplace" data-gr-id="228" id="228">i</g> did, but <g class="gr_ gr_229 gr-alert gr_tiny gr_spell ContextualSpelling
multiReplace" data-gr-id="229" id="229">i</g> am not finding how to filter the start date <=end_date

   with member  [Measures].[X] as 

 Sum(
{[EndDate].[YQMWD].currentmember:NULL}, 
([Measures].[Count])) 
 select [Measures].[X]  on 0 ,
 [EndDate].[YQMWD].year.members on 1
  from Cube

What is the trick I can put so that I can filter those data where start date <=selected end date ....

View 3 Replies View Related

Analysis :: MDX Display All Dimension Records

Jun 15, 2015

If I have a dimension with 3 records like this:

BrokerDimId  BrokerNm
1  Broker1
2  Broker2
3  Broker3

And I have a fact table that has 2 rows that join back to the first 2 dimension rows like this:

BrokerDimId  Amt
1  $100
2  $200

I want to create the mdx so that I get all of the dimension rows, even if there is no record in the fact table for it and just default the amount to 0, like this

BrokerDimId  Amt
1  $100
2  $200
3  $0

Is this possible using MDX?

View 2 Replies View Related

Analysis :: Dimension Errors In Dropdown

Sep 1, 2015

I am getting the dropdown of the dimension folder twice, as i took printshot from client tool. In SSMS/bids also its showing this error. How to resolve it.

View 9 Replies View Related

Analysis :: Cannot Process Calendar Dimension

May 4, 2015

I'm building a cube for sales team , to test out I'm trying to process just one dimension called DimCalandar , when I try to process this dimension I get the following error ,

'Either the user abc/def, does not have access to database, or the database does not exist' ...

View 2 Replies View Related

Analysis :: Dimension Error In SSMS

Sep 2, 2015

I AM GETTING THIS ERRORS, and even when i drag and drop calculated measures, it gives error as" query error" with RED mark..

View 4 Replies View Related

Analysis :: Change Dimension Name Attribute?

Sep 8, 2015

I have 2 dimensions that pull their Facility Name from the same Location Dimension.  The business users want to change Facility Name in the Material Facilities dimension to “Material Facility Name”, but keep Facilities dimension attribute the same. What is a good way to go about completing this task.  

View 2 Replies View Related

Analysis :: Querying Dimension Members In MDX?

Sep 17, 2015

The Member_Key we can get by the method suggested in the post, but when we get the Member_Key, Id as Measures/members then performance is very slow.

Is there any way i can get the Member_key in a set in rows section.

Like

With SET [UniqueName] AS [Geography].[Country].CURRENTMEMBER.UNIQUENAME
Select Measures.Value on Columns,
UniqueName on Rows
From Cube

I want the value as just like

Value
Employees, UniqueName

View 2 Replies View Related

Analysis :: Dimension Processing Error

Mar 14, 2013

I'm getting this error during processing one dimension.OLE DB error: OLE DB or ODBC error: SQL Server blocked access to STATEMENT 'OpenRowset/ OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.; 42000.my dimension contains member from two datasource table.

and in another dimension i get error;Errors in the high-level relational engine. The 'dbo_vicidial_Users' table that is required for a join cannot be reached based on the relationships in the data source view.Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Staging User', Name of 'DimUsers' was being processed.

View 4 Replies View Related

Analysis :: Calculation Based On Dimension Value

Oct 8, 2015

Need to resolve this calculation, which I would believe is something very common on SSAS environments. 

Like many companies, my company has different ways of calculating Sales and the two I want to focus are Sales Gross and Sales Net. 

At a high level, we calculate Sales Gross as Sales with returns, and Sales Net as Sales without returns.

We have an attribute called Order Type that has various types of orders a user can execute with my company. One of them is Returns. If you return something back to us, we record that as a return line on the sales table. With that, we can calculate that return, breaking data down by Order Type, such as:

Order Type Line Total

Mail Orders $ 776,655.44

Internet Orders $ 2,211,334.00

Call Center Orders $ 11,223,344.00

Credit Orders  $ (55,666.00)

Today, to calculate Sales Gross and Sales Net, we are creating two dimensions: DimSalesGross and DimSalesNet. 

To calculate Sales Gross, we leave the data at the natural state, not making any changes to mappings. 

To calculate Sales Net, we map Credit Orders to Call Center Orders at the ETL level, getting a Net value for sales (Orders - Returns), however, I doubt this is the correct way of doing. 

I would like to have a Line Total Net / Line Total Gross calculation, which would be based on the Order Type value.

Perhaps using a CASE statement in MDX? Is the above possible?

View 4 Replies View Related

SQL 2012 :: SSAS Cube - Show Dimension Attributes For Only One Specific Fact Measure?

Aug 3, 2015

I have built a fact table and few dimension views in Datamart with the aim of creating a Cube.

On the Fact table I have added a CASE Statement with the following threshold for Premium due amounts:

CASE WHEN....
'Due_0-1_Month'
'Due_1-2_Month'
'Due_2-3_Month'
'Due_Over_3_Months'
'Overdue_0-1_Month'
'Overdue_1-3_Month'
'Overdue_3-6_Month'
'Overdue_Over_6_Months'
...END

I then created a Dimension to link this to:

CREATE VIEW...
Select 'Due_0-1_Month' as Ageing_Threshold
union all
Select 'Due_1-2_Month'
union all
Select 'Due_2-3_Month'

[Code] ....

I was successful in processing the cube, however the problem is everytime I drag the dimension on the columns field in Pivot tables the Thresholds start to break up the other amounts that I have on display like Acquisition Costs, Tax amounts. I am only interested in showing the breakdown of Premium amount measure by the Threshold dimension.

somehow 'Hide' or 'prevent' the Threshold dimension from breaking down the other measures on the Pivot and only breakdown the amounts for Premium?

how I should structure my tables in SQL or any MDX queries to resolve this.

View 0 Replies View Related

Show Field Names/Schema

Jan 15, 2004

I can't seem to find a sample code, either here or on the net - - so I'll go ahead and ask...

What I'm looking for is a sample of how to query a database, so that I can populate a listbox with the table names from a database - - and then, populate another listobx with the field names from the database, in order to build a user-driven sql statement builder....

either of the above (at least the field name part) - either a code sample, or a link, will be greatly appreciated) - -

Thanks

View 1 Replies View Related

Analysis :: How To Hide Grand Totals For Particular Dimension

Aug 3, 2015

I have a calculated measure. On a particular dimension I want to hide the grand total. for remaining dimension grad totals to be visible. Like the below screen shot ....

View 4 Replies View Related

Analysis :: Moving Dimension In SSAS 2012?

Sep 10, 2015

how to move the dimension attributes from currency to geography and vice versa(i.e need to change their positions) in SQL Server 2012. i need currency to be placed in top of geography or geography below currency.

View 6 Replies View Related

Analysis :: Subset Dimension Linked Two Times?

Aug 6, 2015

I have a cube with some dimensions (dim1,dim2,dim3).I want to add another dimension (dim4) based on dim1 but more restricted.Eg. Dim1 is a product dimension, and I will create Dim4 from a subset of Dim1 (only few and predetermined products).

I will create a view (or a named query) with the appropriate where clause, create a dimension on this view/namedQ, then add on the existant cube and link it on dimension usage. Great. But in this way in the cube I haven't all fact data but only data for the product of Dim4! (Dim1 is still present).

eg: in fact data I have a total of 100$ of sales for all the products, if I link the Dim4 then the sales amount is 40$, that is the value for the product in Dim4 not for all product (I can't set the filter for Dim1/Dim4 in excel). I haven't found any way for get the 100$.

I try with two Date dimension: the first from 01/01/2010 to 31/12/2015 and the second from 01/01/2015 to 31/12/2015. If I link both I see only data for the second period (only 2015).

it seems that the more restrictive dimension trumps.What's wrong? There are other ways to get what I want? I've tried the property "DependOnDimension" but without results.

I won't publish the attribute used in the WHERE clause on the dimension.

BIDS 2008 and SQL Server 2008R2

View 2 Replies View Related

Analysis :: How To Print Dimension Usages For Any CUBE

May 5, 2015

Best way to print Dimension Usages with Measure Group for any CUBE.

This actually facilitate business people to understand which dimensions mapped to which measure group.

View 2 Replies View Related

Analysis :: Using Named Set To Filter On Dimension Attribute

Jun 4, 2015

I am unable to find solution for the problem while writing a Named Set in my cube.

I have a calculated measures which gives me difference in Sales in PERCENTAGE (%).

When I try to filter out those product codes which went a less than 5 %, I get no records.

I have also tried to filter direct values lets say - Products with sales > 100000 which is working fine.

Following is sample of my Named Set

FILTER([X].[Products Code].members, [Measures].[Diff in Sales]<5)

I believe as the values are in percentage, I am facing this issue.

View 21 Replies View Related

Analysis :: Parent Child Dimension Not Displaying Name Value

Apr 16, 2015

I have a parent Child attribute in my dimension.  It show the correct ID value in the reports but when I change the value from the NameColumn to be something else it still only shows the ID value. I have an identical case in another dimension and it is working correctly.  However this dimension for some reason does not seem to work and I believe they are built the same way I must be missing something.  I have tried to show the OrgNodeID and tried to display the OrgNodename but neither displays all it displays is the intdimParentOrgNodeID.My dimension table looks something like this

intdimOrgNodeID int Key (surreget key)
intOrgNodeID int (Actual ID)
intDimParentOrgNodeID
intOrgNodeName

[code]....

View 4 Replies View Related

Analysis :: MDX Need To Filter Measures Based On A Dimension

Oct 26, 2015

I am pretty new to MDX and am having trouble getting what I need out of this MDX query. Some business rules:

Gross Amount applies to all clients, whether Type A or Type B.  I always want to return Gross Amount.Some clients are Type A, some are Type B, some are both, and some are neither.There are Type A Net Amount and Type B Net Amount values for all clients, but I only want to display the Type A Net value if the client is a Type A client, only Type B if the client is a Type B, or both for both, and neither for neither.  I would like to return blank/null, not $0.00, for those values that should not be displayed.

Here's the basic query.  

SELECT { [Measures].[Gross Amount],
[Measures].[Type A Net Amount],
[Measures].[Type B Net Amount]
} ON COLUMNS,
NON EMPTY {[Dim Client].[Parent Client Code].[Parent Client Code] *
[Dim Client].[Child Client Code].[Child Client Code] *
[Dim Client].[Is Type A].CHILDREN *
[Dim Client].[Is Type B].CHILDREN
} ON ROWS
FROM ClientInfo

Here's the DESIRED output........

View 3 Replies View Related

Analysis :: Getting Historical Information From Dimension Table

Aug 17, 2015

Is it possible to design a Cube with a Dimension "DimEmployee" (SCD Type2) and Query the following: What "title" had the person "xyz" at time "2015-01-01"? - See Example data on Images.

I have also a DimTime with Day granularity. The DimEmployee is not at day granularity.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved